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

Вход

Регистрация

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

 

= Мир MS Excel/Сумма данных по условию уникального значения - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма данных по условию уникального значения (Формулы/Formulas)
Сумма данных по условию уникального значения
matigovas Дата: Понедельник, 30.10.2017, 11:00 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Имеется некий массив данных, которые заносятся в книги работниками предприятия. Имеются колонки №, Иванов, Петров, Сидоров
1) В таблицу с № вносятся как числовые так и буквенные значения, причем числовые иногда повторяются как для Иванова, так иногда и для Петрова и Сидова.
2) в колонках с Фамилиями стоят 1 и 0, так работает журнал, тут не переделать

Суть задачи в том, чтобы просуммировать 1 в столбцах с фамилиями, причем только те, которые удовлетворяли бы ряду условий из столбца с №: 1) только числовые значения, буквы игнорируем 2) только уникальные значения (к примеру если в столбце с № есть в одно строке 123 и в другой 123, а в столбце с фамилией стоят напротив 1, то он бы считал их сумму как 1, а не как 2

Раньше пользовались формулой бдсумм и она до сих пор выполняет свои задачи, но появилось новое требование и появился затык, потому что массив данных очень большой, достигает 300-400 строк.

Быть может сумбурно написал, но я приложил файл для примера может подскажите с комбинацией формул или быть может через макрос
К сообщению приложен файл: 9090674.xlsx(46Kb)
 
Ответить
СообщениеИмеется некий массив данных, которые заносятся в книги работниками предприятия. Имеются колонки №, Иванов, Петров, Сидоров
1) В таблицу с № вносятся как числовые так и буквенные значения, причем числовые иногда повторяются как для Иванова, так иногда и для Петрова и Сидова.
2) в колонках с Фамилиями стоят 1 и 0, так работает журнал, тут не переделать

Суть задачи в том, чтобы просуммировать 1 в столбцах с фамилиями, причем только те, которые удовлетворяли бы ряду условий из столбца с №: 1) только числовые значения, буквы игнорируем 2) только уникальные значения (к примеру если в столбце с № есть в одно строке 123 и в другой 123, а в столбце с фамилией стоят напротив 1, то он бы считал их сумму как 1, а не как 2

Раньше пользовались формулой бдсумм и она до сих пор выполняет свои задачи, но появилось новое требование и появился затык, потому что массив данных очень большой, достигает 300-400 строк.

Быть может сумбурно написал, но я приложил файл для примера может подскажите с комбинацией формул или быть может через макрос

Автор - matigovas
Дата добавления - 30.10.2017 в 11:00
sboy Дата: Понедельник, 30.10.2017, 11:14 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1103
Репутация: 276 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Вот такой вариант
Код
=СУММПРОИЗВ(ИНДЕКС($C$3:$E$16;;ПОИСКПОЗ(I3;$C$1:$E$1;))*ЕЧИСЛО($B$3:$B$16)*(ПОИСКПОЗ($B$3:$B$16;$B$3:$B$16;)=(СТРОКА($B$3:$B$16)-2)))
К сообщению приложен файл: 7337080.xlsx(46Kb)
 
Ответить
СообщениеДобрый день.
Вот такой вариант
Код
=СУММПРОИЗВ(ИНДЕКС($C$3:$E$16;;ПОИСКПОЗ(I3;$C$1:$E$1;))*ЕЧИСЛО($B$3:$B$16)*(ПОИСКПОЗ($B$3:$B$16;$B$3:$B$16;)=(СТРОКА($B$3:$B$16)-2)))

Автор - sboy
Дата добавления - 30.10.2017 в 11:14
matigovas Дата: Понедельник, 30.10.2017, 12:01 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Вот такой вариант

спасибо большое, по файлу-примеру всё четко, пытаюсь адаптировать под исходный файл, а формула выдает #Н/Д, не могу понять где косяк
 
Ответить
Сообщение
Вот такой вариант

спасибо большое, по файлу-примеру всё четко, пытаюсь адаптировать под исходный файл, а формула выдает #Н/Д, не могу понять где косяк

Автор - matigovas
Дата добавления - 30.10.2017 в 12:01
sboy Дата: Понедельник, 30.10.2017, 12:07 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1103
Репутация: 276 ±
Замечаний: 0% ±

Excel 2010
не могу понять где косяк

по фотографии не лечим)
обрежьте или обезличте свой файл с ошибкой и приложите тут
 
Ответить
Сообщение
не могу понять где косяк

по фотографии не лечим)
обрежьте или обезличте свой файл с ошибкой и приложите тут

Автор - sboy
Дата добавления - 30.10.2017 в 12:07
matigovas Дата: Понедельник, 30.10.2017, 12:21 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
по фотографии не лечим)

долго читал справку по ошибке, тыкал, разобрался, в месте: *(ПОИСКПОЗ($B$3:$B$16;$B$3:$B$16;) забыл второй раз диапазон вписать. Поправил, формула работает, только выдает почему-то 0. Буду "курить" ещё
 
Ответить
Сообщение
по фотографии не лечим)

долго читал справку по ошибке, тыкал, разобрался, в месте: *(ПОИСКПОЗ($B$3:$B$16;$B$3:$B$16;) забыл второй раз диапазон вписать. Поправил, формула работает, только выдает почему-то 0. Буду "курить" ещё

Автор - matigovas
Дата добавления - 30.10.2017 в 12:21
vikttur Дата: Понедельник, 30.10.2017, 12:44 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2511
Репутация: 452 ±
Замечаний: 0% ±

Ошибочна. Пример: код в двух строках. В столбце с фамилией 0 и 1 (т.е. в первой строке с кодом 0). Единица игнорируется


Сообщение отредактировал vikttur - Понедельник, 30.10.2017, 12:46
 
Ответить
СообщениеОшибочна. Пример: код в двух строках. В столбце с фамилией 0 и 1 (т.е. в первой строке с кодом 0). Единица игнорируется

Автор - vikttur
Дата добавления - 30.10.2017 в 12:44
matigovas Дата: Понедельник, 30.10.2017, 12:52 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Единица игнорируется

есть подозрение, что игнорируется, вот файл обезличенный, положение ячеек исходное, разве что справа мини-табличка чуть сдвинется

Поправьте меня, если я не прав: в исходном файле формула выглядит как =(СТРОКА($B$3:$B$16)-2))), грубо говоря номер строки с которой начинаются данные 3й и из него мы вычитаем 2, чтобы получилась 1, значит в файле, который я приложил, тут кусок формулы должен выглядеть как =(СТРОКА($B$16:$B$20)-15)))

Только вопрос открытый: массив не работает в тех ячейках где не заполнены данные, а у меня табличка постоянно обновляется, как тут можно выйти из данной ситуации?
К сообщению приложен файл: 9601953.xls(80Kb)


Сообщение отредактировал matigovas - Понедельник, 30.10.2017, 14:33
 
Ответить
Сообщение
Единица игнорируется

есть подозрение, что игнорируется, вот файл обезличенный, положение ячеек исходное, разве что справа мини-табличка чуть сдвинется

Поправьте меня, если я не прав: в исходном файле формула выглядит как =(СТРОКА($B$3:$B$16)-2))), грубо говоря номер строки с которой начинаются данные 3й и из него мы вычитаем 2, чтобы получилась 1, значит в файле, который я приложил, тут кусок формулы должен выглядеть как =(СТРОКА($B$16:$B$20)-15)))

Только вопрос открытый: массив не работает в тех ячейках где не заполнены данные, а у меня табличка постоянно обновляется, как тут можно выйти из данной ситуации?

Автор - matigovas
Дата добавления - 30.10.2017 в 12:52
sboy Дата: Понедельник, 30.10.2017, 14:27 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1103
Репутация: 276 ±
Замечаний: 0% ±

Excel 2010
Ошибочна

согласен, не подумал об этом.
для нового примера можно так (формула массива)
Код
=ОКРУГЛВВЕРХ(СУММ(ЕСЛИ(СЧЁТЕСЛИ($H$16:$H$30;$H$16:$H$30)>1;J16:J30/СЧЁТЕСЛИ($H$16:$H$30;$H$16:$H$30);J16:J30)*ЕЧИСЛО($H$16:$H$30));)
К сообщению приложен файл: 8022815.xls(83Kb)


Сообщение отредактировал sboy - Понедельник, 30.10.2017, 14:27
 
Ответить
Сообщение
Ошибочна

согласен, не подумал об этом.
для нового примера можно так (формула массива)
Код
=ОКРУГЛВВЕРХ(СУММ(ЕСЛИ(СЧЁТЕСЛИ($H$16:$H$30;$H$16:$H$30)>1;J16:J30/СЧЁТЕСЛИ($H$16:$H$30;$H$16:$H$30);J16:J30)*ЕЧИСЛО($H$16:$H$30));)

Автор - sboy
Дата добавления - 30.10.2017 в 14:27
matigovas Дата: Понедельник, 30.10.2017, 14:43 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Если добавить строку с данными, то массив не пересчитывается автоматически, надо постоянно жать ctrl+shift+enter
 
Ответить
СообщениеЕсли добавить строку с данными, то массив не пересчитывается автоматически, надо постоянно жать ctrl+shift+enter

Автор - matigovas
Дата добавления - 30.10.2017 в 14:43
sboy Дата: Понедельник, 30.10.2017, 14:49 | Сообщение № 10
Группа: Проверенные
Ранг: Старожил
Сообщений: 1103
Репутация: 276 ±
Замечаний: 0% ±

Excel 2010
тут не подскажу, у меня пересчитывается) У Вас случайно не слетел автопересчет формул?
 
Ответить
Сообщениетут не подскажу, у меня пересчитывается) У Вас случайно не слетел автопересчет формул?

Автор - sboy
Дата добавления - 30.10.2017 в 14:49
krosav4ig Дата: Понедельник, 30.10.2017, 15:26 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1479
Репутация: 599 ±
Замечаний: 0% ±

Excel 2007, 2013
Здравствуйте. Еще вариант формулы для файла из 7 поста
Код
=СЧЁТ(1/ЧАСТОТА(ЕСЛИ(($J14:$AC14=AG14)*$J16:$AC300;$H16:$H300);$H16:$H300))
К сообщению приложен файл: 1794053.xls(81Kb)


(_)Õvõ(_)
 
Ответить
СообщениеЗдравствуйте. Еще вариант формулы для файла из 7 поста
Код
=СЧЁТ(1/ЧАСТОТА(ЕСЛИ(($J14:$AC14=AG14)*$J16:$AC300;$H16:$H300);$H16:$H300))

Автор - krosav4ig
Дата добавления - 30.10.2017 в 15:26
matigovas Дата: Понедельник, 30.10.2017, 16:16 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
тут не подскажу, у меня пересчитывается) У Вас случайно не слетел автопересчет формул?

был локальный глюк, все работает без сбоев, спасибо огромное
 
Ответить
Сообщение
тут не подскажу, у меня пересчитывается) У Вас случайно не слетел автопересчет формул?

был локальный глюк, все работает без сбоев, спасибо огромное

Автор - matigovas
Дата добавления - 30.10.2017 в 16:16
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма данных по условию уникального значения (Формулы/Formulas)
Страница 1 из 11
Поиск:

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