Ismeri azt a remek trükköt, amikor hivatkozást hoz létre egy oszlopfejlécre a sor utolsó nem üres cellájában? A legjobb: Nincs szüksége segédvonalakra vagy oszlopokra. Ilyen egyszerű:
Kombinálja a négy funkciót: IFERROR (), INDEX (), MAX () és IF ()
Az értékesítési ügyvezető igazgató elküldi Önnek a havonta a fokozatosan megszűnt termékekre vonatkozó szerződések listáját. Az N oszlopban található képletet kell használnia az egyes termékek értékesítési utolsó hónapjának megadásához - segédvonalak vagy oszlopok nélkül. Ha nem kötöttek több szerződést, írjon be egy üres cellát az N oszlopba.
Ez a példa, első látásra banális, kemény diónak bizonyul segédvonalak vagy oszlopok használata nélkül. mint Excel a gyakorlathoz-Az olvasók feltörik a diót! A cellában a következő tömbképlet található a problémamegoldáshoz N2 létrehozva:
{= HIBÁS (INDEX ($ B $ 1: $ M $ 1; MAX (HA (B2: M2 ""; OSZLOP (B2: M2) -1; -1))); "")}
Nézze meg lépésről lépésre, hogyan működik
Kezdje a tömbképletbe épített IF () feltétellel: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (HA (B2: M2 "", OSZLOP (B2: M2) -1, -1)));"")}
Az IF () feltétel fiktív segédvonalat hoz létre a tömb képletében, és ellenőrzi a cellákat B2 amíg M2hogy ezek üresek -e vagy sem. Ha egy cella üres, az -1 érték egyébként a COLUMN () függvényen keresztül kerül visszaadásra, a megfelelő oszlopszám mínusz az 1 érték.
Az 1 kivonása azért szükséges a képletben, mert a táblázat első oszlopa nem a hónap nevét, hanem a termék nevét tartalmazza. Az alábbiakban megtudhatja, hogyan kell használni az INDEX () függvényt a megfelelő hónap nevének megjelenítéséhez, amely - ha nem vonta le az 1 -et - a használt további oszlop miatt A. pont egy oszloppal lenne helytelen.
Ha a tartomány összes cellája B2: M2 üresek, a -1 érték (nincs termékértékesítés) hibát hoz létre, amellyel üres cellát ábrázolunk. Az aktív segédvonal a 3. sor ③ ábráján látható.
A következő lépésben olvassa ki a legnagyobb értéket a MAX () függvénnyel, amelyben az IF () feltétel beágyazódik. Ez a 12 -es érték a 3. sorban (13. oszlop mínusz 1; lásd a segédvonalat a Figure ábrán):
{= HIBÁS (INDEX ($ B $ 1: $ M $ 1;MAX (HA (B2: M2 "", OSZLOP (B2: M2) -1, -1)));"")}
Ezt a MAX értéket átadja az INDEX () függvénynek. Ezután a megfelelő naptári hónap olvasható az 1. sorban. Az INDEX () függvény adatterülete a terület $ B $ 1: $ M $ 1. Az átadott MAX érték - a 12. példában - azt jelenti, hogy a lista tizenkettedik értéke, azaz December december hónapra:
{= HIBÁS(INDEX ($ B $ 1: $ M $ 1; MAX (HA (B2: M2 ""; OSZLOP (B2: M2) -1; -1)));"")}
Ha a táblázat havi tartományában az összes cella üres, akkor a legnagyobb érték -1 (lásd az IF () feltételt az elején). Ha a -1 értéket átadja az INDEX () függvénynek, ez elkerülhetetlenül hibaértékhez vezet, mivel az INDEX () függvény listaterülete csak tizenkét bejegyzést tartalmaz, ezért nem találja a -1 bejegyzést. Ezt a hibaértéket az IFERROR () függvénnyel kapja meg, és üres karakterláncot ad vissza. A példában ez a helyzet a 4. sor C termékével, mivel ennél a terméknél nem lehetett több szerződést kötni:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (HA (B2: M2 "", OSZLOP (B2: M2) -1, -1));"")}
Mivel ez egy tömbképlet, fejezze be a képlet beírását a Ctrl + Shift + Enter billentyűkombinációval.
