Eilutės suskirstymas pagal kai kuriuos žodžius buvo viena iš mano pagrindinių duomenų analizės užduočių. Pavyzdžiui, apklausoje, jei paklaustumėte žmonių, kas jiems patinka tam tikrame išmaniajame telefone, tie patys atsakymai turi įvairius žodžius. Kalbant apie fotoaparatą, jie gali naudoti tokius žodžius kaip nuotraukos, vaizdo įrašai, asmenukės ir pan. Jie visi reiškia kamerą. Taigi labai svarbu sakinius suskirstyti į kategorijas anksčiau, kad gautumėte prasmingos informacijos.
Šiame straipsnyje mes išmoksime suskirstyti „Excel“ į kategorijas naudojant raktinius žodžius.
Paimkime apklausos, apie kurią kalbėjome, pavyzdį.
Pavyzdys: suskirstykite duomenis, surinktus iš „Excel“ apklausos
Taigi, mes atlikome apklausą apie mūsų naująjį išmanųjį telefoną „xyz“. Mes paklausėme savo klientų, kas jiems patinka „xyz“ telefone, ir užfiksavome jų atsakymą „Excel“. Dabar turime žinoti, kam patiko mūsų LED ekranas, garsiakalbis ir fotoaparatas.
Norėdami tai padaryti, mes paruošėme raktinių žodžių, galinčių nurodyti kategoriją, sąrašą, kaip matote žemiau esančiame paveikslėlyje. Kad suprasčiau, aš laikiau jį mažą.
Atsiliepimai yra diapazone A2: A9, raktiniai žodžiai yra E2: E10, o kategorija - F2: F10.
Bendra kategorijų kūrimo formulė bus tokia:
{= INDEX (kategorija, MATCH (TRUE, ISNUMBER (SEARCH (žodžiai, sakinys))), 0))}
Atkreipkite dėmesį į garbanotas petnešas, tai yra masyvo formulė. Reikia įvesti naudojant CTRL+SHIFT+ENTER.
Kategorija: Tai yra diapazonas, kuriame yra kategorijų sąrašas. Sakiniai ar atsiliepimai bus suskirstyti pagal šias vertes. Mūsų atveju tai yra F2: F10.
Žodžiai: tai diapazonas, kuriame yra raktinių žodžių ar frazių sąrašas. Jų bus ieškoma sakiniai. Čia yra E2: E10.
Nuosprendis: tai sakinys bus klasifikuojamas. Tai yra viena santykinė ląstelė.
Kadangi dabar žinome kiekvieną kintamąjį ir funkciją, naudojamą „Excel“ kategorizavimui, įgyvendinkime tai savo pavyzdžiu.
B2 langelyje parašykite šią formulę ir paspauskite CTRL+SHIFT+ENTER.
{= INDEKSAS ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0))}
Nukopijuokite formulę, kad pamatytumėte kiekvieno atsiliepimo kategoriją.
Turime išvardyti ištaisytus žodžius ir kategorijas, jie neturėtų keistis, kai nukopijuojame formulę, todėl pateikiau absoliučią raktinių žodžių ir kategorijų nuorodą. Nors norime, kad sakiniai pasikeistų, kai nukopijuojame formulę, todėl aš naudoju santykinę nuorodą kaip A2. Čia galite perskaityti supratimą apie nuorodą „Excel“.
Dabar galite parengti ataskaitą, kiek vartotojų mėgsta LED ekraną, garsiakalbį ir fotoaparatą.
Kaip tai veikia?
Formulės esmė yra ISNUMBER (SEARCH($ 2 USD: 10 USD, A2)): Aš čia išsamiai paaiškinau. Funkcija SEARCH ieško kiekvienos raktinių žodžių vertės (2 USD: 10 USD) A2 sakinyje. Tai grąžina rasto žodžio arba masyvo vietos masyvą #VERTĖ (nes žodis nerastas). Galiausiai šiame pavyzdyje turėsime 9 elementų masyvą. {#VALUE!; 5; #VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}. Toliau mes naudojame funkciją ISNUMBER, kad šį masyvą paverstume naudingais duomenimis. Jis paverčia jį į TIESA ir NETIESA masyvą. {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Dabar viskas yra paprasta rodyklės atitiktis. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): funkcija MATCH ieško TRUE, gauto masyvo ir grąžina pirmą kartą rastos TRUE indeksą. kuris šiuo atveju yra 2.
INDEKSAS(2 USD: 10 USD, MATCH (TRUE, ISNUMBER (PAIEŠKA ($ E $ 2: $ E $ 10, A2)), 0)): Toliau funkcija INDEX žiūri į antrąją poziciją kategorijoje (2 USD: 10 USD) kuris yra LED ekranas. Galiausiai ši formulė šį tekstą ar atsiliepimą priskiria LED ekranui.
Skiriant didžiosios ir mažosios raidės:
Jei norite, kad ši funkcija skirtųsi didžiosiomis ir mažosiomis raidėmis, vietoj funkcijos „SEARCH“ naudokite FIND funkciją. Funkcija FIND pagal numatytuosius nustatymus skiria didžiąsias ir mažąsias raides.
{= INDEX (Kategorija, MATCH (TRUE, ISNUMBER (FIND (žodžius,sakinys)),0))}
Silpnos vietos:
1. Jei du raktiniai žodžiai yra tame pačiame sakinyje, sakinys bus suskirstytas į kategorijas pagal pirmąjį raktinį žodį sąraše.
Teksto fiksavimas kitu žodžiu. Tarkime, kad mes ieškome LAD diapazone. Tada bus skaičiuojami žodžiai, kuriuose yra LAD. Pavyzdžiui, kopėčios bus skaičiuojamos pagal LAD, nes jose yra LAD. Taigi būkite atsargūs. Geriausia praktika yra kiek įmanoma normalizuoti duomenis.
Taigi tai buvo greita pamoka apie tai, kaip suskirstyti duomenis į „Excel“. Stengiausi kuo paprasčiau paaiškinti. Praneškite man, jei turite kokių nors abejonių dėl šio straipsnio ar kitų su „Excel“ susijusių straipsnių.
Atsisiųsti failą:
Klasifikuoti eilutes pagal raktinius žodžiusKaip patikrinti, ar langelyje yra tam tikras tekstas „Excel“
Kaip patikrinti tekstų sąrašą eilutėje „Excel“
Gaukite „COUNTIFS“ dviejų kriterijų atitiktį „Excel“
Gaukite „COUNTIFS“ naudodami kelis kriterijus arba „Excel“
Populiarūs straipsniai:
50 „Excel“ spartusis klavišas produktyvumui padidinti : Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis t„VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti funkciją „COUNTIF“ programoje „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudotis „SUMIF“ funkcija „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.