Šodienas jautājums no Kventina, kurš bija manā Atlanta Power Excel seminārā. Kventinam katram no 1000+ klientiem programmā Excel ir jāveido tie paši 7 aptaujas jautājumi.
Kā redzat šajā attēlā, klienti atrodas A. Jautājumi, kas jāatkārto, ir D slejā.
Jūs to varētu atrisināt ar VBA vai formulām, taču šī ir Power Query nedēļa plkst., Tāpēc es izmantoju foršu triku Power Query.
Ja vēlaties, lai starp katru aptauju būtu tukša rinda, pievienojiet kārtas numuru un pēc pēdējā jautājuma pievienojiet skaitli 7.
Abās datu kopās nospiediet Ctrl + T. Nosauciet otro datu kopu ar nosaukumu, kuru jūs atceraties, piemēram, Jautājumi vai Aptauja.
No otrās datu kopas izmantojiet Dati, No tabulas.
Tiek atvērts Power Query redaktors. Cilnē Sākums izvēlieties nolaižamo izvēlni Aizvērt un ielādēt un izvēlieties Aizvērt un ielādēt …. Nākamajā dialoglodziņā izvēlieties Tikai izveidot savienojumu.
Tagad esat atgriezies programmā Excel. Ailē A izvēlieties jebkuru šūnu klienta tabulā. Dati no tabulas. Kad tiek atvērts vaicājumu redaktors, lentē noklikšķiniet uz cilnes Pievienot kolonnu un pēc tam izvēlieties Pielāgota kolonna. Formula ir =#"Questions"(ieskaitot # un pēdiņas).
Redaktorā tiek parādīta jauna kolonna ar vērtību Tabula atkārtojumu katrā rindā. Slejas galvenē noklikšķiniet uz ikonas Izvērst.
Tabulā izvēlieties abus laukus. Cilnē Sākums izvēlieties Aizvērt un ielādēt.
Tiks parādīta jauna darblapa ar 7 jautājumiem, kas atkārtoti katram no vairāk nekā 1000 klientiem.
Skatīties video
Video atšifrējums
Uzziniet Excel no Podcast epizodes 2205: aizdevuma aptaujas eksplozija.
Sveiki, laipni lūgti atgriezties netcast ēterā, es esmu Bils Jelens. Tieši vakar 2204. sērijā Kaylee no Nashville bija jāizdara sprādziens VLOOKUP - par katru priekšmetu šeit D slejā mums bija atbilstoša virkne priekšmetu G slejā un vajadzēja tos eksplodēt. Tātad, ja Palace C būtu 8 priekšmeti, mēs iegūtu 8 rindas.
Tagad, šodien, mums ir Kventins. Tagad Kventins bija manā seminārā Atlantā, bet viņš patiesībā ir no Floridas, un Kventinam šeit ir gandrīz 1000 klienti - labi, vairāk nekā 1000 klientu - A slejā, un katram klientam viņam ir jāizveido šī aptauja. - šī aptauja ar 1, 2, 3, 4, 5, 6 jautājumiem. Ko es darīšu šeit, es pievienošu kārtas numuru tikai ar skaitļiem no 1 līdz 7, lai tādā veidā es varētu izveidot jauku tukšu rindu starp tām. Abas šīs datu kopas izveidošu tabulā; Tātad, mēs cenšamies panākt, lai šīs 7 rindas eksplodētu katram no šiem 1000 klientiem. Tas ir mērķis.
Tagad es to varu izdarīt ar VPA; Es to varu izdarīt ar formulām; bet šeit ir sava veida "Power Query nedēļa", mēs esam palaiduši šo, tas ir mūsu trešais Power Query piemērs pēc kārtas, tāpēc es izmantošu Power Query. Es gatavošu šo kreiso atstāt galdā. Es būšu ļoti uzmanīgs, nosaucot šo nevis 1. tabulu. Es došu tam nosaukumu. Mums vēlāk šis vārds būs jāizmanto atkārtoti, tāpēc es to sauktu par jautājumiem - tāpat. Un tad šī būs 2. tabula, bet es pārdēvēšu to par klientu - tas nav tik svarīgi, ka es pārdēvēju šo, jo tas ir otrais, kam ir jābūt nosaukumam. Tātad, mēs to izvēlēsimies; Dati; un mēs teiksim no tabulas / diapazona. Iegūt un pārveidot datus - tas ir pazīstams kā Power Query. Tas ir iebūvēts programmā Excel 2016. Ja jums ir 2010. vai 2013. gads, operētājsistēmā Windowsne Mac, ne iOS, ne Android, jūs varat bez maksas lejupielādēt Power Query no Microsoft.
Tātad, mēs iegūsim datus no tabulas / diapazona; šeit ir mūsu galds - mēs nedarīsim tam neko, vienkārši aizveriet un ielādējiet; Aizvērt un ielādēt; tikai izveidot savienojumu; labi, un redzi, šī vaicājuma nosaukums ir Jautājumi. Tas lieto to pašu nosaukumu kā šeit. Un tad mēs atgriežamies pie šī, un, Dati; No tabulas / diapazona; Tātad, tur ir saraksts ar mūsu 1000 vai vairāk klientiem.
Sveiki, šeit ir kliegšana Migelam Eskobaram, manam draugam, kurš ir M Is For (DATA) MONKEY līdzautors. Es ievietoju saiti uz to videoklipā - lieliska grāmata par Power Query - man palīdzēja šajā jautājumā. Mēs ievietosim pilnīgi jaunu pielāgotu kolonnu, un pielāgotās kolonnas formula ir šāda tieši šeit: = # "vaicājuma nosaukums". Es nekad to nebūtu izdomājis bez Migela, tāpēc paldies Migelam par to.
Un, kad es noklikšķinu uz Labi, jā, nešķiet, ka tas darbojās - mēs vienkārši iegūstam galdu, galdu, tabulu, bet tieši tas mums vakar bija ar Keilu un biļetēm. Un viss, kas man jādara, ir to paplašināt, un es patiesībā teikšu, ka man secība, iespējams, nav vajadzīga … labi, ieliksim to katram gadījumam. Mēs to varam izņemt, kad esam to redzējuši. Šobrīd mums ir 1000 rindas, un tagad mums ir 7000 rindas - skaistas. Tagad es redzu, ka tas parādās secībā, tāpēc man tas nav vajadzīgs. Ar peles labo pogu noklikšķiniet un noņemšu tikai šo vienu kolonnu. Un tad es varu Mājas; Aizvērt & ielādēt; un BAM! - mums tagad vajadzētu būt vairāk nekā 7000 rindām ar 6 jautājumiem un tukšu vietu katram klientam. Kventins bija sajūsmināts par šo seminārā. Foršs, foršs triks - izvairās no VBA, izvairās no daudzām formulām, izmantojot indeksu,un tamlīdzīgas lietas - lielisks veids, kā iet.
Bet, hei, šodien ļaujiet man jūs aizsūtīt ar M Is For (DATA) MONKEY. Kens Puls un Migels Eskobars uzrakstīja lielāko grāmatu par Power Query. Es mīlu šo grāmatu; pēc 2 stundām jūs kļūsiet par profesionāli ar šo grāmatu.
Labi, tātad, šodien - Quentin ir jāveido identiska aptauja 1000 dažādiem klientiem. Katram klientam ir 6 vai 7 vai 8 jautājumi. Tagad mēs to varētu izdarīt ar VBA vai makro, taču, tā kā šeit tiek palaists Power Query, veiksim Power Query. Jautājumiem pievienoju papildu tukšu jautājumu; Es pievienoju kārtas numuru, lai pārliecinātos, ka tur paliek tukšs; padarīt klientus par galdu; veidojiet jautājumus tabulā; ir ļoti svarīgi, lai jūs nosauktu jautājumus kaut ko tādu, ko jūs atceraties - es saucu savus jautājumus par “jautājumiem”. Pievienojiet jautājumus Power Query, tikai kā savienojumu; un pēc tam, pievienojot klientus Power Query, izveidojiet jaunu pielāgotu kolonnu, kur formula ir: # "pirmā vaicājuma nosaukums" un pēc tam izvērsiet šo kolonnu Power Query redaktorā; Aizvērt unIelādējiet atpakaļ izklājlapā, un viss ir pabeigts. Apbrīnojams triks - man patīk Power Query - vislielākā lieta, kas ar Excel notikusi 20 gadu laikā.
Es gribu pateikties Kventinam par parādīšanos manā seminārā. Viņš jau pāris reizes ir bijis manā seminārā - lielisks puisis. Es gribu pateikties, ka apstājāties. Tiksimies nākamreiz, lai skatītu citu netcast raidījumu no.
Lejupielādējiet Excel failu
Lai lejupielādētu Excel failu: loan-survey-explosion.xlsx
Power Query mani turpina pārsteigt. Lai uzzinātu vairāk par Power Query, izlasiet grāmatu M, kas paredzēta datu mērkaķim.
Excel dienas doma
Esmu lūdzis saviem Excel Master draugiem padomu par Excel. Šodienas doma apdomāt:
"Ar AGGREGATE jūs varat darīt visu, izņemot to saprast."
Liams Bastiks
