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

Вход

Регистрация

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

 

= Мир MS Excel/Счет значений из диапазона по нескольким критериям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Счет значений из диапазона по нескольким критериям (Формулы/Formulas)
Счет значений из диапазона по нескольким критериям
isbobrov Дата: Пятница, 16.02.2018, 00:09 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Здравствуйте. Подскажите, пожалуйста, никак не получается связать вертикальный и двумерный массивы. %)
Если схематично, то есть столбец с городами магазинов, столбец фио обученного и далее столбцы с датами проведенных тренингов.


Необходимо посчитать количество проведенных тренингов (Ячейки С2:E5) в городе Казань при критериях:
1. Что в столбце B обязательно будет текст
2. Что тренинги проводились в нужном нам диапазоне дат. От 01.01.2018 до 01.04.2018, например
Ответ должен быть 6

Дополнение:
Человек считается обученным, если ему были прочитаны все три тренинга, по горизонтали и с сохранением условий с городом и датами. Причем количество тренингов для счета строки так же необходимо редактировать.
Если считаем по 3-м тренингам, то в данном примере ответ "1". только Сидоров подходит под требуемые параметры
Если по 2-м, то уже Иванов и Сидоров

Заранее спасибо!
К сообщению приложен файл: 2610765.xlsx (9.8 Kb)


Сообщение отредактировал isbobrov - Пятница, 16.02.2018, 13:52
 
Ответить
СообщениеЗдравствуйте. Подскажите, пожалуйста, никак не получается связать вертикальный и двумерный массивы. %)
Если схематично, то есть столбец с городами магазинов, столбец фио обученного и далее столбцы с датами проведенных тренингов.


Необходимо посчитать количество проведенных тренингов (Ячейки С2:E5) в городе Казань при критериях:
1. Что в столбце B обязательно будет текст
2. Что тренинги проводились в нужном нам диапазоне дат. От 01.01.2018 до 01.04.2018, например
Ответ должен быть 6

Дополнение:
Человек считается обученным, если ему были прочитаны все три тренинга, по горизонтали и с сохранением условий с городом и датами. Причем количество тренингов для счета строки так же необходимо редактировать.
Если считаем по 3-м тренингам, то в данном примере ответ "1". только Сидоров подходит под требуемые параметры
Если по 2-м, то уже Иванов и Сидоров

Заранее спасибо!
Автор - isbobrov
Дата добавления - 16.02.2018 в 00:09
Che79 Дата: Пятница, 16.02.2018, 00:31 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
isbobrov, здравствуйте. Так?
Код
=СУММПРОИЗВ((C2:E7>=A13)*(C2:E7<=B13)*(B2:B7>"")*(A2:A7="Казань"))
К сообщению приложен файл: _111-.xlsx (9.2 Kb)

Делай нормально и будет нормально!
 
Ответить
Сообщениеisbobrov, здравствуйте. Так?
Код
=СУММПРОИЗВ((C2:E7>=A13)*(C2:E7<=B13)*(B2:B7>"")*(A2:A7="Казань"))
Автор - Che79
Дата добавления - 16.02.2018 в 00:31
isbobrov Дата: Пятница, 16.02.2018, 02:11 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Да, совершенно верно :) Спасибо.

А если задать еще условие.
Человек считается обученным, если были прочитаны все три тренинга. (С сохранением условий с городом и датами) То есть в данном примере ответ будет один. Подскажете?


Сообщение отредактировал isbobrov - Пятница, 16.02.2018, 13:26
 
Ответить
СообщениеДа, совершенно верно :) Спасибо.

А если задать еще условие.
Человек считается обученным, если были прочитаны все три тренинга. (С сохранением условий с городом и датами) То есть в данном примере ответ будет один. Подскажете?
Автор - isbobrov
Дата добавления - 16.02.2018 в 02:11
Che79 Дата: Пятница, 16.02.2018, 03:53 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Проверьте, так?
Код
=СЧЁТЕСЛИМН(A2:A7;A2;B2:B7;"<>";C2:C7;">="&A13;D2:D7;">="&A13;E2:E7;">="&A13;C2:C7;"<="&B13;D2:D7;"<="&B13;E2:E7;"<="&B13)
Результат в зелёной ячейке. Красные ячейки добавлены для проверки. Если данные из них удалить, то получаем искомые 6 и 1 соответственно.
К сообщению приложен файл: _111-1.xlsx (9.5 Kb)

Делай нормально и будет нормально!
 
Ответить
СообщениеПроверьте, так?
Код
=СЧЁТЕСЛИМН(A2:A7;A2;B2:B7;"<>";C2:C7;">="&A13;D2:D7;">="&A13;E2:E7;">="&A13;C2:C7;"<="&B13;D2:D7;"<="&B13;E2:E7;"<="&B13)
Результат в зелёной ячейке. Красные ячейки добавлены для проверки. Если данные из них удалить, то получаем искомые 6 и 1 соответственно.
Автор - Che79
Дата добавления - 16.02.2018 в 03:53
isbobrov Дата: Пятница, 16.02.2018, 12:51 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо, но формула считает по вертикали, а нужно по горизонтали
Должно быть:если Сидоров прослушал три тренинга, то это считается за 1-цу. (условие 3 тренинга так же надо редактировать в диапазоне от 1 до 6. или в любом требуемом с сохранением условий по городам, фио и датам)
И можно как-то сделать массивом? В оригинальном файле 200+ строк. По одной строке добавлять в условие слишком долго


Сообщение отредактировал isbobrov - Пятница, 16.02.2018, 13:26
 
Ответить
СообщениеСпасибо, но формула считает по вертикали, а нужно по горизонтали
Должно быть:если Сидоров прослушал три тренинга, то это считается за 1-цу. (условие 3 тренинга так же надо редактировать в диапазоне от 1 до 6. или в любом требуемом с сохранением условий по городам, фио и датам)
И можно как-то сделать массивом? В оригинальном файле 200+ строк. По одной строке добавлять в условие слишком долго
Автор - isbobrov
Дата добавления - 16.02.2018 в 12:51
Pelena Дата: Пятница, 16.02.2018, 13:21 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19177
Репутация: 4417 ±
Замечаний: ±

Excel 365 & Mac Excel
isbobrov, не надо цитировать пост целиком. Это нарушение Правил форума. Исправьте в обоих сообщениях

По теме: будет нагляднее, если Вы в файле вручную покажете, что должно получиться

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеisbobrov, не надо цитировать пост целиком. Это нарушение Правил форума. Исправьте в обоих сообщениях

По теме: будет нагляднее, если Вы в файле вручную покажете, что должно получиться
Автор - Pelena
Дата добавления - 16.02.2018 в 13:21
isbobrov Дата: Пятница, 16.02.2018, 13:43 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
К сообщению приложен файл: 123.xlsx (9.8 Kb)


Сообщение отредактировал isbobrov - Пятница, 16.02.2018, 13:47
 
Ответить
Сообщение
Автор - isbobrov
Дата добавления - 16.02.2018 в 13:43
Che79 Дата: Суббота, 17.02.2018, 19:18 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Цитата
Смотрим столбец B. В нем должен быть текст минимум из 3х символов
А если там фамилия из двух букв (например, Ан), её пропускаем? Вероятность появления такого варианта на 400+ строках совсем небольшая, но она есть. Вы бы приложили файл строк на 200 (можно архивом), сохранив при этом реальную структуру файла. И города в С10 лучше вносить с помощью выпадающего списка.

Делай нормально и будет нормально!
 
Ответить
Сообщение
Цитата
Смотрим столбец B. В нем должен быть текст минимум из 3х символов
А если там фамилия из двух букв (например, Ан), её пропускаем? Вероятность появления такого варианта на 400+ строках совсем небольшая, но она есть. Вы бы приложили файл строк на 200 (можно архивом), сохранив при этом реальную структуру файла. И города в С10 лучше вносить с помощью выпадающего списка.
Автор - Che79
Дата добавления - 17.02.2018 в 19:18
isbobrov Дата: Воскресенье, 18.02.2018, 12:24 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата Che79, 17.02.2018 в 19:18, в сообщении № 8 ()
А если там фамилия из двух букв (например, Ан), её пропускаем? Вероятность появления такого варианта на 400+ строках совсем небольшая, но она есть. Вы бы приложили файл строк на 200 (можно архивом), сохранив при этом реальную структуру файла. И города в С10 лучше вносить с помощью выпадающего списка.

Если фамилия из двух букв, то можно воспользоваться чем-то подобным "*".
Другой файл прикладывать не буду, так как считаю, что этого достаточно.
Насчет выпадающего списка. Это все мелочи.
Главное увидеть формулу :)
 
Ответить
Сообщение
Цитата Che79, 17.02.2018 в 19:18, в сообщении № 8 ()
А если там фамилия из двух букв (например, Ан), её пропускаем? Вероятность появления такого варианта на 400+ строках совсем небольшая, но она есть. Вы бы приложили файл строк на 200 (можно архивом), сохранив при этом реальную структуру файла. И города в С10 лучше вносить с помощью выпадающего списка.

Если фамилия из двух букв, то можно воспользоваться чем-то подобным "*".
Другой файл прикладывать не буду, так как считаю, что этого достаточно.
Насчет выпадающего списка. Это все мелочи.
Главное увидеть формулу :)
Автор - isbobrov
Дата добавления - 18.02.2018 в 12:24
Pelena Дата: Вторник, 20.02.2018, 09:49 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19177
Репутация: 4417 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата isbobrov, 18.02.2018 в 12:24, в сообщении № 9 ()
Главное увидеть формулу

Код
=СУММ(($C10=$A$2:$A$7)*(ДЛСТР($B$2:$B$7)>2)*(МУМНОЖ(($C$2:$E$7>=$A10)*($C$2:$E$7<=$B10);ТРАНСП(СТОЛБЕЦ($C$1:$E$1))^0)=D$9))
К сообщению приложен файл: 9739882.xlsx (10.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата isbobrov, 18.02.2018 в 12:24, в сообщении № 9 ()
Главное увидеть формулу

Код
=СУММ(($C10=$A$2:$A$7)*(ДЛСТР($B$2:$B$7)>2)*(МУМНОЖ(($C$2:$E$7>=$A10)*($C$2:$E$7<=$B10);ТРАНСП(СТОЛБЕЦ($C$1:$E$1))^0)=D$9))
Автор - Pelena
Дата добавления - 20.02.2018 в 09:49
isbobrov Дата: Вторник, 20.02.2018, 13:37 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Елена, Вы гениий! Большое спасибо!!! Очень круто :)
 
Ответить
СообщениеЕлена, Вы гениий! Большое спасибо!!! Очень круто :)
Автор - isbobrov
Дата добавления - 20.02.2018 в 13:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Счет значений из диапазона по нескольким критериям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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