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

Вход

Регистрация

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

 

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

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

Excel 2016
Здравствуйте. Помогите пожалуйста дописать формулу. Здесь ведется подсчет уникальных значений по столбцу A, но так же есть несколько критериев по каким подсчитывается. Требуется:
1. Добавить еще один критерий :
Код
=ЕСЛИ(I2:I18=I2;


2. Как сделать чтоб это все считалось на весь столбец а не диапазон, если вдруг я буду вносить новые данные в таблицу. В таблице есть формула.
Сама формула:

Код
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(A2:A25<>"";ЕСЛИ(D2:D25>=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1);ЕСЛИ(K2:K25=K2;ПОИСКПОЗ(A2:A25;A2:A25;0))));СТРОКА(K2:K25)-СТРОКА(K2)+1);1))


Помогите в решении проблемы. Заранее всех благодарю, кто откликается.
К сообщению приложен файл: 2_5219959082391.xlsx (12.5 Kb)


Сообщение отредактировал Mrzod - Суббота, 28.09.2019, 15:25
 
Ответить
СообщениеЗдравствуйте. Помогите пожалуйста дописать формулу. Здесь ведется подсчет уникальных значений по столбцу A, но так же есть несколько критериев по каким подсчитывается. Требуется:
1. Добавить еще один критерий :
Код
=ЕСЛИ(I2:I18=I2;


2. Как сделать чтоб это все считалось на весь столбец а не диапазон, если вдруг я буду вносить новые данные в таблицу. В таблице есть формула.
Сама формула:

Код
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(A2:A25<>"";ЕСЛИ(D2:D25>=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1);ЕСЛИ(K2:K25=K2;ПОИСКПОЗ(A2:A25;A2:A25;0))));СТРОКА(K2:K25)-СТРОКА(K2)+1);1))


Помогите в решении проблемы. Заранее всех благодарю, кто откликается.
Автор - Mrzod
Дата добавления - 28.09.2019 в 15:23
Pelena Дата: Суббота, 28.09.2019, 23:41 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19177
Репутация: 4417 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Не уверена, что правильно поняла. Формула массива
Код
=СУММ(ЧАСТОТА(ЕСЛИ((I2:I500=I2)*(A2:A500<>"")*(D2:D500>=КОНМЕСЯЦА(СЕГОДНЯ();-1)+1)*(K2:K500=K2);ПОИСКПОЗ(A2:A500;A2:A500;0));СТРОКА(K2:K500)-СТРОКА(K2)+1))
К сообщению приложен файл: 2275734.xlsx (12.8 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Не уверена, что правильно поняла. Формула массива
Код
=СУММ(ЧАСТОТА(ЕСЛИ((I2:I500=I2)*(A2:A500<>"")*(D2:D500>=КОНМЕСЯЦА(СЕГОДНЯ();-1)+1)*(K2:K500=K2);ПОИСКПОЗ(A2:A500;A2:A500;0));СТРОКА(K2:K500)-СТРОКА(K2)+1))
Автор - Pelena
Дата добавления - 28.09.2019 в 23:41
Mrzod Дата: Воскресенье, 29.09.2019, 13:55 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо за помощь, но с добавлением условий разобрался. Не понимаю теперь только почему формула не может работать на весь столбец, например если заменю указанный диапазон на весь столбец А:А, так как предполагается, что в таблицу будут добавляться данные и необходимо чтобы они считались. Просто если расширить как вы диапазон проблемы не решит, мне потом нужно будет залазить в формулу и менять его например на больший, но в то же время не хотелось бы в формуле ставить диапазон в таком виде :А2:А10000
 
Ответить
СообщениеСпасибо за помощь, но с добавлением условий разобрался. Не понимаю теперь только почему формула не может работать на весь столбец, например если заменю указанный диапазон на весь столбец А:А, так как предполагается, что в таблицу будут добавляться данные и необходимо чтобы они считались. Просто если расширить как вы диапазон проблемы не решит, мне потом нужно будет залазить в формулу и менять его например на больший, но в то же время не хотелось бы в формуле ставить диапазон в таком виде :А2:А10000
Автор - Mrzod
Дата добавления - 29.09.2019 в 13:55
krosav4ig Дата: Воскресенье, 29.09.2019, 16:32 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
UDF [vba]
Код
Function UsedRng() As Range
    Set UsedRng = Application.Caller.Parent.UsedRange
End Function
[/vba]в диспетчере имен создаем именованный диапазон ВсеСтроки
Код
=UsedRng()
в ячейке формула
Код
=СУММ(ЧАСТОТА(ЕСЛИ((ВсеСтроки I:I=I2)*(ВсеСтроки A:A<>"")*(ВсеСтроки D:D>=КОНМЕСЯЦА(СЕГОДНЯ();-1)+1)*(ВсеСтроки K:K=K2);ПОИСКПОЗ(ВсеСтроки A:A;A:A;0));СТРОКА(ВсеСтроки)-СТРОКА(K2)+1))


или использовать умные таблицы (Лист2)
Код
=СУММ(ЧАСТОТА(ЕСЛИ((Таблица1[Client]=Таблица1[@Client])*(Таблица1[AGR]<>"")*(Таблица1[Дата поступления]>=КОНМЕСЯЦА(СЕГОДНЯ();-1)+1)*(ВсеСтроки Таблица1[результат]=K2);ПОИСКПОЗ(Таблица1[AGR];Таблица1[AGR];0));СТРОКА(Таблица1)-СТРОКА(K2)+1))
К сообщению приложен файл: 6036041.xlsm (23.0 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеUDF [vba]
Код
Function UsedRng() As Range
    Set UsedRng = Application.Caller.Parent.UsedRange
End Function
[/vba]в диспетчере имен создаем именованный диапазон ВсеСтроки
Код
=UsedRng()
в ячейке формула
Код
=СУММ(ЧАСТОТА(ЕСЛИ((ВсеСтроки I:I=I2)*(ВсеСтроки A:A<>"")*(ВсеСтроки D:D>=КОНМЕСЯЦА(СЕГОДНЯ();-1)+1)*(ВсеСтроки K:K=K2);ПОИСКПОЗ(ВсеСтроки A:A;A:A;0));СТРОКА(ВсеСтроки)-СТРОКА(K2)+1))


или использовать умные таблицы (Лист2)
Код
=СУММ(ЧАСТОТА(ЕСЛИ((Таблица1[Client]=Таблица1[@Client])*(Таблица1[AGR]<>"")*(Таблица1[Дата поступления]>=КОНМЕСЯЦА(СЕГОДНЯ();-1)+1)*(ВсеСтроки Таблица1[результат]=K2);ПОИСКПОЗ(Таблица1[AGR];Таблица1[AGR];0));СТРОКА(Таблица1)-СТРОКА(K2)+1))
Автор - krosav4ig
Дата добавления - 29.09.2019 в 16:32
Mrzod Дата: Воскресенье, 29.09.2019, 23:28 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо большое всем за помощь!
 
Ответить
СообщениеСпасибо большое всем за помощь!
Автор - Mrzod
Дата добавления - 29.09.2019 в 23:28
Mrzod Дата: Понедельник, 30.09.2019, 11:42 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Еще подскажите пожалуйста, в чем нюанс, если я хочу формулу применить на Листе 2, почему она не работает. Ошибка #Знач!
Спасибо.
 
Ответить
СообщениеЕще подскажите пожалуйста, в чем нюанс, если я хочу формулу применить на Листе 2, почему она не работает. Ошибка #Знач!
Спасибо.
Автор - Mrzod
Дата добавления - 30.09.2019 в 11:42
китин Дата: Понедельник, 30.09.2019, 12:06 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Цитата Mrzod, 30.09.2019 в 11:42, в сообщении № 6 ()
Ошибка #Знач!
Спасибо.

Цитата Mrzod, 29.09.2019 в 13:55, в сообщении № 3 ()
Формула массива

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

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
Цитата Mrzod, 30.09.2019 в 11:42, в сообщении № 6 ()
Ошибка #Знач!
Спасибо.

Цитата Mrzod, 29.09.2019 в 13:55, в сообщении № 3 ()
Формула массива

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
Автор - китин
Дата добавления - 30.09.2019 в 12:06
Mrzod Дата: Понедельник, 30.09.2019, 12:07 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Прилагаю файл. Формула на листе 2
К сообщению приложен файл: 213-1-.xlsm (19.8 Kb)
 
Ответить
СообщениеПрилагаю файл. Формула на листе 2
Автор - Mrzod
Дата добавления - 30.09.2019 в 12:07
Mrzod Дата: Понедельник, 30.09.2019, 12:09 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата китин, 30.09.2019 в 12:06, в сообщении № 7 ()
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter


Я вроде так и делаю, но не пойму в чем проблема(
 
Ответить
Сообщение
Цитата китин, 30.09.2019 в 12:06, в сообщении № 7 ()
Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter


Я вроде так и делаю, но не пойму в чем проблема(
Автор - Mrzod
Дата добавления - 30.09.2019 в 12:09
китин Дата: Понедельник, 30.09.2019, 12:14 | Сообщение № 10
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
А где в вашем файле лист Всестроки Лист1?????? диапазончики проверьте и названия листов
неправильно

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеА где в вашем файле лист Всестроки Лист1?????? диапазончики проверьте и названия листов
неправильно
Автор - китин
Дата добавления - 30.09.2019 в 12:14
Mrzod Дата: Понедельник, 30.09.2019, 12:22 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата китин, 30.09.2019 в 12:14, в сообщении № 10 ()
А где в вашем файле лист Всестроки Лист1?????? диапазончики проверьте и названия листов

Не совсем понял вас, можно поподробнее %)
Вроде уже все перепробовал.
 
Ответить
Сообщение
Цитата китин, 30.09.2019 в 12:14, в сообщении № 10 ()
А где в вашем файле лист Всестроки Лист1?????? диапазончики проверьте и названия листов

Не совсем понял вас, можно поподробнее %)
Вроде уже все перепробовал.
Автор - Mrzod
Дата добавления - 30.09.2019 в 12:22
китин Дата: Понедельник, 30.09.2019, 12:42 | Сообщение № 12
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
неправильно написал.

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениенеправильно написал.
Автор - китин
Дата добавления - 30.09.2019 в 12:42
Mrzod Дата: Понедельник, 30.09.2019, 12:46 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата китин, 30.09.2019 в 12:42, в сообщении № 12 ()
неправильно написал.

А что именно? :)
Не могли бы в файле показать.
Заранее большое спасибо!
 
Ответить
Сообщение
Цитата китин, 30.09.2019 в 12:42, в сообщении № 12 ()
неправильно написал.

А что именно? :)
Не могли бы в файле показать.
Заранее большое спасибо!
Автор - Mrzod
Дата добавления - 30.09.2019 в 12:46
китин Дата: Понедельник, 30.09.2019, 12:48 | Сообщение № 14
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
я неправильно написал в посте №10

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениея неправильно написал в посте №10
Автор - китин
Дата добавления - 30.09.2019 в 12:48
Mrzod Дата: Понедельник, 30.09.2019, 13:01 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Окей. А мыслей по поводу формулы нет никаких?(
 
Ответить
СообщениеОкей. А мыслей по поводу формулы нет никаких?(
Автор - Mrzod
Дата добавления - 30.09.2019 в 13:01
krosav4ig Дата: Понедельник, 30.09.2019, 17:51 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
заменил UDF
[vba]
Код
Function UsedRng(Optional ByRef r As Range) As Range
    Set UsedRng = IIf(r Is Nothing, Application.Caller, r).Parent.UsedRange
End Function
[/vba]
Определение имени ВсеСтроки заменил на
Код
=UsedRng(Лист1!$A$1)


Ошибка была из-за того, что функция UsedRng возвращала использованный диапазон листа, на котором находится ячейка, из которой эта функция вызывается, т.е. ВсеСтроки - диапазон с Лист2, а столбцы с Лист1, а пробел между диапазонами в формуле - оператор пересечения.
К сообщению приложен файл: 8932097.xlsm (20.1 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Понедельник, 30.09.2019, 17:52
 
Ответить
Сообщениезаменил UDF
[vba]
Код
Function UsedRng(Optional ByRef r As Range) As Range
    Set UsedRng = IIf(r Is Nothing, Application.Caller, r).Parent.UsedRange
End Function
[/vba]
Определение имени ВсеСтроки заменил на
Код
=UsedRng(Лист1!$A$1)


Ошибка была из-за того, что функция UsedRng возвращала использованный диапазон листа, на котором находится ячейка, из которой эта функция вызывается, т.е. ВсеСтроки - диапазон с Лист2, а столбцы с Лист1, а пробел между диапазонами в формуле - оператор пересечения.
Автор - krosav4ig
Дата добавления - 30.09.2019 в 17:51
Mrzod Дата: Понедельник, 30.09.2019, 18:04 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
krosav4ig,
Спасибо вам большое! А за объяснение ошибки отдельное спасибо!И еще вопрос, не сочтите за наглость, почему при добавлении ** сбивается поиск, пишет 0.

Код
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ВсеСтроки Лист1!A:A<>"";ЕСЛИ(ВсеСтроки Лист1!D:D>=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1);ЕСЛИ(ВсеСтроки Лист1!K:K=Лист1!K2;ЕСЛИ(ВсеСтроки Лист1!I:I="*Client 1*";ПОИСКПОЗ(ВсеСтроки Лист1!A:A;Лист1!A:A;0)))));СТРОКА(ВсеСтроки)-СТРОКА(Лист1!K2)+1);1))
]
 
Ответить
Сообщениеkrosav4ig,
Спасибо вам большое! А за объяснение ошибки отдельное спасибо!И еще вопрос, не сочтите за наглость, почему при добавлении ** сбивается поиск, пишет 0.

Код
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ВсеСтроки Лист1!A:A<>"";ЕСЛИ(ВсеСтроки Лист1!D:D>=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1);ЕСЛИ(ВсеСтроки Лист1!K:K=Лист1!K2;ЕСЛИ(ВсеСтроки Лист1!I:I="*Client 1*";ПОИСКПОЗ(ВсеСтроки Лист1!A:A;Лист1!A:A;0)))));СТРОКА(ВсеСтроки)-СТРОКА(Лист1!K2)+1);1))
]
Автор - Mrzod
Дата добавления - 30.09.2019 в 18:04
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет уникальных значений по нескольким критериям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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