Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan lehet megtalálni a szöveges értékek összegét, ahol az Excel és a Google Táblázatok minden egyes ilyen szövegértékhez egyedi kódot rendel.
Szövegként tárolt SUM számok
Először megvizsgáljuk, hogyan lehet összegezni a szövegként tárolt vagy formázott számokat.
A következő példában a Fizetés oszlop szövegként kerül tárolásra. Ha megpróbálja összegezni az értékeket, az Excel nullát jelenít meg.
| 1 | = SUM (C3: C7) |
Ehelyett a SUM művelet végrehajtásához szövegesen tárolt számokon használhat tömbképletet a következő SUM és VALUE függvényekkel:
| 1 | = ÖSSZES (ÉRTÉK (C3: C7)) |
A VALUE függvény a számot ábrázoló szöveget számmá alakítja. A SUM függvény összegezi ezeket a számokat.
Az Excel 365 -ben és az Excel 2022 -nél újabb verziójában egyszerűen beírhatja a képletet, mint általában. Az Excel 2022 és korábbi verziók használatakor azonban a gomb megnyomásával kell megadni a tömb képletét CTRL + SHIFT + ENTER (ahelyett BELÉP), és azt mondja az Excelnek, hogy a képlet tömbképlet. A képlet körül megjelenő göndör zárójelek alapján tudni fogja, hogy ez egy tömbképlet (lásd a felső képet). Az Excel és az Excel 365 későbbi verzióiban egyszerűen nyomja meg a gombot BELÉP helyette.
Nézzük meg a következő magyarázatot, hogy jobban megértsük a képletet.
VALUE függvény
Tömbképletként használva a VALUE függvény a szövegként tárolt számok teljes tartományát számok tömbjévé alakítja, és a SUM függvény bemeneteként adja vissza.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
Ha meg szeretné tekinteni, mit ad vissza az ÉRTÉK függvény, válassza ki a kívánt funkciót, és nyomja meg a gombot F9.
A szövegértékek összege
Egy tömbképlet használható olyan szöveges értékek tartományának összegzésére, ahol minden ilyen szövegértékhez egyedi kód van hozzárendelve.
Az alábbi táblázat rögzíti, hogy az emberek mit választanak három adott kérdésre. A jobb oldali táblázat felsorolja a négy lehetséges választ, amelyek mindegyikéhez kódérték tartozik. Össze kell foglalnunk a kódokat minden egyes személy számára.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))))) |
Az alábbi képletet fogjuk végigjárni.
MATCH funkció
A MATCH függvény egy adott elemet keres egy tartományban, és visszaadja relatív pozícióját ebben a tartományban. Szintaxisa a következő:
Mivel ebben a példában tömbképletről van szó, nézzük meg, mit ad vissza a MATCH függvény.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))))) |
Egy adott személy esetében a MATCH függvény megkeresi a tartomány minden válaszának relatív helyzetét H3: H6. Az eredmény egy pozíciók tömbje.
Jegyzet: Egy tömbképletben, ha meg szeretné tekinteni, hogy egy függvény mit ad vissza, válassza ki a kívánt függvényt, és nyomja meg a gombot F9.
IF & N funkció
Az IF és az N függvény együtt használva a következő tömböt adja vissza az INDEX függvény bemeneteként.
| 1 | = SUM (INDEX (I $ 3: I $ 6, {1,3,2})) |
Itt a két függvény a válaszok relatív helyzetének tömbjét adja vissza a tartományban H3: H6. Az IF és az N függvények használatának célja a dereferencia nevű folyamat végrehajtása. Egyszerűen fogalmazva, a két függvény arra kényszeríti az INDEX függvényt, hogy a kódértékek teljes tömbjét átadja a SUM függvénynek.
Ezt a következő részben ismertetjük.
INDEX függvény
Az INDEX függvény a tartomány egy adott sorának és oszlopának metszéspontjában elhelyezett értéket adja vissza. Szintaxisa a következő:
Nézzük meg, hogyan működik tömbképletként:
| 1 | = ÖSSZES ({10,5,8}) |
Az INDEX függvény megkeresi a tartomány értékeit I3: I6 a megadott pozíciószámok szerint. Ezután visszaad egy érték tömböt, azaz minden válaszhoz tartozó kódot a SUM függvénynek a műveletek végrehajtásához.
Győződjön meg arról, hogy a sorok és az oszlopok száma a Válasz és a Kód oszlop ugyanaz.
SUM funkció
A SUM függvény összegzi az INDEX függvény által visszaadott kódértékeket.
| 1 | = ÖSSZES ({10,5,8}) |
Mindezek együtt adják a kezdeti képletünket:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))}) |
Szövegértékek összege - IF & N függvények nélkül
Ez a rész elmagyarázza, hogyan reagál az Excel, ha nem használjuk az IF és az N függvényt a fent említett képletben.
Ugyanezt a példát használják ugyanazokkal a kódokkal és válaszokkal.
| 1 | {= SUM (MUTATÓ (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))}) |
Amint láthatja, az INDEX függvény csak a SUM függvény első válaszának kódját adja át. Ha az INDEX funkciót a gombbal vizsgálja F9 a következőt kapod:
Az érték! A hiba visszaadásra kerül, mert az INDEX függvény nem tudja tömbként olvasni a sorszámok tömbjét. Ezért az IF és az N függvény használata megteszi a trükköt.
Jegyzet: Az Excel 365 -ben kihagyhatja az IF és az N függvények használatát.
Összes szöveg- Google Táblázatok
Ezek a képletek ugyanúgy működnek a Google Táblázatokban, mint az Excelben.
