3 Složité problémy extrakce Excelu vyřešeny a vysvětleny

Mnoho lidí se potýká s extrahováním informací ze složitých buněk v aplikaci Microsoft Excel. Mnoho komentářů a otázek v odpovědi na můj článek o tom, jak extrahovat číslo nebo text z aplikace Excel pomocí této funkce Jak extrahovat číslo nebo text z aplikace Excel pomocí této funkce Jak extrahovat číslo nebo text z aplikace Excel pomocí této kombinace funkcí čísel a text v tabulce aplikace Excel může představovat výzvy. Ukážeme vám, jak změnit formátování buněk a oddělit čísla od textu. dokazuje to. Zjevně není vždy jasné, jak izolovat požadovaná data z listu aplikace Excel.

Vybrali jsme několik otázek z tohoto článku, abychom zde projeli, abyste viděli, jak řešení fungují. Rychlé učení se Excelu 8 tipů, jak se rychle naučit Excel 8 tipů, jak se rychle naučit Excel Není s Excelem tak pohodlné, jak byste chtěli? Začněte jednoduchými tipy pro přidávání vzorců a správu dat. Postupujte podle tohoto průvodce a budete mít rychlost v žádném okamžiku. není snadné, ale použití skutečných problémů, jako jsou tyto, hodně pomáhá.

1. Extrakce pomocí separátoru

Reader Adrie položil následující otázku:

Chtěl bych extrahovat první sadu čísel ze seznamu, tj. (122,90,84,118,4,128,9)
Jakékoli nápady, jaký vzorec mohu použít?

COIL112X2.5
COIL90X2.5
COIL84X2.0
COIL118.4X1.8
COIL128.9X2.0

Skutečnost, že čísla, která mají být extrahována, mají různé délky, je o něco složitější než pouhým použitím funkce MID, ale kombinací několika různých funkcí se můžeme zbavit písmen vlevo i “X” a čísla vpravo, v nové buňce zůstanou pouze požadovaná čísla.

Zde je vzorec, který použijeme k vyřešení tohoto problému:

= HODNOTA (VLEVO ((VPRAVO (A1, (LEN (A1) -4))), FIND ("X", A1) -5))

Začněme uprostřed a vypracujeme cestu ven, abychom viděli, jak to funguje. Nejprve začneme s funkcí FIND. V tomto případě používáme FIND (“X”,A1). Tato funkce hledá text X v buňce A1 pro písmeno X. Když najde X, vrací pozici, ve které je. Pro první položku, například COIL112X2.5, vrátí 8. Pro druhou položku návraty 7.
Dále se podívejme na funkci LEN. Toto jednoduše vrátí délku řetězce v buňce minus 4. Kombinace toho, že s funkcí RIGHT dostaneme řetězec z buňky minus první čtyři znaky, které odstraní “CÍVKA” od začátku každé buňky. Vzorec pro tuto část vypadá takto: PRAVÝ (A1, (LEN (A1) -4)).

Další úroveň ven je funkce VLEVO. Nyní, když jsme určili polohu X pomocí funkce FIND a zbavili jsme se “CÍVKA” s funkcí VPRAVO vrátí funkce VLEVO, co zbývá. Pojďme to trochu dále rozebrat. Co se stane, když tyto dva argumenty zjednodušíme:

= VLEVO ("112X2,5", (8-5))

Funkce VLEVO vrátí tři znaky nejvíce vlevo v řetězci ( “-5” na konci argumentu zajišťuje, že správný počet znaků je eliminován zúčtováním prvních čtyř znaků v řetězci).

Nakonec funkce VALUE zajišťuje, že vrácené číslo je formátováno jako číslo, nikoli jako text. Tento příklad není tak zábavný jako budování pracovní hry Tetris v Excelu 7 Zábavné a podivné věci, které můžete vytvořit pomocí aplikace Microsoft Excel 7 Zábavné a podivné věci, které můžete vytvořit pomocí aplikace Microsoft Excel Představte si, že Excel byl zábavný! Excel nabízí dostatek prostoru pro projekty, které přesahují jeho zamýšlené použití. Jediným limitem je vaše představivost. Zde jsou nejkreativnější příklady toho, jak lidé používají Excel. , ale slouží jako skvělý příklad toho, jak kombinovat několik funkcí k vyřešení problému.

2. Vytažení čísel ze smíšených řetězců

Podobná otázka byla položena čtenářem Yadhu Nandanem:

Chci vědět, jak oddělit číselné a abecední znaky.

Příklad je - 4552dfsdg6652sdfsdfd5654

Chci 4552 6652 5654 v různých buňkách

Další čtečka Tim K SW poskytla perfektní řešení tohoto konkrétního problému:

Předpokládejme, že 4552dfsdg6652sdfsdfd5654 je v A1 a chcete, aby tato čísla byla extrahována do 3 různých buněk. 4452 v buňce B1 a 6652 v C1 a 5654 v D1 byste je použili.

B1:
= MID (A1,1,4)

C1:
= MID (A1,10,4)

D1:
= MID (A1,21,4)

Vzorce jsou poměrně jednoduché, ale pokud nejste obeznámeni s funkcí MID, možná nebudete rozumět, jak to funguje. MID bere tři argumenty: buňku, číslo znaku, kde začíná výsledek, a počet znaků, které by měly být vytaženy. Například MID (A1,10,4) říká Excelu, aby měl čtyři znaky počínaje desátým znakem v řetězci.

Použití tří různých funkcí MID ve třech buňkách vytáhne čísla z tohoto dlouhého řetězce čísel a písmen. Tato metoda samozřejmě funguje, pouze pokud máte v každé buňce vždy stejný počet znaků - písmen i číslic. Pokud se jejich počet liší, budete potřebovat mnohem komplikovanější vzorec.

3. Získání čísla ze smíšeného řetězce s mezerami

Jednou z nejobtížnějších žádostí zveřejněných v článku byla tato, od čtenáře Daryla:

Ahoj, chci se jen zeptat, jak oddělit velmi neformátovanou položku jako:

Rp. 487 500 (Netto 50% SLEVA)
Rp 256,500 Nett 40% SLEVA
Rp99 000
Rp 51 000 / orang Nett (50% sleva)

Strávil jsem nějaký čas prací na tomto, a nebyl jsem schopen přijít s řešením sám, tak jsem vzal do Reddit. Uživatel UnretiredGymnast poskytl tento vzorec, který je dlouhý a velmi složitý:

= SUMPRODUCT (MID (0 & LEFT (A1, IFERROR (SEARCH ("%", A1) -4, LEN (A1)))), LARGE (INDEX (ISNUMBER) - - MID (LEFT (A1, IFERROR (SEARCH ("%")) , A1) -4, LEN (A1))), ROW ($ 1: 99 $), 1)) * ROW ($ 1: 99 $), 0), ROW ($ 1: 99 $)) + 1,1) * 10 ^ ROW (1 $: 99 $) / 10)

Jak vidíte, dekódování tohoto vzorce trvá docela dlouho a vyžaduje docela solidní znalost mnoha funkcí Excelu 16 vzorců aplikace Excel, které vám pomohou vyřešit problémy v reálném životě 16 vzorců aplikace Excel, které vám pomohou vyřešit problémy v reálném životě Správný nástroj je polovina práce. Excel dokáže řešit výpočty a zpracovávat data rychleji, než najdete kalkulačku. Ukážeme vám klíčové vzorce aplikace Excel a ukážeme, jak je používat. . Abychom zjistili, co se přesně tady děje, musíme se podívat na několik funkcí, které nemusíte znát. První je IFERROR, který zachytí chybu (obvykle reprezentovanou znakem libry, jako je # N / A nebo # DIV / 0) a nahradí ji něčím jiným. Nahoře uvidíte IFERROR (SEARCH (“%”,A1) -4, LEN (A1)). Pojďme to rozebrat.

IFERROR se dívá na první argument, kterým je SEARCH (“%”,A1) -4. Takže když “%” se objeví v buňce A1, její umístění je vráceno a od ní se odečtou čtyři. Li “%” ne se objeví v řetězci, Excel vytvoří chybu a místo toho se vrací délka A1.

Ostatní funkce, které nemusíte znát, jsou o něco jednodušší; SUMPRODUCT například znásobuje a přidává prvky polí. LARGE vrací největší číslo v rozsahu. ROW v kombinaci se znakem dolaru vrací absolutní odkaz na řádek.

Zjistit, jak všechny tyto funkce spolupracují, není snadné, ale pokud začnete uprostřed vzorce a pracujete ven, začnete vidět, co to dělá. Bude to chvíli trvat, ale pokud máte zájem přesně vidět, jak to funguje, doporučujeme jej vložit do listu Excel a hrát si s ním. To je nejlepší způsob, jak získat představu o tom, s čím pracujete.

(Také nejlepším způsobem, jak se tomuto problému vyhnout, je importovat data úhledněji Jak importovat data do tabulek Excelu Úhledný a snadný způsob Jak importovat data do tabulek Excelu Úhledný a snadný způsob Už jste někdy zápasili s import nebo export dat do tabulky? Tento tutoriál vám pomůže zvládnout umění pohybujících se dat mezi Microsoft Excel, CSV, HTML a dalšími formáty souborů - není to vždy možnost, ale měla by to být vaše první volba, když je. )

Krok za krokem

Jak vidíte, nejlepší způsob, jak vyřešit jakýkoli problém s Excelem, je jeden krok po druhém: začněte tím, co víte, uvidíte, co vás dostane, a jděte odtamtud. Někdy skončíte s elegantním řešením a někdy dostanete něco, co je opravdu dlouhé, chaotické a složité. Ale pokud to funguje, uspěli jste!

A nezapomeňte požádat o pomoc Potřebujete se učit Excel? 10 odborníků vás naučí zdarma! Potřebujete se naučit Excel? 10 odborníků vás naučí zdarma! Naučit se používat pokročilé funkce aplikace Excel může být obtížné. Abychom to trochu usnadnili, vystopovali jsme nejlepšího gurua Excelu, který vám pomůže zvládnout Microsoft Excel. . Tam jsou někteří extrémně talentovaní uživatelé Excelu a jejich pomoc může být neocenitelná při řešení těžkého problému.

Máte nějaké návrhy na řešení těžkých problémů s extrakcí? Víte o nějakých dalších řešeních problémů, které jsme řešili výše? Sdílejte je a jakékoli myšlenky, které máte v komentářích níže!