Изтеглете примерната работна книга
Този урок ще покаже как да намерите сумата от текстови стойности, където на всяка такава текстова стойност е присвоен уникален код в Excel и Google Sheets.
SUM числата се съхраняват като текст
Първо ще разгледаме как да сумираме числата, съхранявани или форматирани като текст.
В следния пример колоната Заплата се съхранява като текст. Ако се опитате да сумирате стойностите, Excel ще покаже нула.
| 1 | = SUM (C3: C7) |
Вместо това, за да извършите операцията SUM върху числа, съхранявани като текст, можете да използвате формула на масив с функциите SUM и VALUE по следния начин:
| 1 | = СУММА (СТОЙНОСТ (C3: C7)) |
Функцията VALUE преобразува текст, представляващ число, в число. Функцията SUM сумира тези числа.
В Excel 365 и версия на Excel, по -нова от 2022 г., можете просто да въведете формулата като нормална. Когато обаче използвате Excel 2022 и по -рано, трябва да въведете формулата на масива, като натиснете CTRL + SHIFT + ENTER (вместо ENTER), казвайки на Excel, че формулата е формула на масив. Ще знаете, че това е формула на масив по къдравите скоби, които се появяват около формулата (вижте горното изображение). В по -късните версии на Excel и Excel 365 можете просто да натиснете ENTER вместо.
Нека разгледаме следното обяснение, за да разберем по -добре формулата.
Функция VALUE
Използвана като формула на масив, функцията VALUE преобразува целия диапазон от числа, съхранявани като текст, в масив от числа и я връща като вход за функцията SUM.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
За да видите какво връща функцията VALUE, изберете необходимата функция и натиснете F9.
SUM на текстовите стойности
За да се сумира диапазон от текстови стойности, където на всяка такава текстова стойност е присвоен уникален код, може да се използва формула за масив.
Следващата таблица записва какво хората избират за три зададени въпроса. Таблицата вдясно изброява четирите възможни отговора, на всеки от които е присвоена кодова стойност. Трябва да сумираме кодовете за всеки човек.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Ще преминем през формулата по -долу.
MATCH функция
Функцията MATCH търси определен елемент в диапазон и връща относителното му положение в този диапазон. Неговият синтаксис е:
Като формула на масив в този пример, нека да видим какво връща функцията MATCH.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
За дадено лице функцията MATCH намира относителната позиция на всеки отговор в диапазона H3: H6. Резултатът е масив от позиции.
Забележка: Във формула за масив, за да видите какво връща функция, изберете необходимата функция и натиснете F9.
IF & N функция
Използваните заедно IF и N функцията връщат следния масив като вход за функцията INDEX.
| 1 | = SUM (INDEX (I $ 3: I $ 6, {1,3,2})) |
Тук двете функции връщат масив от относителна позиция на отговорите в диапазона H3: H6. Целта на използването на IF и N функциите е да се извърши процес, наречен разграничаване. С прости думи, двете функции принуждават функцията INDEX да предаде целия масив от кодови стойности на функцията SUM.
Обясняваме това в следващия раздел.
INDEX функция
Функцията INDEX връща стойността, позиционирана в пресечната точка на определен ред и колона в диапазон. Неговият синтаксис е:
Нека да видим как работи като формула на масив:
| 1 | = SUM ({10,5,8}) |
Функцията INDEX намира кодовите стойности в диапазона I3: I6 според дадените номера на позициите. След това връща масив от стойности, т.е. съответния код за всеки отговор, на функцията SUM за извършване на операции.
Уверете се, че броят на редовете и колоните в двете Отговор и Код колоната е същата.
SUM функция
Функцията SUM ще сумира стойностите на кода, върнати от функцията INDEX.
| 1 | = SUM ({10,5,8}) |
Всичко това заедно дава нашата първоначална формула:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
Сума на текстовите стойности - без IF & N функции
Този раздел обяснява как Excel реагира, ако не използваме функцията IF и N в гореспоменатата формула.
Същият пример се използва със същите кодове и отговори.
| 1 | {= SUM (INDEX (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Както можете да видите, функцията INDEX предава само кода за първия отговор на функцията SUM. Ако разглеждате функцията INDEX с натискане F9 ще получите следното:
Стойността! Грешка се връща, защото функцията INDEX не може да прочете масива от номера на редове като масив. Следователно, използването на функцията IF & N върши работа.
Забележка: В Excel 365 можете да пропуснете използването на функциите IF и N като цяло.
Обобщен текст- Google Таблици
Тези формули работят същите в Google Sheets, както и в Excel.
