Есть ли способ использовать диапазон имен в качестве критерия массива для формулы суммы (countif ()) в Excel 2010

Question

Столбец с картинками Полный список комментариев, например, ответ на вопрос анкеты. Теперь представьте столбец B с формулой, которая подсчитывает вхождения конкретных ключевых слов в каждом комментарии. В настоящее время я использую эту формулу в столбце B: SUM(COUNTIF(A27, {" LBNL ", " Lawrence Berkeley ", " LBL ", "Lawrence Lab *"}))

Поскольку список будет расти и сокращаться по пути, и потому что я собираюсь в конечном итоге получить несколько похожих формул (для разных категорий), я бы хотел вместо этого управлять списком в именованном диапазоне и ссылаться на него оттуда.

Итак, теперь давайте предположим, что мой список похож на ниже и имеет диапазон имен search_items1

• LBNL
• Лоуренс Беркли
• LBL
• Лаборатория Лоуренса

Моя формула будет выглядеть как SUM(COUNTIF(A27, search_items1)).

Обратите внимание на использование * для подстановочного знака, который представляет другую проблему, но я не могу заставить работать приведенную выше формулу даже без *. Есть ли способ сделать эту работу? Решение с подстановочным знаком * было бы идеальным.

В качестве альтернативы я мог бы сослаться на одну ячейку, которая объединена вместе из диапазона имен и будет выглядеть следующим образом: {" LBNL ", " Lawrence Berkeley ", " LBL ", "Lawrence Lab *"}. Я пытался это сделать, но формула интерпретирует это как один текстовый блок.

Я пробовал несколько вариантов синтаксиса и бесчисленное количество поисков в Google и Super User. Пожалуйста помоги.

Answer 1

Что делает ваша формула:

=SUM(COUNTIF(A27,{"LBNL","Lawrence Berkeley","LBL","Lawrence Lab*"}))

считается счетом 1, если ячейка A27 является LBNL , или Lawrence Berkeley или LBL или Lawrence Lab* (* действует как подстановочный знак).

Итак, если у вас есть ячейка LBNL , вы получите 1 в результате. Наличие Dr. LBNL приведет к 0.

Если вы хотите получить счетчик 1 если ячейка содержит хотя бы 1 LBNL , вам нужно использовать подстановочные знаки с обеих сторон текста, а именно:

=SUM(COUNTIF(A27,{"*LBNL*","*Lawrence Berkeley*","*LBL*","*Lawrence Lab*"}))

Вы можете создать именованный диапазон и поместить его туда, но это станет формулой массива, которая будет работать только с Ctrl+Shift+Enter:

=SUM(COUNTIF(A27,search_items1))

Теперь вы можете использовать SUMPRODUCT чтобы избежать использования CSE для использования этой формулы:

=SUMPRODUCT(COUNTIF(A27,search_items1))

Однако, если у вас есть ячейка, содержащая LBNL LBNL и вы хотите, чтобы результат был равен 2 , это еще один вопрос, потому что COUNTIF завершается, когда он находит то, что ищет в ячейке, и возвращает 1 .

В этом случае я бы посоветовал подсчитать каждую ячейку для проверки (так как вышеуказанная функция может использоваться для просмотра целых столбцов):

=SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,search_items1,"")))/LEN(search_items1))

[Заметьте теперь, что вам нужно отбросить звездочки из-за того, как работает SUBSTITUTE .]

Answer 2

Это не всегда должна быть формула. Это тоже может быть диапазон. Это позволяет содержать электронную таблицу в чистоте.

Вы можете определить именованный массив через менеджер имен. Позвоните менеджеру имен с ленты формул, создайте новое имя. Дайте ему четкое имя (например, "BinWithMarks"), а затем вставьте его в поле «Относится к:»:

={100,89,84,79,74,69,64,59,54,49,39,0}

Аналогичным образом, вы можете добавить соответствующие оценки следующим образом, используя оценки в качестве названия:

={"A+","A","A-","B+","B","B-","C+","C","C-","D","E"}

После подтверждения вашего выбора вы можете использовать этот массив, чтобы найти соответствующую оценку для студента, используя комбинацию индексов и формул соответствия.

=INDEX(Grades,MATCH(N11,BinWithMarks,-1))