Sledujte video - Použití Intersect Operator v Excelu
Intersect Operator v Excelu lze použít k nalezení protínajících se hodnot dvou seznamů/rozsahů. Jedná se o neobvyklý operátor, protože je reprezentován mezerou (ano, je to tak).
Pokud použijete mezerový znak mezi dvěma rozsahy, stane se operátorem Průnik v Excelu.
Intersect Operator v Excelu
Intersect Operator v Excelu můžete použít k nalezení:
- Průsečík jednoho řádku a sloupce.
- Křižovatka více řádků a sloupců.
- Křižovatka pojmenovaných rozsahů.
Průnik jednoho řádku a sloupce
Předpokládejme, že existuje soubor dat, jak je uvedeno níže:
Pokud nyní použijete = C2: C13 B5: D5 [Všimněte si, že mezi rozsahy je jedna mezera, což je také náš protínající operátor v aplikaci Excel], vrátí 523 (hodnota v buňce C5), což je průsečík těchto 2 rozsahů.
Průnik více řádků a sloupců
Stejnou technikou můžete také najít průsečík rozsahů, které pokrývají více než jeden řádek nebo sloupec. Například se stejnou sadou dat, jak je uvedeno výše, můžete v dubnu získat průnik produktu 1 a produktu 2.
Zde je vzorec, který to dokáže: = B2: C13 B5: D5
Všimněte si, že výsledek tohoto vzorce by zobrazil chybu Hodnota, ale když vyberete vzorec a stisknete F9, zobrazí se výsledek jako {649,523}. Tento vzorec vrací pole hodnot průniku. Můžete to použít ve vzorcích, například SUM (pro získání součtu hodnot průsečíků) nebo MAX (pro získání maxima hodnot průniku).
Křižovatka pojmenovaných rozsahů
Pojmenované rozsahy můžete také použít k nalezení křižovatky pomocí Operátoru protnutí v Excelu.
Zde je příklad, kde jsem hodnoty produktu 1 pojmenoval jako Prdt1, hodnoty produktu 2 jako Prdt2 a dubnové hodnoty jako duben.
Nyní můžete použít vzorec = Prdt1 Apr k získání průniku těchto 2 rozsahů. Podobně můžete použít = Prdt1: Prdt2 Apr k získání průniku produktu 1, produktu 2 a dubna.
Praktický příklad použití Intersect Operator v Excelu
Zde je situace, kdy by se tento trik mohl hodit. Mám sadu dat obchodního zástupce a prodeje, které v každém měsíci v roce 2012 uskutečnili.
Také jsem vytvořil rozevírací seznam s Obchodní zástupce Název v jedné buňce a Měsíc jméno v jiném, a chci extrahovat tržby, které Rep dělal v daném měsíci.
Něco, jak je uvedeno níže:
Jak vytvořit toto:
- Vyberte celou sadu dat (B3: N13) a stisknutím Control + Shift + F3 vytvořte pojmenované rozsahy (lze to také provést pomocí vzorce -> Definovaná jména -> Vytvořit z výběru). Otevře se dialogové okno „Vytvořit jména z výběru“.
- Vyberte možnosti „Horní řádek“ a „Levý sloupec“ a klikněte na OK.
- Tím se vytvoří pojmenované rozsahy pro všechny obchodní zástupce a celý měsíc.
- Nyní přejděte do buňky B16 a vytvořte rozevírací seznam pro všechny obchodní zástupce.
- Podobně přejděte do buňky C15 a vytvořte rozevírací seznam pro všechny měsíce.
- Nyní v buňce C16 použijte následující vzorec = NEPŘÍMÝ (B16) NEPŘÍMÝ (C15)
Jak to funguje?
Všimněte si, že mezi dvěma NEPŘÍMÝMI vzorci je mezera.
Funkce INDIRECT vrací rozsah pro pojmenované rozsahy - obchodní zástupce a měsíc a mezera mezi nimi funguje jako protínající se operátor a vrací protínající se hodnotu.
Poznámka: Tento neviditelný operátor průniku má přednost před ostatními operátory. Pokud tedy v tomto případě použijete = INDIRECT (B16) INDIRECT (C15)> 5000, vrátí hodnotu TRUE nebo FALSE na základě protínající se hodnoty.
