Суммируем текст. СУММ текста  как число с функциями Ч, ИНДЕКС , ЕСЛИ и ПОИСКПОЗ

Перевод текстовых значений в числа и суммировать результат, вы можете использовать СУММ ИНДЕКС Ч ЕСЛИ ПОИСКПОЗ. В показанном примере, формула в K8-это:

{=СУММ(ИНДЕКС($O$8:$O$11;Ч(ЕСЛИ(1;ПОИСКПОЗ(F8:J8;$N$8:$N$11;0)))))}

  1. «Оценка» это диапазон $N$8:$N$11

      2.  «Балл» —  диапазон $O$8:$O$11.

      3. Диапазон для поиска и суммирования F8:J8

Примечание:

это формула массива, и должно вводиться с помощью

Control + Shift + ввод.

Как эта формула работает

В основе этой формулы используется функции ИНДЕКС и ПОИСКПОЗ , для преобразования текстовых значений в числа из таблицы подстановки. Например, перевести «Зачет» на соответствующий Балл, мы будем использовать:

=Индекс(Балл;ПОИСКПОЗ(«Зачет»;Оценка;0))

В ячейке : М12

=ИНДЕКС($O$8:$O$11;ПОИСКПОЗ(«Зачет»;$N$8:$N$11;0))

Формула возвращает 5.

Однако есть сложность так как мы хотим преобразовывать и суммировать ряд текстовых значений а не одно «Зачет» в Столбце N в Баллы столбца O. Нам нужен индекс, чтобы вернуть более одного результата. Формула такая:

=СУММ($O$8:$O$11;ПОИСКПОЗ(F8:J8;$N$8:$N$11;0))

После ПОИСКПОЗ, у нас есть массив из 5 элементов:

Однако, если вы попробуете это, то функция ИНДЕКС вернет не тот результат суммы который мы хотели бы увидеть. Для получения нормального результата необходимо использовать ПОИСКПОЗ  в функции Ч и с функцией  ЕСЛИ :

Ч(ЕСЛИ(1,ПОИСКПОЗ(F8:J8;$N$8:$N$11;0)))

Файл Суммируем текст. СУММ текста  как число с функциями Ч, ИНДЕКС , ЕСЛИ и ПОИСКПОЗ DEVSAP

Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС )

Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС )

=ИНДЕКС(данные ПОИСКПОЗ(критерий 1;столбец;1);ПОИСКПОЗ(критерий 2;строк;1))

Связанные формулы

Двусторонний поиск с ВПР

Для поиска значения в таблице по строкам и столбцам, можно построить формулу, что делает двусторонний поиск с индекс и поискпоз.

В показанном примере, формула k6 является:

=ИНДЕКС($D$6:$H$10;ПОИСКПОЗ($K$4;$D$5:$H$5);ПОИСКПОЗ($K$5;$C$6:$C$10))

Заметим, что эта формула делает и «приблизительное соответствие», поэтому значения строки и столбца должны быть отсортированы.

Как эта формула работает

Ядром этой формулы является индекс, который является просто получение значения от $D$6:$H$10 («данные») на основе номера строки и номера столбца.

=ИНДЕКС($D$6:$H$10;столбец;строка)

Чтобы получить номера строк и столбцов, мы используем ПОИСКПОЗ, настроенные на приблизительное соответствие, установив 3 аргумент 1 (истина):

ПОИСКПОЗ($K$5;$C$6:$C$10) // получаем количество строк
ПОИСКПОЗ($K$4;$D$5:$H$5) // получаем номер столбца

В примере, ПОИСКПОЗ вернет 60, Когда Столбц С = «3», а, строка d «D» = «4» .

В итоге, формула сводится к:

=Индекс($D$6:$H$10, 3, 4) = 60

 

 

Файл Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС ) DEVSAP