Izvairieties no VLOOKUP, izmantojot datu modeli. Tātad, pirms jūs varat izveidot pagrieziena tabulu, jums ir divas tabulas, kurām jābūt savienotām ar VLOOKUP. Ja Windows datorā ir programma Excel 2013 vai jaunāka, tagad to varat izdarīt vienkārši un viegli.
Pieņemsim, ka jums ir datu kopa ar produktu, klientu un pārdošanas informāciju.
IT nodaļa aizmirsa tur ievietot nozari. Šeit ir uzmeklēšanas tabula, kas kartē klientus nozarēs. Laiks VLOOKUP, vai ne?
Lai pievienotos šīm datu kopām, nav jāveic VLOOKUP, ja jums ir programma Excel 2013 vai Excel 2016. Abās šajās Excel versijās Power Pivot dzinējs ir iekļauts Excel kodolā. (To varētu izdarīt arī, izmantojot Excel 2010 pievienojumprogrammu Power Pivot, taču ir dažas papildu darbības.)
Gan sākotnējā datu kopā, gan uzmeklēšanas tabulā izmantojiet Sākums, Formatēt kā tabulu. Cilnē Tabulas rīki pārdēvējiet tabulu no Table1 uz kaut ko jēgpilnu. Esmu izmantojis datus un sektorus.
Datu tabulā atlasiet vienu šūnu. Izvēlieties Ievietot, rakurstabulu. Sākot ar programmu Excel 2013, ir papildu lodziņš Pievienot šos datus datu modelim, kas jums jāizvēlas pirms noklikšķināšanas uz Labi.
Tiek parādīts rakurstabulas lauku saraksts kopā ar laukiem no tabulas Dati. Izvēlieties Ieņēmumi. Tā kā jūs izmantojat datu modeli, saraksta augšdaļā tiek parādīta jauna rinda, kas piedāvā Active vai All. Noklikšķiniet uz Visi.
Pārsteidzoši, ka rakurstabulas lauku saraksts piedāvā visas pārējās darbgrāmatas tabulas. Tas ir revolucionārs. Jūs vēl neesat veicis VLOOKUP. Izvērsiet tabulu Sektori un izvēlieties Sector. Notiek divas lietas, kas jūs brīdina, ka pastāv problēma.
Pirmkārt, rakurstabula parādās ar vienādu numuru visās šūnās.
Iespējams, izsmalcinātāks brīdinājums ir dzeltena rūtiņa saraksta PivotTable lauku augšpusē, norādot, ka jums ir jāizveido attiecības. Izvēlieties Izveidot. (Ja izmantojat programmu Excel 2010 vai 2016, veiciet veiksmi, izmantojot automātisko noteikšanu.)
Dialogā Izveidot attiecības jums ir četras nolaižamās izvēlnes. Izvēlieties Dati tabulā, Klients - kolonnā (Ārvalstu) un Sektori - Saistītā tabula. Power Pivot automātiski aizpildīs atbilstošo kolonnu sadaļā Saistītā kolonna (Primārā). Noklikšķiniet uz Labi.
Rezultātā izveidotā rakurstabula ir sākotnējo datu un uzmeklēšanas tabulas sajaukums. Nav nepieciešami VLOOKUP.
Skatīties video
- Sākot ar programmu Excel 2013, dialoglodziņš Pivot Table piedāvā datu modeli
- Šis ir Power Pivot Engine koda vārds
- Lai izmantotu datu modeli, izveidojiet Ctrl + T tabulu no katras darbgrāmatas tabulas
- No pirmās tabulas izveidojiet pagrieziena tabulu
- Pivot Table lauku sarakstā mainiet no Active uz All
- Uzmeklēšanas tabulā izvēlieties lauku
- Vai nu izveidojiet attiecības, vai arī automātiskā noteikšana
- Automātiskās noteikšanas 2013. gadā nebija
- Paldies Kolinam Maiklam un Alehandro Kviceno par Power Pivot ieteikšanu kopumā.
Video atšifrējums
Uzziniet Excel no Podcast, 2014. gada epizodes - novērsiet VLOOKUP!
Apraidot visu šo grāmatu, atskaņošanas saraksta augšējā labajā stūrī noklikšķiniet uz “i”!
Sveiki, laipni lūgti atgriezties netcast ēterā, es esmu Bils Jelens. To patiesībā sauc par Likvidēt VLOOKUP ar datu modeli! Tagad es atvainojos, šī ir Excel 2013 un jaunāka versija. Ja esat atgriezies programmā Excel 2010, jums ir jālejupielādē Power Pivot pievienojumprogramma, kas, protams, ir bez maksas 2010. gadā. Tātad, kas mums šeit ir, mums ir mūsu galvenā datu kopa, šeit ir lauks Klients, un tad man ir neliela tabula, kurā klienti tiek sadalīti pa nozarēm, man ir jāizveido kopējie ieņēmumi pa nozarēm, vai ne? Tas ir VLOOKUP, vienkārši veiciet VLOOKUP, bet, hei, pateicoties Excel 2013, mums nav jāveic VLOOKUP! Abas šīs lietas izveidoju tabulā, un tabulas Rīki, Dizains vietā es pārdēvēju tabulas, es to saucu par Sektoriem un es to saucu par Datu, lai to padarītu par tabulu, vienkārši izvēlieties vienu šūnu, nospiediet Ctrl + T. Tātad, ja mums ir daži virsraksti un daži skaitļi, nospiežot Ctrl + T,viņi jautā: "Kur ir jūsu tabulas dati?", Manā tabulā ir galvenes, un tad viņi to sauc par 3. tabulu, jūs to saucat par kaut ko citu. Labi, tā es izveidoju šīs divas tabulas, es atbrīvojos no šīs tabulas, labi.
Tātad, lai šis triks darbotos, visiem datiem ir jādzīvo tabulās. Mēs dodamies uz cilni Ievietot, izvēlieties PivotTable un tieši šeit, apakšā, pievienojiet šos datus datu modelim. Tas izklausās ļoti nekaitīgi, vai ne? Nav nekas tāds kā mirgojošs punkts, kas saka: "Hei, tas ļaus jums darīt pārsteidzošas lietas!" Un tas, ko viņi šeit saka, ko viņi cenšas neteikt, ir tas, ka, starp citu, katrā Excel 2013 eksemplārā ir Power Pivot motors. Jūs zināt, ja esat Office 365, jūs maksājat 10 ASV dolārus mēnesī, un viņi vēlas, lai jūs maksātu 12 vai 15 ASV dolārus mēnesī, lai iegūtu Power Pivot - papildu divus vai piecus dolārus. Nu, hei, šš, nesaki, patiesībā lielākā daļa Power Pivot jau ir programmā Excel 2013. Labi, tāpēc es noklikšķinu uz Labi, datu modeļa ielāde aizņem mazliet vairāk laika, labi, bet tas ir OK, un tieši pāri šeit,laukos PivotTable man ir visu lauku saraksts. Tātad, es noteikti vēlos parādīt ieņēmumus, taču šeit ir viss, kas ir atšķirīgs, izmantojot Active un All. Izvēloties Visas, es saņemu visas darbgrāmatas tabulas. Labi, tāpēc dodos uz Sektoriem un teicu, ka vēlos sektoru ievietot apgabalā Rindas. Sākotnēji ziņojums būs nepareizs, redziet 6,7 miljonus līdz galam, un šis dzeltenais brīdinājums šeit teiks, ka jums ir jāizveido attiecības.un šis dzeltenais brīdinājums šeit teiks, ka jums ir jāizveido attiecības.un šis dzeltenais brīdinājums šeit teiks, ka jums ir jāizveido attiecības.
Labi tagad, 2010. gadā ar Power Pivot, tas vienkārši piedāvāja AutoDetect, 2013. gadā viņi izveda AutoDetect, un 2016. gadā viņi atgrieza AutoDetect, labi? Man vajadzētu parādīt, kā izskatās CREATE, bet, kad es noklikšķinu uz šīs CREATE pogas, ak, jā, tas tā, labi, labi. Tāpēc no mūsu pirmās tabulas Dati man ir lauks ar nosaukumu Klients, no saistītās tabulas Sektori, man ir lauks ar nosaukumu Klients, un pēc tam jūs noklikšķiniet uz Labi, labi. Bet ļaujiet man vienkārši parādīt, cik foršs ir AutoDetect. Ja gadās būt 2016. gadā, viņi to izdomāja, cik tas ir lieliski, vai ne? Jums nav jāuztraucas par VLOOKUP, un komats krīt beigās, ja VLOOKUP liek sāpēt galvu, jums patiks datu modelis. Paņēmis šīs divas tabulas, savienojis tās kopā, jūs zināt, tāpat kā Access darītu, es domāju, un izveidoja Pivot tabulu, kas ir pilnīgi pārsteidzoša.Tāpēc pārbaudiet datu modeli nākamreiz, kad starp divām tabulām jāveic VLOOKUP. Šis un visi pārējie 40 padomi ir grāmatā. Noklikšķiniet uz “i” augšējā labajā stūrī. Jūs varat iegādāties grāmatu, jums ir pilnīga atsauce uz visu šo videoklipu sēriju, visu augustu, visu septembri, heck, mēs pat varam pārnest uz oktobri, lai viss tiktu paveikts.
Labi, atkārtojiet šodien: sākot ar programmu Excel 2013, rakurstabulas dialoglodziņš piedāvā kaut ko, ko sauc par datu modeli, tas ir Power Pivot motora koda vārds. Pirms izveidojat Pivot tabulas, veiciet taustiņu kombināciju Ctrl + T, lai izveidotu tabulu no katras darbgrāmatas, es veltīju papildu laiku katras nosaukumam. No pirmās tabulas izveidojiet rakurstabulu un pēc tam lauku sarakstā dodieties uz augšu un pārejiet no Aktīva uz Visu. Uzmeklēšanas tabulā izvēlieties lauku, un pēc tam tas jūs brīdinās, ka 2013. gadā jums vai nu ir jāizveido attiecības, vai arī AutoDetect, jums jānoklikšķina uz CREATE. Bet tas ir kas, 4 klikšķi, lai to izveidotu, 5, ja saskaita pogu Labi, tik tiešām ļoti viegli izdarāms.
Alright, Colin, Michael un Alejandro Quiceno grāmatām ieteica Power Pivot kopumā, paldies viņiem, paldies jums, ka apstājāties, mēs jūs redzēsim nākamreiz, lai saņemtu citu netcast no
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2014.xlsx
