SUMIFS с использованием" больше или равно " с текстовыми значениями

используя следующий упрощенный пример, я хочу вычислить сумму, где категория= "градация" и дата >= 1986-05-01.

Category    Date      Count
Graduation  1985-06-15  1
Graduation  1985-09-12  2
Graduation  1986-05-21  3
Graduation  1986-06-06  4
Graduation  1986-07-03  7
Transfer    1986-08-14  3
Graduation  1986-08-20  1

даты все текст, т. е. не даты Excel. Находясь в формате гггг-ММ-ДД, даты следуют правильному порядку.

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

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"='1986-05-21'")     0   Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"=1986-05-21")       3   Correct
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"1986-05-21")        3   Correct

таким образом, для равенства вы не ставите кавычки вокруг значения. Это штраф.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">1986-05-21")       0   Incorrect
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">'1986-05-21'")     12  Correct

так больше, вы do нужно поставить кавычки вокруг значения.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">='1986-05-21'")    12  Incorrect: should be 15

кажется >= считается, что он был >.

=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<'1986-05-21'")     6   Incorrect: should be 3
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,"<='1986-05-21'")        6   Correct

вроде как < считается, что он был дело=).

Итак, вы можете помочь мне настроить синтаксис так, чтобы SUMIFS получал greater than и less than правильно с текстовыми значениями?

16
задан MattClarke
14.12.2022 1:26 Количество просмотров материала 2697
Распечатать страницу

1 ответ

семейство функций COUNTIF(S)/SUMIF(S) обычно пытается интерпретировать ваши данные как числовые, где это возможно, хотя здесь, очевидно, есть некоторая путаница, поскольку кажется, что она не может последовательно интерпретировать как ваши значения диапазона, так и ваш критерий как один и тот же формат.

в этом случае:

=СУММЕСЛИМН(С2:С8,А2:А8,"выпускной",В2:В8,">1986-05-21")

Excel распознает ваши записи в B2: B8, хотя и в формате текста, как потенциальную дату (т. е. числовые) значения.

однако, в то же время, он (неконструктивно) интерпретирует ваш критерий - "1986-05-21" - как текстовое значение, и поэтому, поскольку Excel не учитывает какие-либо числовые (это именно то, что даты в Excel - 21/05/1986 составляет 31553), чтобы быть "больше", чем любое текстовое значение (например, введите =1000000>"1" в ячейке, где-то - ответ-Ложь), ответа ноль.

когда вы добавляете апострофы:

=СУММЕСЛИМН(С2:С8,А2:А8,"выпускной",В2:В8,">'1986-05-21'")

Excel интерпретирует как критерий, так и записи в B2: B8 как текст, а также прямое сравнение текста с текстом.

причина, по которой вы получаете 12, а не 15, для:

=СУММЕСЛИМН(С2:С8,А2:А8,"выпускной",В2:В8,">='1986-05-21'")

является то, что запись в B4 является 1986-05-21 (без апострофа: один может появиться в строке формул, хотя это не технически часть строки, просто способ Excel, указывающий, что значение ячейки является текстом), и критерий "1986-05-21" (с "подлинными" апострофами). И вы можете легко проверить в клетке где-нибудь, что:

="1986-05-21">="'1986-05-21'"

рассматривается Excel как FALSE (я предполагаю, что Excel интерпретирует ведущий Апостроф в последнем как означающий, что он "больше", чем первый - я не знаю всех тонкостей этих интерпретаций: странно, = "/ a " > " a" возвращает FALSE, тогда как = " 'a" > " A " возвращает TRUE).

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

=функция sumproduct((А2:А8="выпускной")*(В2:В8>="1986-05-21")*С2:С8)

в отношении

2
отвечен XOR LX 2022-12-15 09:14

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Мы используем cookie. Это позволяет нам анализировать взаимодействие посетителей с сайтом и делать его лучше. Продолжая пользоваться сайтом, вы соглашаетесь с использованием файлов cookie. Политика конфиденциальности
Согласен
Вверх