Sledujte video - Používanie operátora Intersect v Exceli
Operátor kríženia v Exceli možno použiť na nájdenie prienikových hodnôt dvoch zoznamov/rozsahov. Toto je neobvyklý operátor, pretože je reprezentovaný medzerníkom (áno, je to tak).
Ak použijete medzeru medzi dvoma rozsahmi, stane sa operátorom Intersect v Exceli.
Operátor kríženia v Exceli
Intersect Operator v Exceli môžete použiť na nájdenie:
- Priesečník jedného riadka a stĺpca.
- Priesečník viacerých riadkov a stĺpcov.
- Križovatka pomenovaných rozsahov.
Prienik jedného riadka a stĺpca
Predpokladajme, že existuje súbor údajov, ako je uvedené nižšie:
Teraz, ak použijete = C2: C13 B5: D5 [Všimnite si, že medzi rozsahmi je jedna medzera, ktorá je tiež naším priesečníkom v programe Excel], vráti 523 (hodnota v bunke C5), ktorá je priesečníkom týchto dvoch rozsahov.
Prienik viacerých riadkov a stĺpcov
Rovnakú techniku môžete použiť aj na nájdenie priesečníka rozsahov, ktoré pokrývajú viac ako jeden riadok alebo stĺpec. Napríklad s rovnakou množinou údajov, ako je uvedené vyššie, môžete v apríli získať priesečník produktu 1 a produktu 2.
Tu je vzorec, ktorý to dokáže: = B2: C13 B5: D5
Výsledok tohto vzorca by zobrazil chybu Hodnota, keď však vyberiete vzorec a stlačíte kláves F9, výsledok sa zobrazí ako {649,523}. Tento vzorec vráti pole hodnôt priesečníkov. Môžete to použiť vo vzorcoch, ako napríklad SUM (na získanie súčtu hodnôt priesečníkov) alebo MAX (na získanie maxima hodnôt priesečníkov).
Priesečník pomenovaných rozsahov
Pomenované rozsahy môžete použiť aj na nájdenie križovatky pomocou Operátora priesečníka v Exceli.
Tu je príklad, kde som hodnoty produktu 1 pomenoval ako Prdt1, hodnoty produktu 2 ako Prdt2 a aprílové hodnoty ako apríl.
Teraz môžete použiť vzorec = Prdt1 Apr na získanie priesečníka týchto 2 rozsahov. Podobne môžete použiť = Prdt1: Prdt2 Apr na získanie priesečníka produktu 1, produktu 2 a apríla.
Praktický príklad použitia operátora Intersect v Exceli
Tu je situácia, kedy by vám tento trik mohol prísť vhod. Mám množinu údajov obchodného zástupcu a tržby, ktoré každý mesiac realizoval v roku 2012.
Tiež som vytvoril rozbaľovací zoznam s Obchodný zástupca Meno v jednej bunke a Mesiac meno v inom, a chcem extrahovať tržby, ktoré zástupca vykonal v ten mesiac.
Niečo, ako je uvedené nižšie:
Ako vytvoriť toto:
- Vyberte celý súbor údajov (B3: N13) a stlačením klávesov Control + Shift + F3 vytvorte pomenované rozsahy (dá sa to vykonať aj pomocou vzorca -> Definované názvy -> Vytvoriť z výberu). Otvorí sa dialógové okno „Vytvoriť mená z výberu“.
- Vyberte možnosti „Horný riadok“ a „Ľavý stĺpec“ a kliknite na tlačidlo OK.
- To vytvorí pomenované rozsahy pre všetkých predajcov a celý mesiac.
- Teraz prejdite na bunku B16 a vytvorte rozbaľovací zoznam pre všetkých predajcov.
- Podobne prejdite na bunku C15 a vytvorte rozbaľovací zoznam pre všetky mesiace.
- Teraz v bunke C16 použite nasledujúci vzorec = NEPRIAMY (B16) NEPRIAMY (C15)
Ako to funguje?
Všimnite si, že medzi dvoma NEPRIAMYMI vzorcami je medzera.
Funkcia INDIRECT vracia rozsah pre pomenované rozsahy - obchodný zástupca a mesiac a medzera medzi nimi funguje ako operátor priesečníka a vracia krížiacu sa hodnotu.
Poznámka: Tento neviditeľný operátor priesečníka má prednosť pred ostatnými operátormi. Ak teda v tomto prípade použijete = INDIRECT (B16) INDIRECT (C15)> 5000, vráti hodnotu TRUE alebo FALSE na základe priesečníka.
