Existuje vražedný vzorec na extrahovanie jedinečných hodnôt, ktoré zodpovedajú kritériám. Vyžaduje Ctrl + Shift + Enter, ale je výkonný a super.
Tento príklad presahuje rámec tejto knihy. V programe Excel existuje tajný typ vzorca, ktorý vyžaduje stlačenie kombinácie klávesov Ctrl + Shift + Enter, aby ste odomkli právomoci vzorca.
Ak by ste vy alebo ja potrebovali získať jedinečný zoznam hodnôt zo stĺpca B, urobili by sme niečo ako napríklad použitie pokročilého filtra alebo kontingenčnej tabuľky alebo kopírovanie údajov a použitie príkazu Odstrániť duplikáty. Tieto metódy trvajú päť sekúnd a sú jednoduché pre vás alebo pre mňa.
Problém vyvrcholí, keď potrebujete, aby manažér vášho manažéra použil tabuľku. Nemôžete dúfať, že viceprezident pre predaj bude ovládať kopírovanie a odstraňovanie duplikátov. Potrebujete spôsob, ako mať živé vzorce, ktoré budú vždy extrahovať jedinečné zoznamy hodnôt.
Vzorce, ako to dosiahnuť, sú absolútne šialené. Ale fungujú. Na nasledujúcom obrázku dlhý vzorec v D2 určuje, koľko jedinečných hodnôt je v zozname. Ešte dlhší vzorec v D5, ktorý je skopírovaný nadol, extrahuje jedinečný zoznam.
Tu je vzorec. Nepokúsim sa ti to vysvetliť.
Urobím však ďalšiu najlepšiu vec. Predstavím vám niekoho, kto vám to môže vysvetliť. Mike Girvin vytvoril na kanáli ExcelisFun tisíce videí programu Excel na YouTube. Napísal tiež niekoľko kníh Excel, vrátane Ctrl + Shift + Enter - kompletného sprievodcu týmito úžasnými vzorcami. V knihe Mike podrobne vysvetľuje tento vzorec a mnoho ďalších vzorcov, aby ste pochopili, ako fungujú, a napísali svoje vlastné.
Ak sa niekedy chystáte vzdať vzorca, pretože sa to nedá urobiť, existuje veľká šanca, že vzorce v Mikeovej knihe to vyriešia.
Ctrl + Shift + Enter »
Ďakujeme Mikeovi Girvinovi, Olge Kryuchkovej a @canalyze_it za navrhnutie tejto funkcie.
Pri propagácii knihy Mika Girvina by som mal spomenúť, že by ste si mali pozrieť kanál YouTube ExcelisFun na YouTube, kde má tisíce bezplatných úžasných videí. S Mikeom sme vytvorili sériu zábavných videí o súbojoch v programe Excel, kde si ukážeme rôzne spôsoby riešenia problémov v programe Excel.
Dalo by sa povedať, že Mike je Elvis z Excelu.
Pozeraj video
- Ako získať zoznam jedinečných hodnôt
- Pokročilý filter iba s jedinečnými hodnotami
- Kontingenčná tabuľka
- Vzorec pre podmienené formátovanie
=COUNTIF(G$1:G1,G2)=0
- Odstrániť duplikáty
- Pole vzorca z knihy Mika Girvina
Prepis videa
Naučte sa Excel z podcastu, epizóda 2045 - Extrahujte jedinečnosti!
Kliknutím na písmeno „i“ v pravom hornom rohu sa dostanete do zoznamu skladieb a podcastujete všetky moje tipy z tejto knihy!
Dobre, takže našim dnešným cieľom je, aby sme tu mali databázu zákazníkov. Chcem získať jedinečný zoznam zákazníkov, iba zákazníkov v tomto zozname. A starý, starý spôsob, ako to urobiť, je použiť pokročilý filter, vybrať údaje, prejsť na údaje, filter, rozšírené, povedať, že ich chceme skopírovať na dané miesto. Miesto, ktoré chceme skopírovať, má požadovaný nadpis a chcem iba jedinečné záznamy, kliknite na tlačidlo OK a v tomto zozname sa nachádzajú zákazníci, jedinečný zoznam zákazníkov, úžasné, že? Rýchlejšie, vložte, kontingenčná tabuľka, existujúci pracovný hárok, kliknite na OK a začiarknite políčko Zákazník, BAM, existuje jedinečný zoznam zákazníkov.
Ahoj, podmienené formátovanie, podmienené formátovanie to údajne robí. Podmienené formátovanie, zvýraznenie buniek, duplikovanie hodnôt, výber jedinečných hodnôt, kliknutie na tlačidlo OK a absolútne sa nič nestane. Je to preto, že ktokoľvek vytvoril túto funkciu, nepoužíva rovnakú verziu angličtiny ako ja. Pre nich je jedinečná hodnota hodnota, ktorá sa objaví presne raz a iba raz, v poriadku, potom ju označia, ak sa objaví 2 - 3 - 4 - 5-krát, neoznačia ju vôbec, to nie je užitočné na všetko. Ale hej, tu stlačím kláves Tab, ak to naozaj chcete urobiť pomocou podmieneného formátovania a neviem, prečo by ste to mohli urobiť, mohli sme to urobiť veľmi ľahko. alt = "" OD, vytvorte nové pravidlo, použite vzorec a vzorec hovorí: "Spočítajte všetko od riadku 1 po riadok tesne nad nami, či sa to rovná tomuto,ak sa táto položka nikdy predtým neobjavila, označte ju červenou farbou. “ a dostaneme jedinečné hodnoty navrchu. Potom môžete prejsť na Údaje - vlastne tu, klikneme pravým tlačidlom myši, Triedime a povedzme Na začiatok vložme vybranú farbu bunky a tento jedinečný zoznam sa presunie na začiatok. Všetky tieto bledé v porovnaní s tým, čo nám poskytli v programe Excel 2010. Uistite sa, že ste vytvorili kópiu údajov, dovoľte mi urobiť kópiu údajov, Ctrl + C, poďte sem, Ctrl + V a potom odstráňte duplikáty , klikni na OK, BAM, nič nie je rýchlejšie!dovoľte mi urobiť kópiu údajov, Ctrl + C, poďte sem, Ctrl + V a potom odstráňte duplikáty, kliknite na OK, BAM, nič nie je rýchlejšie!dovoľte mi urobiť kópiu údajov, Ctrl + C, poďte sem, Ctrl + V a potom odstráňte duplikáty, kliknite na OK, BAM, nič nie je rýchlejšie!
Ale tu je vec, či už chcete použiť rozšírený filter, kontingenčné tabuľky, tvrdé podmienené formátovanie alebo odstránenie duplikátov, všetko je nad možnosti manažéra vášho manažéra. Správne, nikdy ich nebudeš môcť k tomu prinútiť. Takže niekedy to musíte byť schopní urobiť pomocou vzorca a tento vzorec je z mojej súčasnej knihy „Ctrl + Shift + Enter“. Aby to fungovalo, je potrebné stlačiť Ctrl + Shift + Enter, je to šialene úžasný vzorec. A vo svojej knihe „MrExcel XL“ som chcel len poukázať na to, že v tejto knihe je úžasná sada vzorcov. Ak musíte niečo urobiť, napríklad extrahovať jedinečný zoznam zákazníkov alebo extrahovať všetkých zákazníkov, ktorí sa zhodujú s určitým zoznamom, v Mikeovej knihe je celá kapitola o tom, ako to urobiť. Normálne by som vás požiadal, aby ste si dnes kúpili moju knihu.Nemyslím si, že by ste si mali kúpiť moju knihu, myslím si, že by ste si mali kúpiť Mikeovu knihu, kliknutím na písmeno „i“ v pravom hornom rohu sa k nim dostanete.
Dnes sme hovorili o tom, ako získať jedinečné hodnoty, je to rozšírený filter, kontingenčná tabuľka, vzorec podmieneného formátovania alebo najjednoduchší spôsob odstránenia duplikátov. Ak však potrebujete získať zoznam jedinečných hodnôt bez toho, aby manažér správcu urobil nejaké kroky v programe Excel, existuje vzorec poľa, ktorý tento problém vyrieši.
No hej, chcem sa ti poďakovať, že si sa zastavil, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2045.xlsm