Excel COUNTIFS, где условие специфично для строки

Question

Я пытаюсь выполнить функцию СЧЕТЕСЛИ для следующих данных, где критерий специфичен для строки и не связан с конкретным значением или ячейкой.

Например, в следующем примере я пытаюсь подсчитать строки, в которых столбец B в два раза больше столбца A.

А	Б
1	5
2	6
5	3

Я предполагаю, что правильный синтаксис — «=COUNTIF(B:B, «="&A:A *2), так что он проверяет, является ли столбец B в каждой строке вдвое больше столбца A, но я всегда получаю 0. Правильный результат — 2.

Похоже, что разрешены только отдельные символы # или ссылка на одну ячейку (например, =&A2), однако я бы хотел, чтобы условие проверки было конкретным для каждой строки.

Есть ли другая функция или какие-то синтаксические ошибки, которые не позволяют мне это сделать? Спасибо

Answer 1

Использовать SUMPRODUCT:

=SUMPRODUCT(--(A1:A4*2=B1:B4))

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

Answer 2

В вашем примере данных нет ни одного случая (строк), когда значение столбца B вдвое больше значения в A.

=SUM(IF(B1:B3 =A1:A3*2,1,0))поскольку формула 3D/массива сделает это за вас.

Формулу массива нужно вводить с помощью CTRL+ SHIFT+ ENTERв LibreOffice; я не совсем уверен, так ли это в Excel(и нет возможности попробовать).

Answer 3

Единственная проблема с оригинальной формулой в том, что ее нужно как-то суммировать. Один способ — как cybernetic.nomadэто сделать. Другой — просто заключить формулу автора в SUM()функцию.

Существуют и другие способы получения списка из единиц и нулей для определения того, соответствуют ли значения столбца B условию. Hannuпоказан один из них, который, честно говоря, был бы моим «путеводителем», поскольку я никогда не находил особого применения для COUNTсемейства функций. (Я знаю об этом ограничении, поскольку последние восемь лет или около того просмотра сайтов справки Excel показали мне множество случаев «не по назначению», а также то, что они являются более прямыми способами получения результатов, IF()к которым я всегда стремился.)

Однако есть и другие способы сделать это, один из которых — простая проверка ИСТИНА/ЛОЖЬ для каждого значения, которое не используется IF()в работе, поэтому, вероятно, будет быстрее на большом наборе данных:

=SUM(  (B1:B4 = A1:A4*2)  *1)

Это простое сравнение, генерирующее массив результатов ИСТИНА/ЛОЖЬ. Затем можно использовать операторы --, как cybernetic.nomadэто делает, или *1как я делаю здесь. Это преобразует литеральные элементы массива ИСТИНА/ЛОЖЬ в элементы массива 1/0, что позволяет SUM()затем складывать их. (Я предпочитаю , *1поскольку легко не добавлять в конце выражения, если выражение содержит собственное умножение, поскольку этого умножения (или сложения, если ORвместо AND) достаточно для преобразования. Конечно, любой из них работает хорошо, и для многих --кажется более понятным и поэтому ИМ было бы легче поддерживать его, чем *1. Мы, люди, такие, и для каждого — лучшее для него.)

Хотя семейство COUNTпроизводит 1/0 напрямую, так что это хорошее значение. Однако, что-то все еще должно суммировать вещи, когда применяешь это таким образом в диапазоне.