Problém
Máme seznam alfanumerických kódů. Každý kód se skládá z jednoho písmene (A, B, C atd.), Za nímž následuje 3místné číslo. Tyto kódy by se měly objevit v abecedním pořadí, ale někdy jsou mimo pořadí. Chceme označit kódy mimo sekvenci.
Výzva č. 1
Jaký vzorec ve sloupci „Zkontrolovat“ umístí „x“ vedle kódu, který je mimo pořadí? V této výzvě kontrolujeme pouze to, že * číselná * část kódu je mimo pořadí, ne to, že samotné písmeno je mimo pořadí.
Výzva č. 2
Jak lze výše uvedený vzorec rozšířit a zkontrolovat, zda je „alfa“ část kódu (A, B, C atd.) Mimo pořadí? Například bychom měli označit kód, který začíná na „A“, pokud se objeví za kódem, který začíná na „C“ nebo „B“.
Stáhněte si níže uvedený list a přijměte výzvu!
Poznámka: v sešitu jsou 2 listy, jeden pro výzvu č. 1, druhý pro výzvu č. 2.
Nápověda - Toto video ukazuje několik tipů, jak vyřešit problém, jako je tento.
Předpoklady
- Všechny kódy vždy obsahují čtyři znaky: 1 velké písmeno + 3 čísla.
- Počet kódů na písmeno je náhodný, ale v numerických hodnotách by neměly být žádné mezery.
- Je pouze nutné označit první kód písmenem mimo pořadí, ne všechny následující kódy.
Zde je několik pracovních řešení. Je důležité si uvědomit, že existuje mnoho, mnoho způsobů řešení běžných problémů v aplikaci Excel. Odpovědi níže jsou jen moje osobní preference. Ve všech níže uvedených vzorcích lze kliknout na názvy funkcí, pokud potřebujete další informace.
Výzva č. 1
Původně jsem šel s tímto vzorcem:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Poznámka MID vrací text. Přidáním 1 a přidáním nuly dostaneme Excel k převedení textu na číslo. Násobení uvnitř logického testu uvnitř IF používá logickou logiku, aby se zabránilo dalšímu vnořenému IF. Nejsem si jistý, proč jsem nepoužil PRAVÉ, což by také fungovalo dobře.
Všimněte si také LEFT nevyžaduje počet znaků a vrátí první znak, pokud není uveden.
Na základě některých chytrých odpovědí níže můžeme optimalizovat o něco více:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
Zde matematická operace odečtení MID od MID automaticky přenese textové hodnoty na čísla.
Výzva č. 2
Pro toto řešení jsem použil několik vnořených IF (konce řádků přidány kvůli čitelnosti):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
Udělal jsem to proto, že první test LEFT (B5) = LEFT (B6) určuje, zda kontrolujeme čísla nebo písmena. Pokud je první znak stejný, kontrolujeme čísla uvedená výše. Pokud ne, kontrolujeme pouze první písmeno.
Funkce CODE vrátí číslo ASCII prvního znaku, pokud textový řetězec obsahuje více než 1 znak. Cítí se to jako hack a kód je snad méně srozumitelný, ale funguje :)
Pokud to uráží vaši citlivost, použijte LEFT jako výše v CODE, abyste dodali pouze první znak.
