2024 Autor: Peter John Melton | [email protected]. Naposledy změněno: 2023-12-16 04:44
V nedávném článku jsme představili funkci Excel nazvanou VLOOKUP a vysvětlil, jak může být použita k načtení informací z databáze do buňky v místním pracovním listu. V tomto článku jsme zmínili, že pro VLOOKUP existují dvě možnosti využití a pouze jedna z nich se zabývá dotazováním databází. V tomto článku, druhém a posledním z řady VLOOKUP, zkoumáme toto další méně známé použití funkce VLOOKUP.
Pokud jste tak ještě neučinili, přečtěte si prosím první článek VLOOKUP - tento článek předpokládá, že mnohé pojmy vysvětlené v tomto článku jsou čtenáři již známy.
Při práci s databázemi získává VLOOKUP "jedinečný identifikátor", který slouží k identifikaci záznamu, který chceme v databázi najít (např. Kód produktu nebo ID zákazníka). Tento jedinečný identifikátor musí existují v databázi, jinak nám VLOOKUP vrátí chybu. V tomto článku budeme zkoumat způsob použití VLOOKUP, kde identifikátor nemusí vůbec existovat v databázi. Je to téměř tak, jako by VLOOKUP mohl přijmout přístup "dost blízko a je dost dobrý", abychom vrátili data, která hledáme. Za určitých okolností to je přesně tak Co potřebujeme.
Tento článek ilustrujeme příkladem reálného světa - výpočtem provizí, které jsou generovány na základě údajů o prodeji. Začneme velmi jednoduchým scénářem a postupně jej budeme složitější, dokud jediným racionálním řešením problému nebude použití VLOOKUPu. Počáteční scénář v naší fiktivní společnosti funguje takto: Pokud prodejce vytvoří v daném roce více než 30 000 dolarů za prodej, provize, které vydělávají z tohoto prodeje, činí 30%. V opačném případě je jejich provize pouze 20%. Zatím je to docela jednoduchý pracovní list:
Buňka B2 obsahuje jedinou zajímavou část tohoto pracovního listu - vzorec pro rozhodnutí o tom, jakou měnovou sazbu použijete: jeden níže hranice 30 000 dolarů nebo jedna výše prahu. Tento vzorec využívá funkci Excel nazvanou LI. Pro ty čtenáře, kteří nejsou s IF známí, funguje to takto:
IF(condition,value if true,value if false)
Kde stav je výraz, který hodnotí buď skutečný nebo Nepravdivé. Ve výše uvedeném příkladu stav je výraz B1, který lze přečíst jako "Je B1 méně než B5?", nebo jiným způsobem, "Jsou celkové prodeje nižší než práh". Pokud je odpověď na tuto otázku "ano" (pravda), pak použijeme pokud je pravda parametr funkce, jmenovitě B6 v tomto případě - sazba provize, pokud byl celkový prodej níže prahu. Pokud je odpověď na otázku "ne" (nepravda), pak použijeme hodnota, pokud je falešná parametr funkce, jmenovitě B7 v tomto případě - sazba provize, pokud byl celkový prodej výše prahu.
Jak můžete vidět, pomocí prodejních celků ve výši 20 000 dolarů nám dáváme provizi ve výši 20% v buňce B2. Pokud zadáme hodnotu 40 000 USD, získáme jinou sazbu provize:
Zkusme to složitější. Představme druhou prahovou hodnotu: Pokud obchodník vydělá více než 40 000 USD, jejich sazba provize se zvýší na 40%:
Snadno pochopitelné v reálném světě, ale v buňce B2 je náš vzorec stále složitější. Pokud se podíváte pozorně na vzorec, uvidíte, že třetí parametr původní funkce IF ( hodnota, pokud je falešná) je nyní celá IF funkce sama o sobě. Toto se nazývá a vnořené funkce (funkce v rámci funkce). Je dokonale platný v aplikaci Excel (dokonce funguje!), Ale je těžší číst a rozumět.
Nebudeme jít do ořechů a šroubů, jak a proč to funguje, ani nebudeme zkoumat nuance vnořených funkcí. Toto je výukový program pro VLOOKUP, nikoliv pro Excel obecně.
Každopádně to zhoršuje! A co když se rozhodneme, že pokud vydělávají více než 50 000 dolarů, pak mají nárok na 50% provizi a pokud vydělávají více než 60 000 dolarů, mají nárok na 60% provizi?
Určitě je. VLOOKUP na záchranu!
Přeměňme si list trochu. Budeme uchovávat všechny stejné postavy, ale organizovat je novým způsobem, více tabelární způsob:
Udělejte si chvíli a ověřte si, že nový Tabulka hodnot pracuje přesně jako série výše uvedených prahových hodnot.
Koncepčně, to, co se chystáme udělat, je použít VLOOKUP, aby vyhledal celkový prodejní prodejce (od B1) v tabulce sazeb a vrátil nám odpovídající sazbu provize. Všimněte si, že prodejce skutečně vytvořil tržby, které jsou ne jednu z pěti hodnot v tabulce sazeb ($ 0, $ 30,000, $ 40,000, $ 50,000 nebo $ 60,000). Mohli vytvořit tržby ve výši 34.988 dolarů. Je důležité si uvědomit, že 34.988 dolarů ne v tabulce sazby. Podívejme se, jestli VLOOKUP může vyřešit náš problém …
Vybíráme buňku B2 (umístění, kterou chceme dát do vzorce) a potom vložíme funkci VLOOKUP z Vzorce záložka:
The Funkční argumenty Zobrazí se pole pro VLOOKUP. Argumenty (parametry) vyplňujeme jeden po druhém, počínaje zadáním příkazu Vyhledávací_hodnota, což je v tomto případě celkový prodej z buňky B1. Kurzor umístíme do okna Vyhledávací_hodnota pole a potom jednou klikněte na buňku B1:
Dále musíme specifikovat, do VLOOKUPu, jakou tabulku vyhledáváme tato data. V tomto příkladu je samozřejmě tabulka sazby. Kurzor umístíme do okna Table_array pole a pak zvýrazněte celou tabulku sazby - s výjimkou okruhů:
Dále musíme určit, který sloupec v tabulce obsahuje informace, které chceme, aby se náš vzorec vrátil k nám. V tomto případě chceme míru provize, která se nachází ve druhém sloupci tabulky, a proto zadáme a 2 do Col_index_num pole:
Nakonec zadáme hodnotu v Range_lookup pole.
Důležité: Použití tohoto pole odlišuje dva způsoby použití VLOOKUP. Chcete-li použít VLOOKUP s databází, Range_lookup, musí být vždy nastavena na NEPRAVDIVÉ, ale s tímto dalším používáním VLOOKUPu musíme buď nechat prázdné nebo zadat hodnotu SKUTEČNÝ. Při použití funkce VLOOKUP je důležité, abyste správně zvolili tento konečný parametr.
Abychom byli explicitní, zadáme hodnotu skutečný v Range_lookup pole. Také by bylo dobré nechat to prázdné, protože je to výchozí hodnota:
Dokončili jsme všechny parametry. Nyní klikněte na tlačítko OK a Excel sestaví náš vzorec VLOOKUP pro nás:
Závěr
V "databázi" verze VLOOKUP, kde Range_lookup parametr je NEPRAVDIVÉ, hodnota předaná v prvním parametru (Vyhledávací_hodnota) musí být v databázi. Jinými slovy, hledáme přesnou shodu.
Ale v tomto dalším použití VLOOKUPu nemusíme nutně hledat přesnou shodu. V takovém případě je "dost blízko". Ale co tím myslíme "dostatečně blízko"? Použijeme příklad: Při hledání provize na prodejní částce 34.988 dolarů nám náš vzorec VLOOKUP vrátí hodnotu 30%, což je správná odpověď. Proč si zvolil řádek v tabulce obsahující 30%? Co vlastně znamená v tomto případě "dostatečně blízko"? Buďme přesní:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
Je také důležité poznamenat, že pro tento systém fungovat, tabulka musí být ve sloupci 1 seřazeny ve vzestupném pořadí!
Pokud chcete pracovat s VLOOKUP, ukázkový soubor ilustrovaný v tomto článku si můžete stáhnout zde.
Doporučuje:
Rychlý tip: Databáze automaticky kompaktního přístupu
Ačkoli je nepravděpodobné, že někdy narazíte na maximální velikost databáze aplikace Access velikosti 2 GB, je stále dobré vědět, že můžete nastavit, aby vaše databáze automaticky kompaktovala nebo zmenšovala velikost souboru. To platí zejména pro databáze, kde přidáváte a odstraňujete spoustu dat pokaždé.
Proč byste se měli obávat, kdykoli dojde k úniku databáze hesel služby
Realita je, že kompromisy s databázemi heslem jsou obavy, bez ohledu na to, jak se společnost může pokusit točit. Existuje však několik věcí, které můžete udělat, abyste se izolovali, bez ohledu na to, jak jsou špatné bezpečnostní postupy společnosti.
Použijte nástroj BGInfo k vytvoření databáze systémových informací vašich síťových počítačů
Jeden z nejoblíbenějších nástrojů sady Sysinternals mezi správci systému je BGInfo, který při přihlašování přistupuje k systémovým informacím v reálném čase k ploše. Ze zřejmých důvodů má informace, jako je systémová paměť, dostupný prostor na pevném disku a systémový čas (mimo jiné), který je přímo před vámi, velmi výhodné, když spravujete několik systémů.
Jak zálohovat databáze SQL do síťového sdílení
Zálohování databází SQL pravidelně je nutné. Již jsme se zabývali způsoby, jak snadno zálohovat všechny vaše SQL serverové databáze na lokální pevný disk, ale to neochrání proti selhání jednotky a / nebo systému. Jako dodatečnou vrstvu ochrany proti tomuto typu katastrofy můžete kopírovat nebo přímo vytvářet zálohy na síťové sdílené položce.
Použití Simple Helper metody při vývoji Windows Phone 7.5 Mango Apps: Část 8
V tomto tutoriálu na vývoj softwaru Windows Phone 7.5 Mango Apps se dozvíme, jak používat jednoduché metody pomocníků, které uživatelům umožňují rozdělit kód na více kódových bloků.