Jeśli masz złożoną formułę zależną od wielu zmiennych i chcesz zrozumieć, jak zmiana tych danych wejściowych skutecznie wpływa na wyniki, tabela danych analizy typu „co jeśli” w programie Excel jest potężnym narzędziem. Pozwala szybko zobaczyć wszystkie możliwe wyniki. Oto przewodnik krok po kroku dotyczący tworzenia tabeli danych w programie Excel. Dodatkowo omówimy kilka kluczowych punktów efektywnego wykorzystania tabel danych i zademonstrujemy inne operacje, takie jak usuwanie, edytowanie i ponowne obliczanie tabel danych.

Co to jest tabela danych w programie Excel?

Utwórz tabelę danych z jedną zmienną

• Tabela danych zorientowana kolumnowo
• Tabela danych zorientowana na wiersze
• Wiele formuł dla tabeli danych z jedną zmienną

Utwórz tabelę danych z dwiema zmiennymi

Kluczowe punkty przy użyciu tabel danych

Inne operacje korzystania z tabel danych

• Usuń tabelę danych
• Edytuj wynik tabeli danych
• Przelicz ręcznie tabelę danych

Co to jest tabela danych w programie Excel?

W programie Excel tabela danych jest jednym z narzędzi analizy warunkowej, które umożliwia eksperymentowanie z różnymi wartościami wejściowymi formuł i obserwowanie zmian w wynikach formuły. To narzędzie jest niezwykle przydatne do badania różnych scenariuszy i przeprowadzania analiz wrażliwości, zwłaszcza gdy formuła jest zależna od wielu zmiennych.

W programie Excel istnieją dwa typy tabel danych:

Tabela danych z jedną zmienną: umożliwia analizę wpływu różnych wartości jednej zmiennej na formułę. Możesz skonfigurować tabelę danych z jedną zmienną w formacie zorientowanym na wiersze lub kolumny, w zależności od tego, czy chcesz różnicować wprowadzane dane w wierszach, czy w kolumnach.

Tabela danych z dwiema zmiennymi: ten typ umożliwia sprawdzenie wpływu zmiany dwóch różnych zmiennych na wynik formuły. W tabeli danych z dwiema zmiennymi zmieniasz wartości zarówno w wierszu, jak i w kolumnie.

Utwórz tabelę danych z jedną zmienną

Tworzenie tabeli danych z jedną zmienną w programie Excel to cenna umiejętność analizowania, w jaki sposób zmiany w pojedynczych danych wejściowych mogą wpływać na różne wyniki. W tej sekcji przeprowadzimy Cię przez proces tworzenia tabel danych zorientowanych na kolumny, wiersze i tabele danych z wieloma formułami i jedną zmienną.

Niesamowity! Korzystanie z wydajnych zakładek w programie Excel, takich jak Chrome, Edge, Firefox i Safari!
Zaoszczędź 50% swojego czasu i zmniejszaj tysiące kliknięć myszą każdego dnia!

---

Tabela danych zorientowana kolumnowo

Tabela danych zorientowana na kolumny jest przydatna, gdy chcesz sprawdzić, jak różne wartości pojedynczej zmiennej wpływają na wynik formuły, przy czym wartości zmiennych są wyszczególnione w dalszej części kolumny. Rozważmy prosty przykład finansowy:

Załóżmy, że rozważasz pożyczkę w wysokości 50,000 3 USD, którą planujesz spłacić w ciągu 36 lat (równowartość XNUMX miesięcy). Aby ocenić, jaka miesięczna płatność byłaby przystępna w oparciu o Twoje wynagrodzenie, chcesz sprawdzić, jak różne stopy procentowe wpłyną na kwotę, którą musisz płacić co miesiąc.

Krok 1: Skonfiguruj formułę podstawową

Aby obliczyć płatność, ustawię tutaj odsetki na 5%. W komórce B4 wprowadź formułę PMT, która oblicza miesięczną ratę na podstawie stopy procentowej, liczby okresów i kwoty pożyczki. Zobacz zrzut ekranu:

= -PMT($B$1/12, $B$2*12, $B$3)

Krok 2: Wypisz stopy procentowe w kolumnie

W kolumnie wypisz różne stopy procentowe, które chcesz przetestować. Na przykład wpisz w kolumnie wartości od 4% do 11% z przyrostem co 1% i pozostaw co najmniej jedną pustą kolumnę po prawej stronie, aby wyświetlić wyniki. Zobacz zrzut ekranu:

Krok 3: Utwórz tabelę danych

1. W komórce E2 wprowadź następującą formułę: = B4.
   
   Note: B4 to komórka, w której znajduje się główna formuła. Jest to formuła, której wyniki mają się zmieniać wraz ze zmianą stopy procentowej.
   
2. Wybierz zakres zawierający formułę, listę stóp procentowych i sąsiadujące komórki z wynikami (np. wybierz D2 do E10). Zobacz zrzut ekranu:
   
3. Przejdź do wstążki, kliknij Dane kartę, a następnie kliknij Co jeśli analiza > Tabela danychzobacz zrzut ekranu:
   
4. W Tabela danych oknie dialogowym kliknij w Kolumna Komórka wejściowa box (ponieważ używamy kolumny do wprowadzania wartości wejściowych) i wybierz komórkę zmiennej, do której odwołuje się Twoja formuła. W tym przykładzie wybieramy B1, który zawiera stopę procentową. Wreszcie kliknij OK przycisk, zobacz zrzut ekranu:
   
5. Excel automatycznie wypełni puste komórki sąsiadujące z każdą wartością zmiennych (różne stopy procentowe) odpowiednimi wynikami. Zobacz zrzut ekranu:
   
6. Zastosuj żądany format liczb do wyników (waluta) zgodnie z potrzebami. Teraz tabela danych zorientowana na kolumny została utworzona pomyślnie i możesz szybko przejrzeć wyniki, aby ocenić, które miesięczne kwoty płatności będą dla Ciebie dostępne w przypadku zmiany stopy procentowej. Zobacz zrzut ekranu:
   

Tabela danych zorientowana na wiersze

Tworzenie tabeli danych zorientowanej na wiersze w programie Excel polega na takim uporządkowaniu danych, aby wartości zmiennych były wyświetlane w wierszu, a nie w kolumnie. Biorąc powyższy przykład jako odniesienie, kontynuujmy kroki, aby zakończyć tworzenie tabeli danych zorientowanej na wiersze w programie Excel.

Krok 1: Wypisz stopy procentowe w rzędzie

Umieść wartości zmiennych (stopy procentowe) w wierszu, upewniając się, że po lewej stronie znajduje się co najmniej jedna pusta kolumna na formułę i jeden pusty wiersz poniżej na wyniki, patrz zrzut ekranu:

Krok 2: Utwórz tabelę danych

1. W komórce A9 wpisz następującą formułę: = B4.
   
2. Wybierz zakres zawierający formułę, listę stóp procentowych i sąsiadujące komórki z wynikami (np. wybierz A8 do I9). Następnie kliknij Dane > Co jeśli analiza > Tabela danych.
3. W Tabela danych oknie dialogowym kliknij w Wierszowa komórka wejściowa box (ponieważ używamy wiersza do wprowadzania wartości wejściowych) i wybierz komórkę zmiennej, do której odwołuje się formuła. W tym przykładzie wybieramy B1, który zawiera stopę procentową. Wreszcie kliknij OK przycisk, zobacz zrzut ekranu:
   
4. Zastosuj żądany format liczb do wyników (waluta) zgodnie z potrzebami. Teraz tworzona jest tabela danych zorientowana na wiersze, zobacz zrzut ekranu:
   

Wiele formuł dla tabeli danych z jedną zmienną

Utworzenie tabeli danych z jedną zmienną i wieloma formułami w programie Excel pozwala zobaczyć, jak zmiana pojedynczych danych wejściowych wpływa na kilka różnych formuł jednocześnie. Co w powyższym przykładzie zrobić, jeśli chcesz zobaczyć zmianę stóp procentowych zarówno w przypadku spłat, jak i odsetek całkowitych? Oto jak to skonfigurować:

Krok 1: Dodaj nową formułę, aby obliczyć całkowite odsetki

W komórce B5 wprowadź następującą formułę, aby obliczyć łączne odsetki:

=B4*B2*12-B3

Krok 2: Uporządkuj dane źródłowe tabeli danych

W kolumnie wypisz różne stopy procentowe, które chcesz przetestować, i pozostaw co najmniej dwie puste kolumny po prawej stronie, aby wyświetlić wyniki. Zobacz zrzut ekranu:

Krok 3: Utwórz tabelę danych:

1. W komórce E2 wpisz następującą formułę: = B4 stworzyć w danych pierwotnych odniesienie do wyliczenia spłaty.
2. W komórce F2 wprowadź następującą formułę: = B5 stworzyć odniesienie do całkowitego zainteresowania danymi pierwotnymi.
   
3. Wybierz zakres zawierający formuły, listę stóp procentowych i sąsiadujące komórki wyniku (np. wybierz D2 do F10). Następnie kliknij Dane > Co jeśli analiza > Tabela danych.
4. W Tabela danych oknie dialogowym kliknij w Kolumnowa komórka wejściowa box (ponieważ używamy kolumny do wprowadzania wartości wejściowych) i wybierz komórkę zmiennej, do której odwołuje się Twoja formuła. W tym przykładzie wybieramy B1, który zawiera stopę procentową. Wreszcie kliknij OK przycisk, zobacz zrzut ekranu:
   
5. Zastosuj żądany format liczb do wyników (waluta) zgodnie z potrzebami. Możesz zobaczyć wyniki dla każdej formuły na podstawie różnych wartości zmiennych.
   

Utwórz tabelę danych z dwiema zmiennymi

Tabela danych z dwiema zmiennymi w programie Excel wyświetla wpływ różnych kombinacji dwóch zestawów wartości zmiennych na wynik formuły, pokazując, jak zmiany w dwóch danych wejściowych formuły jednocześnie wpływają na jej wynik.

Tutaj narysowałem prosty szkic, który pomoże ci lepiej zrozumieć wygląd i strukturę tabeli danych z dwiema zmiennymi.

Opierając się na przykładzie tworzenia tabeli danych z jedną zmienną, przejdźmy teraz do nauki tworzenia tabeli danych z dwiema zmiennymi w programie Excel.

W poniższym zestawie danych mamy stopę procentową, okres pożyczki i kwotę pożyczki oraz obliczyliśmy miesięczną ratę za pomocą następującego wzoru: =-PMT($B$1/12, $B$2*12, $B$3) również. Tutaj skupimy się na dwóch kluczowych zmiennych z naszych danych: stopie procentowej i kwocie kredytu, aby obserwować, jak zmiany obu tych czynników jednocześnie wpływają na kwoty spłaty.

Krok 1: Skonfiguruj dwie zmienne zmienne

1. W kolumnie wypisz różne stopy procentowe, które chcesz przetestować. zobacz zrzut ekranu:
   
2. Wprowadź z rzędu różne wartości kwot pożyczki tuż nad wartościami kolumn (zaczynając od jednej komórki na prawo od komórki z formułą), zobacz zrzut ekranu:
   

Krok 2: Utwórz tabelę danych

1. Umieść formułę na przecięciu wiersza i kolumny, w której wymieniono wartości zmiennych. W tym przypadku wprowadzę następującą formułę: = B4 do komórki E2. Zobacz zrzut ekranu:
   
2. Wybierz zakres obejmujący kwotę pożyczki, stopy procentowe, komórkę z formułą i komórki, w których będą wyświetlane wyniki.
   
3. Następnie kliknij Dane > Co jeśli analiza > Tabela danych. W oknie dialogowym Tabela danych:
   • W Wierszowa komórka wejściowa wybierz odwołanie do komórki wejściowej dla wartości zmiennych w wierszu (w tym przykładzie B3 zawiera kwotę pożyczki).
   • W Kolumna wejściowa celW polu l wybierz odwołanie do komórki wejściowej dla wartości zmiennych w kolumnie (B1 zawiera stopę procentową).
   • A następnie kliknij OK Przycisk.
     
4. Teraz Excel wypełni tabelę danych wynikami dla każdej kombinacji kwoty pożyczki i stopy procentowej. Zapewnia bezpośredni wgląd w to, jak różne kombinacje kwot kredytu i stóp procentowych wpływają na miesięczną ratę, co czyni go cennym narzędziem do planowania i analizy finansowej.
   
5. Na koniec powinieneś zastosować żądany format liczbowy do wyników (waluta) zgodnie ze swoimi potrzebami.
   

Kluczowe punkty przy użyciu tabel danych

• Nowo utworzona tabela danych musi znajdować się w tym samym arkuszu, co oryginalne dane.
• Dane wyjściowe tabeli danych zależą od komórki z formułą w źródłowym zestawie danych i wszelkie zmiany w tej komórce z formułą spowodują automatyczną aktualizację danych wyjściowych.
• Po obliczeniu wartości przy użyciu tabeli danych nie można ich cofnąć Ctrl + Z. Można jednak ręcznie wybrać wszystkie wartości i je usunąć.
• Tabela danych generuje formuły tablicowe, więc poszczególnych komórek w tabeli nie można zmieniać ani usuwać.

Inne operacje korzystania z tabel danych

Po utworzeniu tabeli danych skuteczne zarządzanie nią może wymagać dodatkowych operacji, takich jak usunięcie tabeli danych, modyfikacja jej wyników i ręczne przeliczenie.

Usuń tabelę danych

Ponieważ wyniki tabeli danych są obliczane przy użyciu formuły tablicowej, nie można usunąć pojedynczej wartości z tabeli danych. Można jednak usunąć tylko całą tabelę danych.

Zaznacz wszystkie komórki tabeli danych lub tylko komórki z wynikami i naciśnij Usuń klawisz na klawiaturze.

Edytuj wynik tabeli danych

Rzeczywiście nie można bezpośrednio edytować poszczególnych komórek w tabeli danych, ponieważ zawierają one formuły tablicowe, które program Excel generuje automatycznie.

Aby wprowadzić zmiany, zazwyczaj trzeba usunąć istniejącą tabelę danych, a następnie utworzyć nową z żądanymi modyfikacjami. Wiąże się to z dostosowaniem formuły podstawowej lub wartości wejściowych, a następnie ponownym skonfigurowaniem tabeli danych w celu odzwierciedlenia tych zmian.

Przelicz ręcznie tabelę danych

Zwykle program Excel ponownie oblicza wszystkie formuły we wszystkich otwartych skoroszytach po każdej zmianie. Jeśli duża tabela danych zawierająca wiele wartości zmiennych i złożone formuły powoduje spowolnienie skoroszytu programu Excel.

Aby uniknąć automatycznego wykonywania przez program Excel obliczeń dla wszystkich tabel danych za każdym razem, gdy wprowadzana jest zmiana w skoroszycie, możesz przełączyć tryb obliczeń z automatycznego na ręczny. Wykonaj następujące czynności:

Idź do Wzory kartę, a następnie kliknij Opcje obliczeniowe > Automatycznie z wyjątkiem tabel danychzobacz zrzut ekranu:

Po zmodyfikowaniu tego ustawienia i ponownym obliczeniu całego skoroszytu program Excel nie będzie już automatycznie aktualizował obliczeń w tabelach danych.

Jeśli chcesz ręcznie zaktualizować tabelę danych, po prostu zaznacz komórki, w których wyświetlane są wyniki (komórki zawierające formuły TABLE()), a następnie naciśnij F9 klawisz.

Wykonując kroki opisane w tym przewodniku i pamiętając o kluczowych kwestiach, możesz efektywnie wykorzystywać tabele danych do potrzeb analizy danych. Jeśli chcesz poznać więcej porad i wskazówek dotyczących Excela, nasza witryna internetowa oferuje tysiące samouczków, Aby uzyskać do nich dostęp, kliknij tutaj. Dziękujemy za przeczytanie i mamy nadzieję, że w przyszłości przekażemy Ci więcej przydatnych informacji!