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

Вход

Регистрация

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

 

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

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

Excel 2007
Всем Доброго Времени!!
Не могу найти оптимальное решение такой задачи.

Имеется от 150 до 200 приборов (В примере - 5). У каждого из которых, свой ID (первый столбец листа "Дано").
Приборы фиксируют 4 показателя. Тестирование приборов состоит из 20 испытаний.
Range1_1, Data1, Range1_2, Value1 - результаты первого испытания;
Range2_1, Data2, Range2_2, Value2 - результаты второго испытания;
...
Range20_1, Data20, Range20_2, Value20

В прилагаемом файле - 4 испытания.
Ячейки в стобцах Value принимают только два значения Х или Y. В ячейке S1 - некоторое тестовое значение (S1=10).
Резульаты новых тестирований постоянно добавляются в таблицу.
Нужно подсчитать количество X-критериев и Y-критериев для каждого прибора.
Так, для прибора с ID=1 Х-критерий = 5, это количество значений в столбцах Range, совпадающих с S1 и соответствующих Value=X (ячейки выделены зелёным цветом).
Аналогично для ID=1, Y-критерий = 7 (ячейки выделены жёлтым цветом).
Т.е. значения столбцов Data не участвуют в подсчете.
Результат, который нужно получить, приведён на листе "Итог".

Попытка использовать СУММПРОИЗ, СЧЕТЕСЛИМН, сводные таблицы к результату не привела :(

Спасибо.
К сообщению приложен файл: frd_13.xls (21.5 Kb)
 
Ответить
СообщениеВсем Доброго Времени!!
Не могу найти оптимальное решение такой задачи.

Имеется от 150 до 200 приборов (В примере - 5). У каждого из которых, свой ID (первый столбец листа "Дано").
Приборы фиксируют 4 показателя. Тестирование приборов состоит из 20 испытаний.
Range1_1, Data1, Range1_2, Value1 - результаты первого испытания;
Range2_1, Data2, Range2_2, Value2 - результаты второго испытания;
...
Range20_1, Data20, Range20_2, Value20

В прилагаемом файле - 4 испытания.
Ячейки в стобцах Value принимают только два значения Х или Y. В ячейке S1 - некоторое тестовое значение (S1=10).
Резульаты новых тестирований постоянно добавляются в таблицу.
Нужно подсчитать количество X-критериев и Y-критериев для каждого прибора.
Так, для прибора с ID=1 Х-критерий = 5, это количество значений в столбцах Range, совпадающих с S1 и соответствующих Value=X (ячейки выделены зелёным цветом).
Аналогично для ID=1, Y-критерий = 7 (ячейки выделены жёлтым цветом).
Т.е. значения столбцов Data не участвуют в подсчете.
Результат, который нужно получить, приведён на листе "Итог".

Попытка использовать СУММПРОИЗ, СЧЕТЕСЛИМН, сводные таблицы к результату не привела :(

Спасибо.
Автор - Isa
Дата добавления - 13.12.2013 в 22:34
Pelena Дата: Пятница, 13.12.2013, 23:19 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19187
Репутация: 4421 ±
Замечаний: ±

Excel 365 & Mac Excel
Вариант с доп. столбцами
К сообщению приложен файл: 0235048.xls (28.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВариант с доп. столбцами
Автор - Pelena
Дата добавления - 13.12.2013 в 23:19
Hugo Дата: Пятница, 13.12.2013, 23:20 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3254
Репутация: 707 ±
Замечаний: 0% ±

2019
Получилось только с UDF.
Там у Вас ошибка - выделил жёлтым в своих результатах.
Ну и X желательно всюду писать одинаково! :)
К сообщению приложен файл: frd_13H.xls (37.0 Kb)

excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеПолучилось только с UDF.
Там у Вас ошибка - выделил жёлтым в своих результатах.
Ну и X желательно всюду писать одинаково! :)
Автор - Hugo
Дата добавления - 13.12.2013 в 23:20
SergeyKorotun Дата: Пятница, 13.12.2013, 23:22 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Сводной, но изменена структура хранения данных
К сообщению приложен файл: qwerty13.xls (58.5 Kb)
 
Ответить
СообщениеСводной, но изменена структура хранения данных
Автор - SergeyKorotun
Дата добавления - 13.12.2013 в 23:22
Isa Дата: Пятница, 13.12.2013, 23:49 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, Hugo, SergeyKKorotun, большое спасибо! За разные подходы и за идеи, которые у меня появились.
Если можно, ещё один вопрос: обработка файла ок. 1500 строк много времени займёт?
Спасибо!
 
Ответить
СообщениеPelena, Hugo, SergeyKKorotun, большое спасибо! За разные подходы и за идеи, которые у меня появились.
Если можно, ещё один вопрос: обработка файла ок. 1500 строк много времени займёт?
Спасибо!
Автор - Isa
Дата добавления - 13.12.2013 в 23:49
Isa Дата: Суббота, 14.12.2013, 00:08 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Hugo, будет ли работать Ваша функция с динамическими именованными диапазонами?
Спасибо
 
Ответить
СообщениеHugo, будет ли работать Ваша функция с динамическими именованными диапазонами?
Спасибо
Автор - Isa
Дата добавления - 14.12.2013 в 00:08
Hugo Дата: Суббота, 14.12.2013, 00:14 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3254
Репутация: 707 ±
Замечаний: 0% ±

2019
1500 - это ерунда. Думаю UDF или сводная за пару секунд справятся. Массивные формулы чуть дольше (их там и больше нужно вводить).
Вообще если задача частая и критерии ID/X/Y постоянные - теоретически можно написать одну массивную UDF, которая будет делать всю работу за доли секунды. Подменяй только анализируемые данные.
Практически некогда - там код будет посложнее.

P.S. Должна с динамическими, попробуйте.
Там их всего два - ID и таблица данных, где в первом столбце эти ID. Два других критерия - номер и буква, их можно брать с листа или вводить прямо в формулу.

excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
Сообщение1500 - это ерунда. Думаю UDF или сводная за пару секунд справятся. Массивные формулы чуть дольше (их там и больше нужно вводить).
Вообще если задача частая и критерии ID/X/Y постоянные - теоретически можно написать одну массивную UDF, которая будет делать всю работу за доли секунды. Подменяй только анализируемые данные.
Практически некогда - там код будет посложнее.

P.S. Должна с динамическими, попробуйте.
Там их всего два - ID и таблица данных, где в первом столбце эти ID. Два других критерия - номер и буква, их можно брать с листа или вводить прямо в формулу.
Автор - Hugo
Дата добавления - 14.12.2013 в 00:14
Isa Дата: Суббота, 14.12.2013, 00:23 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Hugo, понялЪ. Спасибо за идею. Надеюсь, что с кодом потихоньку справлюсь.
Всем Модераторам - Respect.
hands
 
Ответить
СообщениеHugo, понялЪ. Спасибо за идею. Надеюсь, что с кодом потихоньку справлюсь.
Всем Модераторам - Respect.
hands
Автор - Isa
Дата добавления - 14.12.2013 в 00:23
Hugo Дата: Суббота, 14.12.2013, 00:29 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3254
Репутация: 707 ±
Замечаний: 0% ±

2019
Да с этим уже ничего делать не нужно.
Ну а если писать одну массивную - тогда она должна возвращать сразу двумерный массив значений, анализируя за один проход по исходному массиву сразу всё.
Т.е. в итоге будет работать не много отдельных формул (по одной на ячейку), каждая из которых бегает по исходным данным, а всего одна.

excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеДа с этим уже ничего делать не нужно.
Ну а если писать одну массивную - тогда она должна возвращать сразу двумерный массив значений, анализируя за один проход по исходному массиву сразу всё.
Т.е. в итоге будет работать не много отдельных формул (по одной на ячейку), каждая из которых бегает по исходным данным, а всего одна.
Автор - Hugo
Дата добавления - 14.12.2013 в 00:29
Isa Дата: Суббота, 14.12.2013, 00:45 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Hugo, забрезжил свет в конце "тоннеля", спасибо!
 
Ответить
СообщениеHugo, забрезжил свет в конце "тоннеля", спасибо!
Автор - Isa
Дата добавления - 14.12.2013 в 00:45
Hugo Дата: Суббота, 14.12.2013, 01:01 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3254
Репутация: 707 ±
Замечаний: 0% ±

2019
Вот на скорую руку переделал первый вариант на массив - вроде вполне работает. Проверьте на своих 1500.
Вводить сразу в диапазон как формулу массива!
К сообщению приложен файл: frd_13-UDF.xls (42.5 Kb)

excel@nxt.ru
webmoney: E265281470651 Z422237915069
 
Ответить
СообщениеВот на скорую руку переделал первый вариант на массив - вроде вполне работает. Проверьте на своих 1500.
Вводить сразу в диапазон как формулу массива!
Автор - Hugo
Дата добавления - 14.12.2013 в 01:01
_Boroda_ Дата: Суббота, 14.12.2013, 01:57 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Обычная формула
Код
=СУММПРОИЗВ(($D$3:$P$10=$S$1)*($E$3:$Q$10=ЛЕВБ(T$2))*($A$3:$A$10=$S3))+СУММПРОИЗВ(($B$3:$N$10=$S$1)*($E$3:$Q$10=ЛЕВБ(T$2))*($A$3:$A$10=$S3))

или
Код
=СУММПРОИЗВ((($D$3:$P$10=$S$1)+($B$3:$N$10=$S$1))*($E$3:$Q$10=ЛЕВБ(T$2))*($A$3:$A$10=$S3))
К сообщению приложен файл: frd_13_2.xls (32.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеОбычная формула
Код
=СУММПРОИЗВ(($D$3:$P$10=$S$1)*($E$3:$Q$10=ЛЕВБ(T$2))*($A$3:$A$10=$S3))+СУММПРОИЗВ(($B$3:$N$10=$S$1)*($E$3:$Q$10=ЛЕВБ(T$2))*($A$3:$A$10=$S3))

или
Код
=СУММПРОИЗВ((($D$3:$P$10=$S$1)+($B$3:$N$10=$S$1))*($E$3:$Q$10=ЛЕВБ(T$2))*($A$3:$A$10=$S3))
Автор - _Boroda_
Дата добавления - 14.12.2013 в 01:57
Isa Дата: Суббота, 14.12.2013, 22:41 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Hugo, да всё ОК. Первый вариант уже заработал с именованными динамическими диапазонами.
И второй тоже.
Спасибище!
 
Ответить
СообщениеHugo, да всё ОК. Первый вариант уже заработал с именованными динамическими диапазонами.
И второй тоже.
Спасибище!
Автор - Isa
Дата добавления - 14.12.2013 в 22:41
Isa Дата: Суббота, 14.12.2013, 22:48 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, драйв от Ваших уроков (решений).
 
Ответить
Сообщение_Boroda_, драйв от Ваших уроков (решений).
Автор - Isa
Дата добавления - 14.12.2013 в 22:48
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет в двумерной таблице по нескольким критериям (Формулы)
  • Страница 1 из 1
  • 1
Поиск:

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