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


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

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

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

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

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

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

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

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


2
2017-09-09 17:58


Источник


Кстати, я стараюсь избегать VBA, если это вообще возможно - daniellopez46
Кажется, это работает для меня, и я нахожусь в Excel 2007. Единственная проблема с текущей формулой заключается в том, что вы рассчитываете только в ячейке A27. Кроме того, обязательно используйте Ctrl + Shift + Enter, поскольку формула является функцией массива (или используйте SUMPRODUCT вместо SUM). Именованный символ может содержать звездочку и будет считаться подстановочным знаком. - Jerry
будет то, что я пытаюсь сделать для каждой ячейки (таким образом, ссылка на одну ячейку вместо диапазона) подсчитывает количество экземпляров, например, ячейка A1 может иметь «LBNL» или «LBL» или и то, и другое. В случае первых двух формула должна возвращать 1 и в случае второй 2. Для меня она работает только для первого элемента в диапазоне имен. Поэтому он будет считать ячейку с единственным текстом «LBNL», но если у нее есть «LBNL LBL», она вернет 0 вместо 2. Вы уверены, что она работает на вас? Он возвращает 2 для вас в последнем примере? - daniellopez46
BTW Я попробовал CSE и использовал sumproduct и использовал диапазон, но все еще не работал: {= SUMPRODUCT (COUNTIF (A27: A27, search_items1))} - daniellopez46
Хорошо, то, что вы сейчас описываете, не то, что я имел в виду после прочтения вашего вопроса. Я думал, что в колонке много комментариев, и вы хотите подсчитать количество ячеек, имеющих LBNL (и ничего больше в одной и той же ячейке), имея Lawrence Berkeley, и т. д. и для 4-го текста, любая ячейка, начинающаяся с Lawrence Lab, То, что вы сейчас описываете, меняет все, так как вы сейчас должен поместите подстановочные знаки, чтобы получить ожидаемый результат. Поместите звездочки до и после текста, который нужно пересчитать. И вам не нужно использовать CSE с SUMPRODUCT, поэтому я предложил SUM которые вам понадобятся. - Jerry


Ответы:


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

=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+сдвиг+Войти:

=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))

enter image description here

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


5
2017-09-09 18:50



Вы знаете, что мне было интересно об этом последнем сценарии, но даже не подумал, чтобы я ударил и попросил. Еще раз спасибо. Отличная работа! - daniellopez46
@ daniellopez46 Я искал способ получить все счета в последней части в одной ячейке, так что до сих пор мне не повезло ^^; Наверное, тебе пока придется гоняться со мной. Если я найду одну альтернативу раньше, чем кто-то другой, я дам вам знать! - Jerry


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

Вы можете определить именованный массив через менеджер имен. Вызовите менеджера имен из ленты формул, создайте новое имя. Дайте ему четкое имя (например, «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))

1
2018-03-02 08:44