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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчет кол-ва строк с условиями на две колонки формулой - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет кол-ва строк с условиями на две колонки формулой (Формулы)
Подсчет кол-ва строк с условиями на две колонки формулой
nsmirnofff Дата: Вторник, 17.09.2013, 21:28 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Имеется таблица с кол-вом строк несколько тысяч и тремя колонками.
A:Job_name B:Org_Name C:Date_finish
Колонка B исполнитель (Org_Name) может быть только с двумя значениями – «ЗАО 1» , и «ЗАО 2». Признаком завершения работы является колонка С. Если она заполнена, то работа завершена в указанную дату.
Для того, чтобы посчитать кол-во выполненных работ поквартально, и в разрезе исполнителей, мне приходится вручную устанавливать фильтры на колонку С и B, и смотреть сколько же получилось строк.
Не могу понять, как сделать формулу, как автоматом посчитать кол-во строк, с условием на две колонки – С и B? Причем С – диапазон дат (1 квартал – с 01.01.2013 до 31.03.2013, 2 квартал с 01.04.2013 до 31.06.2013, и т.д.), а B уже строгое равенство один из двух вариантов. Пробовал варианты с СУММЕСЛИ() и СУММ(ЕСЛИ()), что то ничего не выходит. Макросы не предлагайте, по ряду причин нужна формула.
Вопрос срочный, горит, так что уж простите если что-то не по феншую.

UPD

Простите, вопрос не совсем соответствует примеру, но только в деталях. Приложил нужный файл 1ate.xls, внизу таблицы нужно подвести итоги, предложенная формула СУММПРОИЗВ почему-то не работает. Исполнитель бывает или "Подрядчик", или пустое значение, признаком выполнения работы является наличие даты.
К сообщению приложен файл: ate.xls (68.0 Kb) · 1ate.xls (65.5 Kb)


Сообщение отредактировал nsmirnofff - Среда, 18.09.2013, 09:16
 
Ответить
СообщениеИмеется таблица с кол-вом строк несколько тысяч и тремя колонками.
A:Job_name B:Org_Name C:Date_finish
Колонка B исполнитель (Org_Name) может быть только с двумя значениями – «ЗАО 1» , и «ЗАО 2». Признаком завершения работы является колонка С. Если она заполнена, то работа завершена в указанную дату.
Для того, чтобы посчитать кол-во выполненных работ поквартально, и в разрезе исполнителей, мне приходится вручную устанавливать фильтры на колонку С и B, и смотреть сколько же получилось строк.
Не могу понять, как сделать формулу, как автоматом посчитать кол-во строк, с условием на две колонки – С и B? Причем С – диапазон дат (1 квартал – с 01.01.2013 до 31.03.2013, 2 квартал с 01.04.2013 до 31.06.2013, и т.д.), а B уже строгое равенство один из двух вариантов. Пробовал варианты с СУММЕСЛИ() и СУММ(ЕСЛИ()), что то ничего не выходит. Макросы не предлагайте, по ряду причин нужна формула.
Вопрос срочный, горит, так что уж простите если что-то не по феншую.

UPD

Простите, вопрос не совсем соответствует примеру, но только в деталях. Приложил нужный файл 1ate.xls, внизу таблицы нужно подвести итоги, предложенная формула СУММПРОИЗВ почему-то не работает. Исполнитель бывает или "Подрядчик", или пустое значение, признаком выполнения работы является наличие даты.
Автор - nsmirnofff
Дата добавления - 17.09.2013 в 21:28
Serge_007 Дата: Вторник, 17.09.2013, 22:11 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
с условием на две колонки – С и B
C каким условием?

Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
Колонка B исполнитель (Org_Name) может быть только с двумя значениями – «ЗАО 1» , и «ЗАО 2»
В примере нет таковых

Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
Для того, чтобы посчитать кол-во выполненных работ поквартально, и в разрезе исполнителей...
надо использовать сводную таблицу

Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
что-то не по феншую
Да все. Включая огромный (почти на тысячу строк пример, хотя достаточно и десятка) и бесполезный пример. Так что если
Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
Вопрос срочный, горит
потрудитесь внятно изложить проблему и предоставить нормальный пример

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
с условием на две колонки – С и B
C каким условием?

Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
Колонка B исполнитель (Org_Name) может быть только с двумя значениями – «ЗАО 1» , и «ЗАО 2»
В примере нет таковых

Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
Для того, чтобы посчитать кол-во выполненных работ поквартально, и в разрезе исполнителей...
надо использовать сводную таблицу

Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
что-то не по феншую
Да все. Включая огромный (почти на тысячу строк пример, хотя достаточно и десятка) и бесполезный пример. Так что если
Цитата (nsmirnofff, 17.09.2013 в 21:28, в сообщении № 1)
Вопрос срочный, горит
потрудитесь внятно изложить проблему и предоставить нормальный пример
Автор - Serge_007
Дата добавления - 17.09.2013 в 22:11
AlexM Дата: Среда, 18.09.2013, 00:50 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Кросс
На этот вопрос в проекте Ответы и Вопросы на Mail.ru я дал такой ответ.
Формула
Код
=СУММПРОИЗВ((B1:B1000="ЗАО 1")*(ПОИСКПОЗ(МЕСЯЦ(C1:C1000);{1:4:7:10})=1))

Первый критерий - "ЗАО 1"
Второй критерий номер квартала - 1


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеКросс
На этот вопрос в проекте Ответы и Вопросы на Mail.ru я дал такой ответ.
Формула
Код
=СУММПРОИЗВ((B1:B1000="ЗАО 1")*(ПОИСКПОЗ(МЕСЯЦ(C1:C1000);{1:4:7:10})=1))

Первый критерий - "ЗАО 1"
Второй критерий номер квартала - 1
Автор - AlexM
Дата добавления - 18.09.2013 в 00:50
nsmirnofff Дата: Среда, 18.09.2013, 09:16 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Serge_007, AlexM, Простите, вопрос не совсем соответствует примеру, но только в деталях. Приложил нужный файл 1ate.xls, внизу таблицы нужно подвести итоги, предложенная AlexM формула СУММПРОИЗВ почему-то не работает. Исполнитель бывает или "Подрядчик", или пустое значение, признаком выполнения работы является наличие даты.
 
Ответить
СообщениеSerge_007, AlexM, Простите, вопрос не совсем соответствует примеру, но только в деталях. Приложил нужный файл 1ate.xls, внизу таблицы нужно подвести итоги, предложенная AlexM формула СУММПРОИЗВ почему-то не работает. Исполнитель бывает или "Подрядчик", или пустое значение, признаком выполнения работы является наличие даты.
Автор - nsmirnofff
Дата добавления - 18.09.2013 в 09:16
Serge_007 Дата: Среда, 18.09.2013, 09:26 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (nsmirnofff, 18.09.2013 в 09:16, в сообщении № 4)
формула СУММПРОИЗВ почему-то не работает
Потому что в первой строке текст. Так будет работать:
Код
=СУММПРОИЗВ((B2:B865="Подрядчик")*(ПОИСКПОЗ(МЕСЯЦ(C2:C865);{1:4:7:10})=1))

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (nsmirnofff, 18.09.2013 в 09:16, в сообщении № 4)
формула СУММПРОИЗВ почему-то не работает
Потому что в первой строке текст. Так будет работать:
Код
=СУММПРОИЗВ((B2:B865="Подрядчик")*(ПОИСКПОЗ(МЕСЯЦ(C2:C865);{1:4:7:10})=1))
Автор - Serge_007
Дата добавления - 18.09.2013 в 09:26
AlexM Дата: Среда, 18.09.2013, 10:19 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Цитата (Serge_007, 18.09.2013 в 09:26, в сообщении № 5)
Потому что в первой строке текст.

Когда писал формулу примера не было. Поэтому диапазон с первой строки.
К сообщению приложен файл: 1ate_new.xls (66.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Среда, 18.09.2013, 10:19
 
Ответить
Сообщение
Цитата (Serge_007, 18.09.2013 в 09:26, в сообщении № 5)
Потому что в первой строке текст.

Когда писал формулу примера не было. Поэтому диапазон с первой строки.
Автор - AlexM
Дата добавления - 18.09.2013 в 10:19
nsmirnofff Дата: Среда, 18.09.2013, 10:27 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Serge_007, спасибо, я до таких длинных формул еще не дорос. А что означает последовательность {1:4:7:10}, никак не пойму...?


Сообщение отредактировал nsmirnofff - Среда, 18.09.2013, 10:32
 
Ответить
СообщениеSerge_007, спасибо, я до таких длинных формул еще не дорос. А что означает последовательность {1:4:7:10}, никак не пойму...?
Автор - nsmirnofff
Дата добавления - 18.09.2013 в 10:27
AlexM Дата: Среда, 18.09.2013, 10:33 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Цитата (nsmirnofff, 18.09.2013 в 10:27, в сообщении № 7)
что означает последовательность {1:4:7:10}

Так определяем номер квартала.
Массив номеров месяцев, с которых начинается квартал.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата (nsmirnofff, 18.09.2013 в 10:27, в сообщении № 7)
что означает последовательность {1:4:7:10}

Так определяем номер квартала.
Массив номеров месяцев, с которых начинается квартал.
Автор - AlexM
Дата добавления - 18.09.2013 в 10:33
SergeyKorotun Дата: Среда, 18.09.2013, 11:30 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Цитата (Serge_007, 18.09.2013 в 09:26, в сообщении № 5)
Так будет работать:
Код
=СУММПРОИЗВ((B2:B865="Подрядчик")*(ПОИСКПОЗ(МЕСЯЦ(C2:C865);{1:4:7:10})=1))
И так не верно. Пустая дата учитывается.
К сообщению приложен файл: error.rar (54.1 Kb)


Сообщение отредактировал Serge_007 - Среда, 18.09.2013, 12:02
 
Ответить
Сообщение
Цитата (Serge_007, 18.09.2013 в 09:26, в сообщении № 5)
Так будет работать:
Код
=СУММПРОИЗВ((B2:B865="Подрядчик")*(ПОИСКПОЗ(МЕСЯЦ(C2:C865);{1:4:7:10})=1))
И так не верно. Пустая дата учитывается.
Автор - SergeyKorotun
Дата добавления - 18.09.2013 в 11:30
nsmirnofff Дата: Среда, 18.09.2013, 15:27 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Честно говоря, не могу никак применить рецепт в файле 1ate.xls. Даже если выдается какая-то цифира, а не #Знач, то не совпадает с кол-вом строк, получаемым в результате ручной фильтрации. Реально все указанные итоги заполнить формулой?

Фактические результаты ручной фильтрации:

Всего выполнено работ за 2012 год = 57
из них выполнил подрядчик = 16
из них выполнено самостоятельно = 41

Всего выполнено работ за 1 кв. 2012 года = 8
из них выполнил подрядчик = 3
из них выполнено самостоятельно = 5

и т.д.


Сообщение отредактировал nsmirnofff - Среда, 18.09.2013, 15:36
 
Ответить
СообщениеЧестно говоря, не могу никак применить рецепт в файле 1ate.xls. Даже если выдается какая-то цифира, а не #Знач, то не совпадает с кол-вом строк, получаемым в результате ручной фильтрации. Реально все указанные итоги заполнить формулой?

Фактические результаты ручной фильтрации:

Всего выполнено работ за 2012 год = 57
из них выполнил подрядчик = 16
из них выполнено самостоятельно = 41

Всего выполнено работ за 1 кв. 2012 года = 8
из них выполнил подрядчик = 3
из них выполнено самостоятельно = 5

и т.д.
Автор - nsmirnofff
Дата добавления - 18.09.2013 в 15:27
Serge_007 Дата: Среда, 18.09.2013, 15:47 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Чем больше пример - тем дольше ждать решения
См. вложение
Остальное - по аналогии
К сообщению приложен файл: nsmirnofff.xls (34.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЧем больше пример - тем дольше ждать решения
См. вложение
Остальное - по аналогии
Автор - Serge_007
Дата добавления - 18.09.2013 в 15:47
AlexM Дата: Среда, 18.09.2013, 18:21 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Цитата (SergeyKorotun, 18.09.2013 в 11:30, в сообщении № 9)
Пустая дата учитывается.

Поправил формулу, чтобы формула не учитывала пустые ячейки в столбце С
Но результат "выполнено самостоятельно" не совпадает с вашим.
К сообщению приложен файл: 4849171.xls (67.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата (SergeyKorotun, 18.09.2013 в 11:30, в сообщении № 9)
Пустая дата учитывается.

Поправил формулу, чтобы формула не учитывала пустые ячейки в столбце С
Но результат "выполнено самостоятельно" не совпадает с вашим.
Автор - AlexM
Дата добавления - 18.09.2013 в 18:21
nsmirnofff Дата: Среда, 18.09.2013, 18:27 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо, я взял решение Serge_007, все работает как мне надо.

=СУММПРОИЗВ((ГОД(C$2:C$20)=2012)*(МЕСЯЦ(C$2:C$20)<4)*(B$2:B$20="Подрядчик")*(C$2:C$20<>""))

Странно, у меня нет проблем с " учетом пустых ячеек в столбце С", условие (C$2:C$20<>"") никак не влияет, я его вообще убрал. Результат прилагается.

В общем, я не знал о таком применении СУММПРОИЗВ, я думал, что она только для перемножения массивов и все... Спасибо, еще раз, всем.
К сообщению приложен файл: rezultate.rar (34.6 Kb)


Сообщение отредактировал nsmirnofff - Среда, 18.09.2013, 18:50
 
Ответить
СообщениеСпасибо, я взял решение Serge_007, все работает как мне надо.

=СУММПРОИЗВ((ГОД(C$2:C$20)=2012)*(МЕСЯЦ(C$2:C$20)<4)*(B$2:B$20="Подрядчик")*(C$2:C$20<>""))

Странно, у меня нет проблем с " учетом пустых ячеек в столбце С", условие (C$2:C$20<>"") никак не влияет, я его вообще убрал. Результат прилагается.

В общем, я не знал о таком применении СУММПРОИЗВ, я думал, что она только для перемножения массивов и все... Спасибо, еще раз, всем.
Автор - nsmirnofff
Дата добавления - 18.09.2013 в 18:27
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет кол-ва строк с условиями на две колонки формулой (Формулы)
  • Страница 1 из 1
  • 1
Поиск:

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