Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/СЧЁТЕСЛИ: Дополнительный диапазон - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » СЧЁТЕСЛИ: Дополнительный диапазон (Формулы/Formulas)
СЧЁТЕСЛИ: Дополнительный диапазон
Iment Дата: Среда, 14.10.2020, 10:27 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день, коллеги!
Известно, что в функции СЧЁТЕСЛИ есть два компонента: диапазон и критерий. Я знаю, что стандартный вариант формулы СЧЁТЕСЛИ для всего Листа1 в качестве диапазона, и значения Х в качестве критерия выглядит так:
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")

Скажите, пожалуйста, как добавить ещё один критерий, который должен по сути быть маленьким диапазоном. Т.е., нужен "диапазон в диапазоне". Например, необходимо, чтобы на Листе1 формула посчитала количество ячеек со значением Х в диапазоне строк, содержащих даты от даты Н до М?
Я думал о том, что это можно реализовать тремя вариантами:
"простой вариант": добавить в формулу ещё один критерий, содержащий конкретный диапазон дат;
либо "сложный вариант": сделать формулу, которая присваивала бы этим датам буквенный код и добавить в качестве критерия этот код (тогда необходимо делить итоговую сумму на 2);
либо третий неэффективный "ручной" вариант - прописать множество критериев с отдельными значениями дат, которые будут учитываться формулой. Но для этого потребуется уже СЧЁТЕСЛИМН, если я правильно понимаю.
Долго пытался задать в качестве дополнительного критерия диапазон, используя хоть какой-нибудь вариант, при этом попытки не увенчались успехом. Надежда всё-таки есть на то, что, используя эту формулу, можно реализовать эту идею. Скажите, пожалуйста, как вы видите необходимую формулу?
Благодарю!


Сообщение отредактировал Pelena - Среда, 14.10.2020, 16:43
 
Ответить
СообщениеДобрый день, коллеги!
Известно, что в функции СЧЁТЕСЛИ есть два компонента: диапазон и критерий. Я знаю, что стандартный вариант формулы СЧЁТЕСЛИ для всего Листа1 в качестве диапазона, и значения Х в качестве критерия выглядит так:
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")

Скажите, пожалуйста, как добавить ещё один критерий, который должен по сути быть маленьким диапазоном. Т.е., нужен "диапазон в диапазоне". Например, необходимо, чтобы на Листе1 формула посчитала количество ячеек со значением Х в диапазоне строк, содержащих даты от даты Н до М?
Я думал о том, что это можно реализовать тремя вариантами:
"простой вариант": добавить в формулу ещё один критерий, содержащий конкретный диапазон дат;
либо "сложный вариант": сделать формулу, которая присваивала бы этим датам буквенный код и добавить в качестве критерия этот код (тогда необходимо делить итоговую сумму на 2);
либо третий неэффективный "ручной" вариант - прописать множество критериев с отдельными значениями дат, которые будут учитываться формулой. Но для этого потребуется уже СЧЁТЕСЛИМН, если я правильно понимаю.
Долго пытался задать в качестве дополнительного критерия диапазон, используя хоть какой-нибудь вариант, при этом попытки не увенчались успехом. Надежда всё-таки есть на то, что, используя эту формулу, можно реализовать эту идею. Скажите, пожалуйста, как вы видите необходимую формулу?
Благодарю!

Автор - Iment
Дата добавления - 14.10.2020 в 10:27
Светлый Дата: Среда, 14.10.2020, 11:52 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
СЧЁТЕСЛИМН с несколькими критериями и диапазонами. А вот если бы был файл, то было бы легче помочь.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
СЧЁТЕСЛИМН с несколькими критериями и диапазонами. А вот если бы был файл, то было бы легче помочь.

Автор - Светлый
Дата добавления - 14.10.2020 в 11:52
Iment Дата: Среда, 14.10.2020, 12:36 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Светлый, благодарю за отклик и за указание точного направление поиска! Буду пытаться применить функцию СЧЁТЕСЛИМН.
Я смоделировал идентичный по логике файл, спасибо за поддержку!
К сообщению приложен файл: example.xlsx (10.1 Kb)
 
Ответить
СообщениеСветлый, благодарю за отклик и за указание точного направление поиска! Буду пытаться применить функцию СЧЁТЕСЛИМН.
Я смоделировал идентичный по логике файл, спасибо за поддержку!

Автор - Iment
Дата добавления - 14.10.2020 в 12:36
Nic70y Дата: Среда, 14.10.2020, 12:52 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 8703
Репутация: 2258 ±
Замечаний: 0% ±

Excel 2010
Код
=СЧЁТЕСЛИМН(Лист1!$B:$B;$A2;Лист1!$A:$A;">=1/"&СТОЛБЕЦ(I1)&"/2020";Лист1!$A:$A;"<1/"&СТОЛБЕЦ(J1)&"/2020")


ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=СЧЁТЕСЛИМН(Лист1!$B:$B;$A2;Лист1!$A:$A;">=1/"&СТОЛБЕЦ(I1)&"/2020";Лист1!$A:$A;"<1/"&СТОЛБЕЦ(J1)&"/2020")

Автор - Nic70y
Дата добавления - 14.10.2020 в 12:52
Iment Дата: Среда, 14.10.2020, 13:51 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Nic70y, здорово, вы открыли для меня понимание функции СТОЛБЕЦ и её возможностей, помогли реализовать идею! Спасибо за готовый вариант формулы, очень наглядно.

Правка: коллеги, вопрос решён, ещё раз благодарю вас за помощь!


Сообщение отредактировал Iment - Среда, 14.10.2020, 13:54
 
Ответить
СообщениеNic70y, здорово, вы открыли для меня понимание функции СТОЛБЕЦ и её возможностей, помогли реализовать идею! Спасибо за готовый вариант формулы, очень наглядно.

Правка: коллеги, вопрос решён, ещё раз благодарю вас за помощь!

Автор - Iment
Дата добавления - 14.10.2020 в 13:51
Iment Дата: Среда, 14.10.2020, 19:20 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Nic70y, уточните, пожалуйста, я правильно понимаю, что функция СЧЁТЕСЛИМН не позволяет выбрать в качестве диапазона весть лист (как это реализовано в функции
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")
, а только лишь один столбец? Допустим, данные разбросаны по всему листу в разных столбцах.
Я смоделировал второй образец, прошу посмотреть.
Благодарю!
К сообщению приложен файл: example2.xlsx (10.3 Kb)
 
Ответить
СообщениеNic70y, уточните, пожалуйста, я правильно понимаю, что функция СЧЁТЕСЛИМН не позволяет выбрать в качестве диапазона весть лист (как это реализовано в функции
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")
, а только лишь один столбец? Допустим, данные разбросаны по всему листу в разных столбцах.
Я смоделировал второй образец, прошу посмотреть.
Благодарю!

Автор - Iment
Дата добавления - 14.10.2020 в 19:20
Pelena Дата: Среда, 14.10.2020, 20:29 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19158
Репутация: 4411 ±
Замечаний: ±

Excel 365 & Mac Excel
Iment, оформите формулу тегами с помощью кнопки fx в режиме правки поста


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеIment, оформите формулу тегами с помощью кнопки fx в режиме правки поста

Автор - Pelena
Дата добавления - 14.10.2020 в 20:29
Iment Дата: Суббота, 17.10.2020, 09:46 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, не успел поправить, благодарю за уточнение. Формула выглядит так:
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")


Сообщение отредактировал Iment - Суббота, 17.10.2020, 09:47
 
Ответить
СообщениеPelena, не успел поправить, благодарю за уточнение. Формула выглядит так:
Код
=СЧЁТЕСЛИ('Лист1'!1:1048576;"Значение Х")

Автор - Iment
Дата добавления - 17.10.2020 в 09:46
Nic70y Дата: Суббота, 17.10.2020, 10:38 | Сообщение № 9
Группа: Друзья
Ранг: Экселист
Сообщений: 8703
Репутация: 2258 ±
Замечаний: 0% ±

Excel 2010
Допустим, данные разбросаны по всему листу в разных столбцах
в
Я смоделировал второй образец
не так,
пример имеет довольно правильную структуру.
что значит разбросаны?
есть какая-то закономерность наверное.


ЮMoney 41001841029809
 
Ответить
Сообщение
Допустим, данные разбросаны по всему листу в разных столбцах
в
Я смоделировал второй образец
не так,
пример имеет довольно правильную структуру.
что значит разбросаны?
есть какая-то закономерность наверное.

Автор - Nic70y
Дата добавления - 17.10.2020 в 10:38
Iment Дата: Суббота, 17.10.2020, 16:24 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
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.
прикрепляю корректный пример:
К сообщению приложен файл: 2986230.xlsx (10.5 Kb)
 
Ответить
Сообщение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.
прикрепляю корректный пример:

Автор - Iment
Дата добавления - 17.10.2020 в 16:24
Che79 Дата: Воскресенье, 18.10.2020, 12:56 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Добрый день.
почему вариант, который я хотел бы в идеале реализовать, не работает
потому что СУММЕСЛИМН так не работает, почитайте справку о функции. Альтернативный подход через СУММПРОИЗВ. Обратите внимание на изменения на листе с исходными данными. Формулу не оптимизировал, она , по сути, под Ваш пример. Диапазон суммирования расширил с запасом. Здесь лучше без фанатизма, иначе будет сильно тормозить.
Код
=СУММПРОИЗВ((Лист1!$B$3:$Z$500=$A2)*(МЕСЯЦ(Лист1!$A$3:$A$500)=(ПОИСК(ПСТР(ПСТР(C$1;ПОИСК(" ";C$1)+1;ПОИСК("все";C$1)-ПОИСК(" ";C$1)-3);2;2);"нвеварпрайюнюлвгенктояек")+1)/2))
К сообщению приложен файл: 2986230-1.xlsx (11.2 Kb)


Делай нормально и будет нормально!
 
Ответить
СообщениеДобрый день.
почему вариант, который я хотел бы в идеале реализовать, не работает
потому что СУММЕСЛИМН так не работает, почитайте справку о функции. Альтернативный подход через СУММПРОИЗВ. Обратите внимание на изменения на листе с исходными данными. Формулу не оптимизировал, она , по сути, под Ваш пример. Диапазон суммирования расширил с запасом. Здесь лучше без фанатизма, иначе будет сильно тормозить.
Код
=СУММПРОИЗВ((Лист1!$B$3:$Z$500=$A2)*(МЕСЯЦ(Лист1!$A$3:$A$500)=(ПОИСК(ПСТР(ПСТР(C$1;ПОИСК(" ";C$1)+1;ПОИСК("все";C$1)-ПОИСК(" ";C$1)-3);2;2);"нвеварпрайюнюлвгенктояек")+1)/2))

Автор - Che79
Дата добавления - 18.10.2020 в 12:56
Nic70y Дата: Понедельник, 19.10.2020, 08:48 | Сообщение № 12
Группа: Друзья
Ранг: Экселист
Сообщений: 8703
Репутация: 2258 ±
Замечаний: 0% ±

Excel 2010
если даты по-порядку, как в примере, можно так:
Код
=СЧЁТЕСЛИ(ИНДЕКС(Лист1!$1:$1048576;СЧЁТЕСЛИ(Лист1!$A:$A;"<1/"&СТОЛБЕЦ(I1)&"/2020")+3;):ИНДЕКС(Лист1!$1:$1048576;СЧЁТЕСЛИ(Лист1!$A:$A;"<1/"&СТОЛБЕЦ(J1)&"/2020")+2;);$A2)
К сообщению приложен файл: 5079369.xlsx (10.9 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщениеесли даты по-порядку, как в примере, можно так:
Код
=СЧЁТЕСЛИ(ИНДЕКС(Лист1!$1:$1048576;СЧЁТЕСЛИ(Лист1!$A:$A;"<1/"&СТОЛБЕЦ(I1)&"/2020")+3;):ИНДЕКС(Лист1!$1:$1048576;СЧЁТЕСЛИ(Лист1!$A:$A;"<1/"&СТОЛБЕЦ(J1)&"/2020")+2;);$A2)

Автор - Nic70y
Дата добавления - 19.10.2020 в 08:48
Iment Дата: Среда, 28.10.2020, 18:17 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Nic70y, Che79, добрый вечер! Прошу прощения за поздний ответ.
Я восхищён возможностями Экселя, которые вы показали! Работают оба метода, теперь мне предстоит изучить их подробнее и применять самостоятельно в моём документе. Спасибо Вам за то, что помогли воплотить идею с технической точки зрения)
 
Ответить
СообщениеNic70y, Che79, добрый вечер! Прошу прощения за поздний ответ.
Я восхищён возможностями Экселя, которые вы показали! Работают оба метода, теперь мне предстоит изучить их подробнее и применять самостоятельно в моём документе. Спасибо Вам за то, что помогли воплотить идею с технической точки зрения)

Автор - Iment
Дата добавления - 28.10.2020 в 18:17
Мир MS Excel » Вопросы и решения » Вопросы по Excel » СЧЁТЕСЛИ: Дополнительный диапазон (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!