Excel What-If Analysis oferă un tabel de date. Acesta este un nume rău. Ar trebui să se numească Analiza sensibilității. Este rece. Citiți despre asta aici.
Goal Seek vă permite să găsiți setul de intrări care duc la un anumit rezultat. Uneori, doriți să vedeți multe rezultate diferite din diverse combinații de intrări. Cu condiția să aveți doar două celule de intrare de modificat, tabelul de date oferă o modalitate rapidă de a compara alternativele.
Folosind exemplul de plată a împrumutului, spuneți că doriți să calculați prețul pentru o varietate de solduri principale și pentru o varietate de termeni.
Asigurați-vă că formula pe care doriți să o modelați se află în colțul din stânga sus al unui interval. Puneți diferite valori pentru o variabilă în coloana din stânga și diferite valori pentru o altă variabilă în partea de sus.
Din fila Date, selectați What-If Analysis, tabel de date.
Aveți valori de-a lungul rândului superior al tabelului de intrare. Doriți ca Excel să conecteze aceste valori într-o anumită celulă de intrare. Specificați acea celulă de intrare ca celulă de intrare rând.
Aveți valori de-a lungul coloanei din stânga. Vrei ca aceia să fie conectați la o altă celulă de intrare. Specificați acea celulă ca celulă de intrare a coloanei.
Când faceți clic pe OK, Excel va repeta formula din coloana din stânga sus pentru toate combinațiile dintre rândul de sus și coloana din stânga. În imaginea de mai jos, vedeți 60 de plăți de împrumut diferite pe baza diferitelor rezultate.
Rețineți că am formatat rezultatele tabelului pentru a nu avea zecimale și am folosit Acasă, Formatare condiționată, Scală de culori pentru a adăuga umbrirea roșu / galben / verde.
Iată partea cea mai bună: acest tabel este „live”. Dacă modificați celulele de intrare de-a lungul coloanei din stânga sau din rândul de sus, valorile din tabel se vor recalcula. Mai jos, valorile din partea stângă se concentrează pe intervalul de 23K până la 24K $.
Mulțumim lui Owen W. Green pentru sugerarea tabelelor.
Urmăriți videoclipul
- Trei instrumente ce se întâmplă în Excel
- Ieri - Goal Seek
- Astăzi - un tabel de date
- Excelent pentru probleme cu două variabile
- Trivia: funcția matrice TABLE nu poate fi introdusă manual - nu va funcționa
- Folosiți o Scală de culori pentru a colora răspunsurile
- Ce se întâmplă dacă aveți 3 variabile de modificat? Scenarii? Nu! Copiați foaia de lucru
- Tabelele sunt greu de calculat: modul de calcul pentru toate cu excepția tabelelor
- Mulțumim lui Owen W. Green pentru sugerarea acestui sfat
Transcriere video
Aflați Excel din podcast, episodul 2034 - Ce se întâmplă cu un tabel de date!
Podcastez această carte întreagă, faceți clic pe „i” în colțul din dreapta sus pentru a accesa lista de redare!
Astăzi vom vorbi despre cel de-al doilea instrument sub Analiza Ce-Dacă, ieri am vorbit despre Căutarea obiectivelor, astăzi vom acoperi un tabel de date. Deci, avem acest mic model frumos aici, acesta este un model mic, 3 celule de intrare, o formulă. Dar acest model ar putea fi sute de celule de intrare, mii de rânduri, atâta timp cât se reduce la un răspuns final și dorim să modelăm acest răspuns pentru mai multe valori diferite de 2-3 (?) Celule de intrare. De exemplu, poate că suntem interesați să ne uităm la diferite mașini, așa că oriunde de la 20000 în sus, așa că voi pune 20 și 21000 acolo, apuc mânerul de umplere și trageți, duc asta până la 28000. Ne uităm la termeni diferiți, deci un împrumut de 36 de luni, 42 de luni, 48 de luni, 54, 60, 66 și chiar 72.
Bine acum, acest pas următor este complet opțional, dar mă ajută cu adevărat să mă gândesc la asta, schimb mereu culorile valorilor de sus și valorile din stânga. Și cel mai important lucru aici este că acea celulă de colț, acea celulă de colț foarte importantă, trebuie să fie răspunsul pe care încercăm să îl modelăm, bine. Deci, trebuie să începeți să selectați din acea celulă de colț cu răspunsul, apoi să selectați toate rândurile și toate coloanele. Așa că intrăm în Date, Ce ar fi dacă Analiza și un Tabel de date și solicită două lucruri aici și iată cum v-ați gândi la asta. Se spune că există o grămadă de elemente diferite de-a lungul rândului superior din tabel, vreau să iau aceste articole, unul câte unul, și să le conectez la model, unde ar trebui să introducem? Deci, aceste elemente, aceștia sunt termeni, ar trebui să intre în celula B2. Și apoi,există o grămadă de articole de-a lungul coloanei din stânga, vrem să le luăm, pe rând, și să le conectăm la B1, așa, bine și facem clic pe OK, BAM, rulează acest model mereu și iar și iar .
Acum, doar un pic de curățare aici, intru mereu și fac acasă și probabil 0 zecimale, așa. Și poate un pic de formatare condițională, scale de culoare și să mergem cu numere roșii pentru numere mari și verzi pentru mici, doar pentru a-mi oferi un mod, să știi, de a urmări vizual acest lucru. Acum se pare că, dacă tragem pentru 425 de dolari, suntem cam, știți, în acest loc sau în acest loc, sau știți, poate aici, ne vom aduce cu toții aproape de 425 de dolari. Așa că pot vedea care sunt diferitele cote, diferitele noastre combinații, pentru a ne duce la acele valori.
Acum câteva lucruri, această parte din interiorul nostru, este de fapt o formulă de matrice mare, deci = TABLE (B2, B1), rândul și coloana de intrare. Acest lucru este curios, nu aveți voie să tastați acest lucru, îl puteți crea doar folosind Date, What-If Analysis, trebuie să utilizați acea casetă de dialog. Dacă încercați și tastați acea formulă, apăsați Ctrl + Shift + Enter, nu va funcționa, nu? Deci, este o funcție în Excel, dar dacă ești suficient de inteligent pentru a o tasta, păcat, nu va funcționa, dar se recalculează constant. Deci, dacă stabilim că ne uităm doar la termeni de la 48 și vrem să ne uităm în grupuri de 3 sau ceva de genul asta, așa cum schimb aceste numere, toate acestea se calculează. În acest caz, face doar o formulă pentru fiecare, dar imaginați-vă dacă am face 100 de formule, acest lucru va încetini dramatic. Deci, aici, sub Formule, acoloDe fapt, este o opțiune Opțiuni de calcul, automată sau manuală, există o a treia care spune „Da, recalculează totul, cu excepția tabelelor de date, nu continua să recalculezi tabelul de date”. Deoarece acest lucru poate fi un mare impact asupra timpilor de calcul.
Bine acum, tabelele de date sunt minunate atunci când aveți două variabile de modificat, dar avem trei variabile de modificat. Ce se întâmplă dacă există rate ale dobânzii diferite, vă recomand să mergeți la Managerul de scenarii? NU, NU recomand NICIODATĂ să mergi la Managerul de scenarii! În acest caz avem 9x7, adică 63 de scenarii diferite pe care le-am calculat aici, pentru a crea 63 de scenarii diferite de scenariu Manager ar dura 2 ore, este oribil. Nu acoper acest lucru în cartea „MrExcel XL”, deoarece sunt cele mai bune 40 de sfaturi. Acest lucru este probabil în cartea mea „Power Excel” cu 567 de mistere Excel rezolvate, dar sunt sigur că m-am plâns de cât de mizerabil este să folosești, nu mă vei vedea făcând Managerul de scenarii aici. Dacă într-adevăr a trebuit să facem acest lucru pentru mai multe rate diferite, cel mai bun lucru de făcut este doar Ctrl-drag, luați această foaie, Ctrl-drag, Ctrl-drag,Ctrl-glisați, apoi modificați ratele de pe fiecare foaie. Deci, dacă am putea obține un 5% sau 4,75% sau ceva de genul asta și așa mai departe, nu există o modalitate ușoară de a configura acest lucru pentru 3 variabile în Scenario Manager. Bine, „40 de cele mai bune sfaturi Excel din toate timpurile”, toate din această carte, puteți cumpăra cartea, faceți clic pe „i” din colțul din dreapta sus.
Recapitularea episodului de astăzi: există trei instrumente What-If în Excel, ieri am vorbit despre Goal Seek, astăzi Tabelul de date. Este minunat pentru problemele cu 2 variabile, mâine veți vedea una cu o problemă cu 1 variabilă. Funcția matricei de tabele nu poate fi introdusă manual, nu va funcționa, trebuie să utilizați date, ce-dacă analiză, tabel de date. Am folosit o scală de culori, Acasă, Formatare condiționată, Scale de culoare, pentru a colora răspunsurile. Dacă aveți 3 variabile de modificat, faceți scenarii? Nu, faceți doar copii ale foii de lucru sau copii ale tabelului, acestea sunt greu de calculat, mai ales cu un model complex. Există un mod de calcul pentru Automat pentru toate cu excepția tabelelor, iar Owen W. Green a sugerat includerea acestei caracteristici în cărți.
Așadar, mulțumită lui și mulțumită ție pentru că ai trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2034.xlsx
