Добрый день, коллеги! Известно, что в функции СЧЁТЕСЛИ есть два компонента: диапазон и критерий. Я знаю, что стандартный вариант формулы СЧЁТЕСЛИ для всего Листа1 в качестве диапазона, и значения Х в качестве критерия выглядит так:
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")
Скажите, пожалуйста, как добавить ещё один критерий, который должен по сути быть маленьким диапазоном. Т.е., нужен "диапазон в диапазоне". Например, необходимо, чтобы на Листе1 формула посчитала количество ячеек со значением Х в диапазоне строк, содержащих даты от даты Н до М? Я думал о том, что это можно реализовать тремя вариантами: "простой вариант": добавить в формулу ещё один критерий, содержащий конкретный диапазон дат; либо "сложный вариант": сделать формулу, которая присваивала бы этим датам буквенный код и добавить в качестве критерия этот код (тогда необходимо делить итоговую сумму на 2); либо третий неэффективный "ручной" вариант - прописать множество критериев с отдельными значениями дат, которые будут учитываться формулой. Но для этого потребуется уже СЧЁТЕСЛИМН, если я правильно понимаю. Долго пытался задать в качестве дополнительного критерия диапазон, используя хоть какой-нибудь вариант, при этом попытки не увенчались успехом. Надежда всё-таки есть на то, что, используя эту формулу, можно реализовать эту идею. Скажите, пожалуйста, как вы видите необходимую формулу? Благодарю!
Добрый день, коллеги! Известно, что в функции СЧЁТЕСЛИ есть два компонента: диапазон и критерий. Я знаю, что стандартный вариант формулы СЧЁТЕСЛИ для всего Листа1 в качестве диапазона, и значения Х в качестве критерия выглядит так:
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")
Скажите, пожалуйста, как добавить ещё один критерий, который должен по сути быть маленьким диапазоном. Т.е., нужен "диапазон в диапазоне". Например, необходимо, чтобы на Листе1 формула посчитала количество ячеек со значением Х в диапазоне строк, содержащих даты от даты Н до М? Я думал о том, что это можно реализовать тремя вариантами: "простой вариант": добавить в формулу ещё один критерий, содержащий конкретный диапазон дат; либо "сложный вариант": сделать формулу, которая присваивала бы этим датам буквенный код и добавить в качестве критерия этот код (тогда необходимо делить итоговую сумму на 2); либо третий неэффективный "ручной" вариант - прописать множество критериев с отдельными значениями дат, которые будут учитываться формулой. Но для этого потребуется уже СЧЁТЕСЛИМН, если я правильно понимаю. Долго пытался задать в качестве дополнительного критерия диапазон, используя хоть какой-нибудь вариант, при этом попытки не увенчались успехом. Надежда всё-таки есть на то, что, используя эту формулу, можно реализовать эту идею. Скажите, пожалуйста, как вы видите необходимую формулу? Благодарю!Iment
Сообщение отредактировал Pelena - Среда, 14.10.2020, 16:43
Светлый, благодарю за отклик и за указание точного направление поиска! Буду пытаться применить функцию СЧЁТЕСЛИМН. Я смоделировал идентичный по логике файл, спасибо за поддержку!
Светлый, благодарю за отклик и за указание точного направление поиска! Буду пытаться применить функцию СЧЁТЕСЛИМН. Я смоделировал идентичный по логике файл, спасибо за поддержку!Iment
Nic70y, здорово, вы открыли для меня понимание функции СТОЛБЕЦ и её возможностей, помогли реализовать идею! Спасибо за готовый вариант формулы, очень наглядно.
Правка: коллеги, вопрос решён, ещё раз благодарю вас за помощь!
Nic70y, здорово, вы открыли для меня понимание функции СТОЛБЕЦ и её возможностей, помогли реализовать идею! Спасибо за готовый вариант формулы, очень наглядно.
Правка: коллеги, вопрос решён, ещё раз благодарю вас за помощь!Iment
Сообщение отредактировал Iment - Среда, 14.10.2020, 13:54
Nic70y, уточните, пожалуйста, я правильно понимаю, что функция СЧЁТЕСЛИМН не позволяет выбрать в качестве диапазона весть лист (как это реализовано в функции
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")
, а только лишь один столбец? Допустим, данные разбросаны по всему листу в разных столбцах. Я смоделировал второй образец, прошу посмотреть. Благодарю!
Nic70y, уточните, пожалуйста, я правильно понимаю, что функция СЧЁТЕСЛИМН не позволяет выбрать в качестве диапазона весть лист (как это реализовано в функции
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")
, а только лишь один столбец? Допустим, данные разбросаны по всему листу в разных столбцах. Я смоделировал второй образец, прошу посмотреть. Благодарю!Iment
Nic70y, ещё раз благодарю вас за конкретную формулу, я смог создать книгу, которую хотел изначально. Идея работает, при этом, хотелось бы, чтобы сам файл был бы легко изменяем при возникновении такой необходимости, и мне просто интересно, почему вариант, который я хотел бы в идеале реализовать, не работает. Допустим, на листе1 у нас есть "значение Х" в разных столбцах. Нам нужно посчитать общее количество ячеек, содержащих это "значение Х". С этой задачей вполне справится функция "СЧЁТЕСЛИ", где в качестве диапазона мы укажем все ячейки листа
Код
Лист1!1:1048576
, а в качестве критерия - само Значение Х. Если же мы захотим добавить ещё одно условие, например диапазон дат, для этого мы используем функцию СЧЁТЕСЛИМН по уже описанному варианту. Я прописываю нижнюю и верхнюю границы дат (диапазоны и условия 2 и 3, соответственно):
Код
Лист1!$A:$A
Код
>=1/"&СТОЛБЕЦ(I1)&"/2020
Код
Лист1!$A:$A
Код
<1/"&СТОЛБЕЦ(J1)&"/2020
Здесь я сталкиваюсь с трудностью: в качестве диапазона_условия_1 я хочу, чтобы был весь Лист1:
Код
Лист1!1:1048576
и программа выдаёт ошибку в значении. Поэтапное вычисление формулы показывает, что успешно преобразуется функция СТОЛБЕЦ (диапазоны дат), ошибка возникает на этапе работы с диапазоном. Не могу понять, где конкретно скрыта ошибка. Спасибо за поддержку!
P.S. прикрепляю корректный пример:
Nic70y, ещё раз благодарю вас за конкретную формулу, я смог создать книгу, которую хотел изначально. Идея работает, при этом, хотелось бы, чтобы сам файл был бы легко изменяем при возникновении такой необходимости, и мне просто интересно, почему вариант, который я хотел бы в идеале реализовать, не работает. Допустим, на листе1 у нас есть "значение Х" в разных столбцах. Нам нужно посчитать общее количество ячеек, содержащих это "значение Х". С этой задачей вполне справится функция "СЧЁТЕСЛИ", где в качестве диапазона мы укажем все ячейки листа
Код
Лист1!1:1048576
, а в качестве критерия - само Значение Х. Если же мы захотим добавить ещё одно условие, например диапазон дат, для этого мы используем функцию СЧЁТЕСЛИМН по уже описанному варианту. Я прописываю нижнюю и верхнюю границы дат (диапазоны и условия 2 и 3, соответственно):
Код
Лист1!$A:$A
Код
>=1/"&СТОЛБЕЦ(I1)&"/2020
Код
Лист1!$A:$A
Код
<1/"&СТОЛБЕЦ(J1)&"/2020
Здесь я сталкиваюсь с трудностью: в качестве диапазона_условия_1 я хочу, чтобы был весь Лист1:
Код
Лист1!1:1048576
и программа выдаёт ошибку в значении. Поэтапное вычисление формулы показывает, что успешно преобразуется функция СТОЛБЕЦ (диапазоны дат), ошибка возникает на этапе работы с диапазоном. Не могу понять, где конкретно скрыта ошибка. Спасибо за поддержку!
почему вариант, который я хотел бы в идеале реализовать, не работает
потому что СУММЕСЛИМН так не работает, почитайте справку о функции. Альтернативный подход через СУММПРОИЗВ. Обратите внимание на изменения на листе с исходными данными. Формулу не оптимизировал, она , по сути, под Ваш пример. Диапазон суммирования расширил с запасом. Здесь лучше без фанатизма, иначе будет сильно тормозить.
почему вариант, который я хотел бы в идеале реализовать, не работает
потому что СУММЕСЛИМН так не работает, почитайте справку о функции. Альтернативный подход через СУММПРОИЗВ. Обратите внимание на изменения на листе с исходными данными. Формулу не оптимизировал, она , по сути, под Ваш пример. Диапазон суммирования расширил с запасом. Здесь лучше без фанатизма, иначе будет сильно тормозить.
Nic70y, Che79, добрый вечер! Прошу прощения за поздний ответ. Я восхищён возможностями Экселя, которые вы показали! Работают оба метода, теперь мне предстоит изучить их подробнее и применять самостоятельно в моём документе. Спасибо Вам за то, что помогли воплотить идею с технической точки зрения)
Nic70y, Che79, добрый вечер! Прошу прощения за поздний ответ. Я восхищён возможностями Экселя, которые вы показали! Работают оба метода, теперь мне предстоит изучить их подробнее и применять самостоятельно в моём документе. Спасибо Вам за то, что помогли воплотить идею с технической точки зрения)Iment