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

Вход

Регистрация

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

 

= Мир MS Excel/подсчёт только уникальных строк по условию - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » подсчёт только уникальных строк по условию (Формулы/Formulas)
подсчёт только уникальных строк по условию
Altersolver Дата: Вторник, 05.06.2018, 14:37 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Коллеги, добрый день!
Немного сломал голову над следующей задачей.

Есть простая таблица с учётом приходов от клиентов.
Иногда от одного клиента бывает несколько приходов.
Тем не менее, сколько бы ни было приходов от одного клиента - мы считаем их как 1 уникальный приход.

Например:
Май: Петя - 5000
Май: Вася - 3000
Май: Валера - 3000
Май: Петя - 3000

В конце таблицы я делаю некоторые итоги по приходам, пользуюсь СЧЁТЕСЛИМН, чтобы посчитать количество приходов за нужный месяц, по нужному человеку, и по нужному диапазону сумм.
Возникает проблема, когда я хочу посчитать количество УНИКАЛЬНЫХ приходов по конкретному человеку.
в Гугл Таблицах есть классная штука, как COUNTUNIQUE. Однако я не могу задать условие COUNTUNIQUE IF...

Какое решение мы можем использовать, чтобы я мог сделать подсчёт уникальных приходов по месяцу в разрезе нужных мне людей?
COUNTUNIQUEIF - было бы идеально :)
 
Ответить
СообщениеКоллеги, добрый день!
Немного сломал голову над следующей задачей.

Есть простая таблица с учётом приходов от клиентов.
Иногда от одного клиента бывает несколько приходов.
Тем не менее, сколько бы ни было приходов от одного клиента - мы считаем их как 1 уникальный приход.

Например:
Май: Петя - 5000
Май: Вася - 3000
Май: Валера - 3000
Май: Петя - 3000

В конце таблицы я делаю некоторые итоги по приходам, пользуюсь СЧЁТЕСЛИМН, чтобы посчитать количество приходов за нужный месяц, по нужному человеку, и по нужному диапазону сумм.
Возникает проблема, когда я хочу посчитать количество УНИКАЛЬНЫХ приходов по конкретному человеку.
в Гугл Таблицах есть классная штука, как COUNTUNIQUE. Однако я не могу задать условие COUNTUNIQUE IF...

Какое решение мы можем использовать, чтобы я мог сделать подсчёт уникальных приходов по месяцу в разрезе нужных мне людей?
COUNTUNIQUEIF - было бы идеально :)

Автор - Altersolver
Дата добавления - 05.06.2018 в 14:37
китин Дата: Вторник, 05.06.2018, 14:40 | Сообщение № 2
Группа: Модераторы
Ранг: Участник клуба
Сообщений: 5071
Репутация: 807 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
Есть простая таблица

повезло. у нас нет таковой


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
Есть простая таблица

повезло. у нас нет таковой

Автор - китин
Дата добавления - 05.06.2018 в 14:40
Altersolver Дата: Вторник, 05.06.2018, 14:45 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Как это, всё есть)
К сообщению приложен файл: 1660325.xlsx(8.8 Kb)
 
Ответить
СообщениеКак это, всё есть)

Автор - Altersolver
Дата добавления - 05.06.2018 в 14:45
sboy Дата: Вторник, 05.06.2018, 15:17 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2105
Репутация: 604 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Как-то не совсем пример корректен.
У Вас уже уникальные слева выведены, нужно просто проверить был ли он в мае?
формула соответственно будет
Код
=--(B10>0)

но я не думаю, что Вы именно этого хотели :)
К сообщению приложен файл: 9359460.xlsx(8.8 Kb)
 
Ответить
СообщениеДобрый день.
Как-то не совсем пример корректен.
У Вас уже уникальные слева выведены, нужно просто проверить был ли он в мае?
формула соответственно будет
Код
=--(B10>0)

но я не думаю, что Вы именно этого хотели :)

Автор - sboy
Дата добавления - 05.06.2018 в 15:17
_Boroda_ Дата: Вторник, 05.06.2018, 15:26 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13187
Репутация: 5422 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если у клиента в месяц был хоть один приход, то кол-во уникальных равно 1, если не было, то 0
Код
=ЕСЛИ(B10;1;)
или как у Сергея выше
Если же очень хочется отдельную формулу, то
Код
=СЧЁТ(1/(ПОИСКПОЗ(A10&"Май";B$2:B$6&A$2:A$6;)=СТРОКА(A1:A5)))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
К сообщению приложен файл: 1660325_1.xlsx(9.2 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли у клиента в месяц был хоть один приход, то кол-во уникальных равно 1, если не было, то 0
Код
=ЕСЛИ(B10;1;)
или как у Сергея выше
Если же очень хочется отдельную формулу, то
Код
=СЧЁТ(1/(ПОИСКПОЗ(A10&"Май";B$2:B$6&A$2:A$6;)=СТРОКА(A1:A5)))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

Автор - _Boroda_
Дата добавления - 05.06.2018 в 15:26
Altersolver Дата: Вторник, 05.06.2018, 15:45 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Добрый день.
Как-то не совсем пример корректен.
У Вас уже уникальные слева выведены, нужно просто проверить был ли он в мае?
формула соответственно будет


Дело в малом примере.
У нас 168 сделок в месяц.
Из них есть где-то 30 повторных (по одному и тому же клиенту).
Общую цифру без дублей, за счёт COUNTUNIQUE я посчитать смогу. Но в разрезе менеджера - не могу.
Т.е. задача посчитать автоматом количество УНИКАЛЬНЫХ сделок по менеджерам.
 
Ответить
Сообщение
Добрый день.
Как-то не совсем пример корректен.
У Вас уже уникальные слева выведены, нужно просто проверить был ли он в мае?
формула соответственно будет


Дело в малом примере.
У нас 168 сделок в месяц.
Из них есть где-то 30 повторных (по одному и тому же клиенту).
Общую цифру без дублей, за счёт COUNTUNIQUE я посчитать смогу. Но в разрезе менеджера - не могу.
Т.е. задача посчитать автоматом количество УНИКАЛЬНЫХ сделок по менеджерам.

Автор - Altersolver
Дата добавления - 05.06.2018 в 15:45
sboy Дата: Вторник, 05.06.2018, 15:48 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2105
Репутация: 604 ±
Замечаний: 0% ±

Excel 2010
Ну тогда и сделайте корректный пример где есть клиент-менеджер-сделка-период
 
Ответить
СообщениеНу тогда и сделайте корректный пример где есть клиент-менеджер-сделка-период

Автор - sboy
Дата добавления - 05.06.2018 в 15:48
Altersolver Дата: Вторник, 05.06.2018, 15:56 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Хорошо. Ещё раз.
К сообщению приложен файл: 3304288.xlsx(9.2 Kb)
 
Ответить
СообщениеХорошо. Ещё раз.

Автор - Altersolver
Дата добавления - 05.06.2018 в 15:56
_Boroda_ Дата: Вторник, 05.06.2018, 16:06 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13187
Репутация: 5422 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СЧЁТ(1/(ПОИСКПОЗ(A28&"Май"&B$2:B$19;C$2:C$19&A$2:A$19&B$2:B$19;)=СТРОКА(A$1:A$18)))
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
К сообщению приложен файл: 3304288_1.xlsx(9.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СЧЁТ(1/(ПОИСКПОЗ(A28&"Май"&B$2:B$19;C$2:C$19&A$2:A$19&B$2:B$19;)=СТРОКА(A$1:A$18)))
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

Автор - _Boroda_
Дата добавления - 05.06.2018 в 16:06
Altersolver Дата: Вторник, 05.06.2018, 16:37 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Так нужно?

Похоже на правду.
Осталось только всосать суть построения о_О
На своей практике до таких вариантов не доходил умом...
Сможете кратко логику по этапам вычисления словами объяснить?
 
Ответить
Сообщение
Так нужно?

Похоже на правду.
Осталось только всосать суть построения о_О
На своей практике до таких вариантов не доходил умом...
Сможете кратко логику по этапам вычисления словами объяснить?

Автор - Altersolver
Дата добавления - 05.06.2018 в 16:37
_Boroda_ Дата: Вторник, 05.06.2018, 16:49 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13187
Репутация: 5422 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. A28&"Май"&B$2:B$19 - склеиваем менеджера, Май и все имена
2. C$2:C$19&A$2:A$19&B$2:B$1 - склеиваем всех менеджеров, все периоды и все имена
3. ПОИСКПОЗ(A28&"Май"&B$2:B$19;C$2:C$19&A$2:A$19&B$2:B$19;) - позицию ПЕРВОГО вхождения п.1 в п.2. Получаем что-то типа {1:7:1:4:7:1:7:1:4:7:1:7:1:4:7:#Н/Д:#Н/Д:#Н/Д}
4. СТРОКА(A$1:A$18) - дает нам номера строк 1, 2, 3, ... , 18
5. Приравниваем п.3 к п.4, получаем массив из ИСТИНА (где в п.3 найдено действительно первое вхождение) и ЛОЖЬ или ошибку (в остальных случаях)
6. 1/п.5 даст массив из единиц (там, где в п.5 ИСТИНА) и ошибок в остальных случаях (на 0 делить нельзя)
7. СЧЁТ(п.6) - подсчитывает количество чисел в диапазоне из п.6, игнорируя ошибки


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. A28&"Май"&B$2:B$19 - склеиваем менеджера, Май и все имена
2. C$2:C$19&A$2:A$19&B$2:B$1 - склеиваем всех менеджеров, все периоды и все имена
3. ПОИСКПОЗ(A28&"Май"&B$2:B$19;C$2:C$19&A$2:A$19&B$2:B$19;) - позицию ПЕРВОГО вхождения п.1 в п.2. Получаем что-то типа {1:7:1:4:7:1:7:1:4:7:1:7:1:4:7:#Н/Д:#Н/Д:#Н/Д}
4. СТРОКА(A$1:A$18) - дает нам номера строк 1, 2, 3, ... , 18
5. Приравниваем п.3 к п.4, получаем массив из ИСТИНА (где в п.3 найдено действительно первое вхождение) и ЛОЖЬ или ошибку (в остальных случаях)
6. 1/п.5 даст массив из единиц (там, где в п.5 ИСТИНА) и ошибок в остальных случаях (на 0 делить нельзя)
7. СЧЁТ(п.6) - подсчитывает количество чисел в диапазоне из п.6, игнорируя ошибки

Автор - _Boroda_
Дата добавления - 05.06.2018 в 16:49
Altersolver Дата: Вторник, 05.06.2018, 17:33 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Оструеть... Кем надо было работать, чтобы до этой логики дойти O.o
Спасибо!
Буду пробовать составить теперь это самому.
 
Ответить
СообщениеОструеть... Кем надо было работать, чтобы до этой логики дойти O.o
Спасибо!
Буду пробовать составить теперь это самому.

Автор - Altersolver
Дата добавления - 05.06.2018 в 17:33
Altersolver Дата: Вторник, 05.06.2018, 18:05 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Но, увы, спроецировать на свою реальную таблицу не получилось по аналогии...
 
Ответить
СообщениеНо, увы, спроецировать на свою реальную таблицу не получилось по аналогии...

Автор - Altersolver
Дата добавления - 05.06.2018 в 18:05
Мир MS Excel » Вопросы и решения » Вопросы по Excel » подсчёт только уникальных строк по условию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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