jak tworzyć i używać tabeli danych w Excelu

samouczek pokazuje, jak używać tabel danych do analizy co-jeśli w Excelu. Dowiedz się, jak utworzyć tabelę jedno-i dwu-zmienną, aby zobaczyć wpływ jednej lub dwóch wartości wejściowych na formułę oraz jak skonfigurować tabelę danych, aby oceniać wiele formuł jednocześnie.

zbudowałeś złożoną formułę zależną od wielu zmiennych i chcesz wiedzieć, jak zmiana tych danych wejściowych zmienia wyniki., Zamiast testować każdą zmienną z osobna, stwórz tabelę danych analizy „What-if” i szybko obserwuj wszystkie możliwe wyniki!

• Tabela danych w programie Excel
• Utwórz jedną zmienną tabelę danych
• utwórz dwie zmienne tabelę danych
• Tabela danych do oceny wielu formuł
• tabele danych Excel – 3 rzeczy, które powinieneś wiedzieć
• Usuń tabelę danych w programie Excel
• Edytuj wyniki tabeli danych
• przeliczaj tabelę danych ręcznie

Co to jest Tabela danych w programie Excel?,

w programie Microsoft Excel Tabela danych jest jednym z narzędzi do analizy „co Jeśli”, które pozwala wypróbować różne wartości wejściowe dla formuł i zobaczyć, jak zmiany tych wartości wpływają na wyniki wyjściowe formuł.

tabele danych są szczególnie przydatne, gdy formuła zależy od kilku wartości i chcesz eksperymentować z różnymi kombinacjami danych wejściowych i porównać wyniki.

obecnie istnieje jedna zmienna Tabela danych i dwie zmienne Tabela danych. Chociaż Tabela danych jest ograniczona do maksymalnie dwóch różnych komórek wejściowych, umożliwia testowanie dowolnej liczby wartości zmiennych.,

jak utworzyć jedną zmienną tabelę danych w Excelu

jedna zmienna Tabela danych w Excelu umożliwia testowanie serii wartości dla pojedynczej komórki wejściowej i pokazuje, w jaki sposób wartości te wpływają na wynik powiązanej formuły.,

aby pomóc ci lepiej zrozumieć tę funkcję, będziemy podążać za konkretnym przykładem, a nie opisywać ogólne kroki.

Załóżmy, że rozważasz zdeponowanie swoich oszczędności w banku, który płaci 5% odsetek miesięcznie. Aby sprawdzić różne opcje, zbudowałeś następujący Kalkulator odsetek złożonych, gdzie:

• B8 zawiera formułę FV, która oblicza saldo zamknięcia.
• B2 jest zmienną, którą chcesz przetestować (inwestycja początkowa).,

a teraz zróbmy prostą analizę „What-If”, aby zobaczyć, jakie będą twoje oszczędności w ciągu 5 lat w zależności od kwoty początkowej inwestycji, w zakresie od 1000 do 6000 USD.

oto kroki tworzenia tabeli danych o jednej zmiennej:

1. wprowadź wartości zmiennych w jednej kolumnie lub w jednym wierszu. W tym przykładzie stworzymy tabelę danych zorientowaną na kolumny, więc wpisujemy nasze wartości zmiennych w kolumnie (D3: D8)i zostawiamy co najmniej jedną pustą kolumnę po prawej stronie dla wyników.,
2. wpisz formułę w komórce o jeden wiersz powyżej i jedną komórkę po prawej stronie wartości zmiennej (w naszym przypadku E2). Lub połącz tę komórkę z formułą w oryginalnym zbiorze danych (jeśli zdecydujesz się zmienić formułę w przyszłości, będziesz musiał zaktualizować tylko jedną komórkę). Wybieramy tę ostatnią opcję i wprowadzamy tę prostą formułę w E2: =B8
   
   Tip., Jeśli chcesz zbadać wpływ wartości zmiennych na inne formuły, które odnoszą się do tej samej komórki wejściowej, wprowadź dodatkowe formuły po prawej stronie pierwszej formuły, jak pokazano w tym przykładzie.
3. Wybierz zakres tabeli danych, w tym formułę, komórki wartości zmiennych i puste komórki dla wyników(D2: E8).,
4. przejdź do zakładki Dane > Data Tools group, kliknij przycisk Analiza co-jeśli, a następnie kliknij tabelę danych…
   
5. w oknie dialogowym Tabela danych, kliknij w polu komórki wejściowej kolumny (ponieważ nasze wartości inwestycyjne są w kolumnie) i wybierz komórkę zmiennej, do której odwołujesz się w swojej formule. W tym przykładzie wybieramy B3, który zawiera początkową wartość inwestycji.
   
6. kliknij OK, a program Excel natychmiast wypełni puste komórki wynikami odpowiadającymi wartości zmiennej w tym samym wierszu.,
7. Zastosuj żądany format liczb do wyników (w naszym przypadku waluta), i jesteś gotowy!

teraz możesz szybko spojrzeć na tabelę danych o jednej zmiennej, sprawdzić możliwe salda i wybrać optymalny rozmiar wpłaty:

Tabela danych zorientowana na wiersz

powyższy przykład pokazuje, jak skonfigurować pionową lub kolumnową tabelę danych w programie Excel., Jeśli wolisz układ poziomy, oto co musisz zrobić:

1. wpisz wartości zmiennych w wierszu, pozostawiając co najmniej jedną pustą kolumnę po lewej stronie (dla formuły) i jeden pusty wiersz poniżej (dla wyników). W tym przykładzie wprowadzamy wartości zmiennych w komórkach F3: J3.
2. wprowadź formułę w komórce, która jest jedną kolumną na lewo od pierwszej wartości zmiennej i jedną komórkę poniżej (w naszym przypadku E4).,
3. Utwórz tabelę danych, jak opisano powyżej, ale Wprowadź wartość wejściową (B3) w polu komórki wejściowej wiersza:
   
4. kliknij OK, a otrzymasz następujący wynik:
   

jak utworzyć tabelę danych o dwóch zmiennych w programie Excelh2

Tabela danych o dwóch zmiennych pokazuje, w jaki sposób różne kombinacje 2 zestawów wartości zmiennych wpływają na wynik formuły. Innymi słowy, pokazuje, jak zmiana dwóch wartości wejściowych tej samej formuły zmienia wyjście.,

kroki tworzenia tabeli danych o dwóch zmiennych w programie Excel są zasadniczo takie same jak w powyższym przykładzie, z tym wyjątkiem, że można wprowadzić dwa zakresy możliwych wartości wejściowych, jeden w wierszu, a drugi w kolumnie.

aby zobaczyć, jak to działa, skorzystajmy z tego samego kalkulatora odsetek złożonych i zbadajmy wpływ wielkości inwestycji początkowej i liczby lat na saldzie. Aby to zrobić, skonfiguruj tabelę danych w ten sposób:

1. wprowadź formułę w pustej komórce lub połącz ją z oryginalną formułą., Upewnij się, że masz wystarczającą ilość pustych kolumn po prawej stronie i pustych wierszy poniżej, aby pomieścić wartości zmiennych. Tak jak wcześniej, łączymy komórkę E2 z oryginalną formułą FV, która oblicza saldo: =B8
2. wpisz jeden zestaw wartości wejściowych poniżej formuły ,w tej samej kolumnie(wartości inwestycji w E3: E8).
3. wprowadź drugi zestaw wartości zmiennych po prawej stronie formuły, w tym samym wierszu(liczba lat w F2: H2).,

   w tym momencie Twoja Tabela danych o dwóch zmiennych powinna wyglądać podobnie do tej:
   

4. wybierz cały zakres tabeli danych, w tym formułę, wiersz i kolumnę wartości zmiennych oraz komórki, w których będą wyświetlane obliczone wartości. Wybieramy zakres E2: H8.,
5. Utwórz tabelę danych w już znany sposób: Zakładka danych > przycisk analizy co-jeśli > Tabela danych…
6. w polu komórki wejściowej wiersza wprowadź odniesienie do komórki wejściowej dla wartości zmiennych w wierszu (w tym przykładzie jest to B6 zawierająca wartość lat).
7. w polu komórki wejściowej kolumny wprowadź odniesienie do komórki wejściowej dla wartości zmiennych w kolumnie (B3 zawierający początkową wartość inwestycji).
8. kliknij OK.,

   

Tabela danych w celu porównania wielu wyników

Jeśli chcesz ocenić więcej niż jedną formułę w tym samym czasie, Zbuduj swoją tabelę danych, jak pokazano w poprzednich przykładach, i wprowadź dodatkowe formuły:

• po prawej stronie pierwszej formuły w przypadku pionowej tabeli danych zorganizowanej w kolumny
• poniżej pierwszej formuły w przypadku poziomej tabeli danych zorganizowanej w wierszach

aby Tabela danych „multi-formula” działała poprawnie, wszystkie formuły powinny odnosić się do tej samej komórki wejściowej.,

jako przykład dodajmy jeszcze jeden wzór do naszej tabeli danych o jednej zmiennej, aby obliczyć odsetki i zobaczyć, jak wpływa na nie wielkość początkowej inwestycji. Oto, co robimy:

1. w komórce B10 Oblicz odsetki za pomocą tej formuły: =B8-B3
2. ułóż dane źródłowe tabeli danych tak, jak zrobiliśmy to wcześniej: wartości zmiennych w D3:D8 i E2 połączone z B8 (formuła równowagi).,
3. Dodaj jeszcze jedną kolumnę do zakresu tabeli danych (kolumna F) i połącz F2 z B10 (formuła zainteresowania):
   
4. Wybierz rozszerzony zakres tabeli danych (D2:F8).
5. Otwórz okno dialogowe tabeli danych, klikając kartę Dane > Analiza co-jeśli > Tabela danych…
6. w polu komórki wejściowej kolumny podaj komórkę wejściową (B3) i kliknij OK.,

Voilà, możesz teraz obserwować wpływ wartości zmiennych na obu formułach:

Tabela danych w Excelu – 3 rzeczy, które powinieneś wiedzieć

aby efektywnie korzystać z tabel danych w Excelu, pamiętaj o tych 3 prostych faktach:

1. aby Tabela danych została pomyślnie utworzona, komórki wejściowe muszą znajdować się na tabeli danych.ten sam arkusz co Tabela danych.,
2. Microsoft Excel używa funkcji TABLE(row_input_cell, colum_input_cell) do obliczania wyników tabeli danych:
   • w tabeli danych jednej zmiennej pomija się jeden z argumentów, w zależności od układu (kolumnowego lub wierszowego). Na przykład, w naszej poziomej tabeli danych o jednej zmiennej, formuła jest =TABLE(, B3) gdzie B3 jest komórką wejściową kolumny.
   • w dwu zmiennych tabeli danych oba argumenty są na miejscu. Na przykład =TABLE(B6, B3) gdzie B6 jest komórką wejściową wiersza, a B3 komórką wejściową kolumny.,

   funkcja TABLE jest wprowadzana jako formuła tablicy. Aby się tego upewnić, wybierz dowolną komórkę z obliczoną wartością, spójrz na pasek formuły i zwróć uwagę na {nawiasy klamrowe} wokół formuły. Jednak nie jest to normalna formuła tablicy – nie możesz wpisać jej na pasku formuły ani edytować istniejącej. To tylko „na pokaz”.

3. ponieważ wyniki tabeli danych są obliczane za pomocą formuły tablicy, wynikowe komórki nie mogą być edytowane indywidualnie. Możesz edytować lub usuwać tylko całą tablicę komórek, jak wyjaśniono poniżej.,

jak usunąć tabelę danych w programie Excel

Jak wspomniano powyżej, program Excel nie umożliwia usuwania wartości w poszczególnych komórkach zawierających wyniki. Za każdym razem, gdy próbujesz to zrobić, pojawi się komunikat o błędzie „Nie można zmienić części tabeli danych”.

można jednak łatwo wyczyścić całą tablicę wynikowych wartości. Oto jak to zrobić:

1. w zależności od potrzeb wybierz wszystkie komórki tabeli danych lub tylko komórki z wynikami.
2. naciśnij klawisz Delete.

gotowe!, 🙂

jak edytować wyniki tabeli danych

ponieważ nie można zmienić części tablicy w Excelu, nie można edytować pojedynczych komórek z obliczonymi wartościami. Możesz zastąpić wszystkie te wartości tylko własnymi, wykonując następujące kroki:

1. Wybierz wszystkie wynikowe komórki.
2. Usuń formułę tabeli na pasku formuły.
3. wpisz żądaną wartość i naciśnij Ctrl + Enter.,

spowoduje to wstawienie tej samej wartości we wszystkich wybranych komórkach:

gdy formuła tabeli zniknie, poprzednia Tabela danych stanie się zwykłym zakresem i możesz normalnie edytować dowolną pojedynczą komórkę.

jak ręcznie przeliczać tabelę danych

Jeśli duża Tabela danych z wieloma wartościami zmiennych i formułami spowalnia program Excel, można wyłączyć automatyczne przeliczanie w tej i wszystkich innych tabelach danych.,

w tym celu przejdź do karty Formuły> Grupa obliczeń, kliknij przycisk Opcje obliczeń, a następnie kliknij przycisk automatyczne oprócz tabel danych.

spowoduje to wyłączenie automatycznych obliczeń tabeli danych i przyspieszenie przeliczania całego skoroszytu.

aby ręcznie przeliczyć tabelę danych, wybierz jej komórki wynikowe, tj. komórki z formułami TABLE() i naciśnij klawisz F9.

w ten sposób tworzysz i używasz tabeli danych w programie Excel., Aby przyjrzeć się bliżej przykładom omówionym w tym samouczku, zapraszamy do pobrania naszego przykładowego skoroszytu tabel danych Excel. Dziękuję za przeczytanie i chętnie zobaczę cię ponownie w przyszłym tygodniu!

• Analiza co by było gdyby z celem szukanie w Excelu
• Jak używać Solvera w Excelu: krok po kroku z przykładami
• jak grupować wiersze w Excelu