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

Вход

Регистрация

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

 

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

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

Ребята, выручайте нуба. Только начинаю работать с excel, дабы облегчить работу себе и ряду других отделов и службы на работе.
До некоторых моментов допёр сам, некоторые успешно подсмотрел, а тут загвоздка.
Никак не могу найти и прописать адекватную формулу, чтобы подсчитывала крайне геморройную необходимость: количество уникальных лиц, получивших определенные взыскания за указанный месяц
Т.е. из указанного списка нужна выборка, например: количество людей, которые в течение января получили взыскания за исключением "Устного выговора", при этом, если он уже имел в этом месяце подобное взыскание - человек должен засчитываться в итоге всего один раз.
Проще говоря "Количество лиц, допустивших нарушения в определенном месяце (за исключением устного выговора)".
Прекрасно понимаю, что прошу (наверное) многого, но буду безмерно рад любой помощи
К сообщению приложен файл: 5321870.xlsx (25.0 Kb)


Сообщение отредактировал MESSER - Вторник, 30.11.2021, 22:49
 
Ответить
СообщениеРебята, выручайте нуба. Только начинаю работать с excel, дабы облегчить работу себе и ряду других отделов и службы на работе.
До некоторых моментов допёр сам, некоторые успешно подсмотрел, а тут загвоздка.
Никак не могу найти и прописать адекватную формулу, чтобы подсчитывала крайне геморройную необходимость: количество уникальных лиц, получивших определенные взыскания за указанный месяц
Т.е. из указанного списка нужна выборка, например: количество людей, которые в течение января получили взыскания за исключением "Устного выговора", при этом, если он уже имел в этом месяце подобное взыскание - человек должен засчитываться в итоге всего один раз.
Проще говоря "Количество лиц, допустивших нарушения в определенном месяце (за исключением устного выговора)".
Прекрасно понимаю, что прошу (наверное) многого, но буду безмерно рад любой помощи
Автор - MESSER
Дата добавления - 30.11.2021 в 22:48
Pelena Дата: Вторник, 30.11.2021, 23:20 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19177
Репутация: 4417 ±
Замечаний: ±

Excel 365 & Mac Excel
Вариант сводной таблицей с моделью данных
К сообщению приложен файл: 1655182.xlsx (182.6 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВариант сводной таблицей с моделью данных
Автор - Pelena
Дата добавления - 30.11.2021 в 23:20
Светлый Дата: Среда, 01.12.2021, 10:40 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Попробуйте такую формулу массива:
Код
=СУММ(ЕСЛИОШИБКА(1/МУМНОЖ(($B2:$B180&$C2:$C180&$D2:$D180=ТРАНСП($B2:$B180&$C2:$C180&$D2:$D180))*($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21")));))
*Или немного иначе:
Код
=СЧЁТ($A2:$A180/(ПОИСКПОЗ(ЕСЛИ(($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));$B2:$B180&$C2:$C180&$D2:$D180);ЕСЛИ(($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));$B2:$B180&$C2:$C180&$D2:$D180;);)=$A2:$A180))
**Вот так правильнее:
Код
=СЧЁТ($A2:$A180/(ПОИСКПОЗ(ЕСЛИ(($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));СЖПРОБЕЛЫ($B2:$B180&" "&$C2:$C180&" "&$D2:$D180));ЕСЛИ(($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));СЖПРОБЕЛЫ($B2:$B180&" "&$C2:$C180&" "&$D2:$D180););)=$A2:$A180))
Файл перезалил.
К сообщению приложен файл: 5321870-1.xlsx (33.3 Kb)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 01.12.2021, 11:58
 
Ответить
СообщениеПопробуйте такую формулу массива:
Код
=СУММ(ЕСЛИОШИБКА(1/МУМНОЖ(($B2:$B180&$C2:$C180&$D2:$D180=ТРАНСП($B2:$B180&$C2:$C180&$D2:$D180))*($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21")));))
*Или немного иначе:
Код
=СЧЁТ($A2:$A180/(ПОИСКПОЗ(ЕСЛИ(($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));$B2:$B180&$C2:$C180&$D2:$D180);ЕСЛИ(($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));$B2:$B180&$C2:$C180&$D2:$D180;);)=$A2:$A180))
**Вот так правильнее:
Код
=СЧЁТ($A2:$A180/(ПОИСКПОЗ(ЕСЛИ(($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));СЖПРОБЕЛЫ($B2:$B180&" "&$C2:$C180&" "&$D2:$D180));ЕСЛИ(($G2:$G180<>"Устный выговор")*($F2:$F180>=--("1."&СТОЛБЕЦ(A4)&".21"))*($F2:$F180<--("1."&СТОЛБЕЦ(B4)&".21"));СЖПРОБЕЛЫ($B2:$B180&" "&$C2:$C180&" "&$D2:$D180););)=$A2:$A180))
Файл перезалил.
Автор - Светлый
Дата добавления - 01.12.2021 в 10:40
китин Дата: Среда, 01.12.2021, 11:32 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
PQ всемогущий
К сообщению приложен файл: 5321870-1-.xlsx (36.5 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеPQ всемогущий
Автор - китин
Дата добавления - 01.12.2021 в 11:32
jakim Дата: Среда, 01.12.2021, 19:20 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Тоже PQ, но код немного короче

[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Список_нарушений"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Номер рапорта", Int64.Type}, {"Фамилия", type text}, {"Имя", type text}, {"Отчество", type text}, {"Дата нарушения", type datetime}, {"Дата объявления", type datetime}, {"Вид взыскания", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Вид взыскания] <> "Устный выговор")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Фамилия", "Имя", "Отчество", "Дата нарушения", "Вид взыскания"}),
    #"Extracted Month Name" = Table.TransformColumns(#"Removed Columns", {{"Дата объявления", each Date.MonthName(_), type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Extracted Month Name", each ([Дата объявления] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Дата объявления"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each ([Дата объявления] <> "Апрель")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows2"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"
[/vba]
К сообщению приложен файл: 5321870-PQ.xlsx (30.4 Kb)
 
Ответить
Сообщение
Тоже PQ, но код немного короче

[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Список_нарушений"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Номер рапорта", Int64.Type}, {"Фамилия", type text}, {"Имя", type text}, {"Отчество", type text}, {"Дата нарушения", type datetime}, {"Дата объявления", type datetime}, {"Вид взыскания", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Вид взыскания] <> "Устный выговор")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Фамилия", "Имя", "Отчество", "Дата нарушения", "Вид взыскания"}),
    #"Extracted Month Name" = Table.TransformColumns(#"Removed Columns", {{"Дата объявления", each Date.MonthName(_), type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Extracted Month Name", each ([Дата объявления] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Дата объявления"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each ([Дата объявления] <> "Апрель")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows2"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"
[/vba]
Автор - jakim
Дата добавления - 01.12.2021 в 19:20
MESSER Дата: Среда, 01.12.2021, 20:48 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Цитата Pelena, 30.11.2021 в 23:20, в сообщении № 2 ()
Вариант сводной таблицей с моделью данных
К сообщению приложен файл: 1655182.xlsx(182.6 Kb)

Спасибо всем за оперативность.
Но если смотреть в плане правильности итоговых цифр, то это самый правильный вариант из предложенных.
Есть ли возможность это упаковать в графы, которые на основном листе таблицы через формулу?


Сообщение отредактировал MESSER - Среда, 01.12.2021, 20:48
 
Ответить
Сообщение
Цитата Pelena, 30.11.2021 в 23:20, в сообщении № 2 ()
Вариант сводной таблицей с моделью данных
К сообщению приложен файл: 1655182.xlsx(182.6 Kb)

Спасибо всем за оперативность.
Но если смотреть в плане правильности итоговых цифр, то это самый правильный вариант из предложенных.
Есть ли возможность это упаковать в графы, которые на основном листе таблицы через формулу?
Автор - MESSER
Дата добавления - 01.12.2021 в 20:48
прохожий2019 Дата: Среда, 01.12.2021, 21:20 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1242
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
Цитата MESSER, 01.12.2021 в 20:48, в сообщении № 6 ()
Но если смотреть в плане правильности
...то PQ тоже нужно пользоваться с умом :)
[vba]
Код
let
    from = Excel.CurrentWorkbook(){[Name="Список_нарушений"]}[Content],
    filtr = Table.SelectRows(from,each [Вид взыскания]<>"Устный выговор"),
    mnth = Table.TransformColumns(filtr, {"Дата нарушения", Date.MonthName}),
    to = Table.Group(mnth, {"Дата нарушения"}, {"Количество", each List.Count(List.Distinct([Фамилия]))})
in
    to
[/vba]
а на формулах, например, так:
Код
=СЧЁТЗ(УНИК(ФИЛЬТР(Список_нарушений[Фамилия];(ТЕКСТ(Список_нарушений[Дата нарушения];"ММММ")=N3)*(Список_нарушений[Вид взыскания]<>"Устный выговор"))))
К сообщению приложен файл: 1452699-1-1-.xlsx (32.1 Kb)


Сообщение отредактировал прохожий2019 - Среда, 01.12.2021, 22:16
 
Ответить
Сообщение
Цитата MESSER, 01.12.2021 в 20:48, в сообщении № 6 ()
Но если смотреть в плане правильности
...то PQ тоже нужно пользоваться с умом :)
[vba]
Код
let
    from = Excel.CurrentWorkbook(){[Name="Список_нарушений"]}[Content],
    filtr = Table.SelectRows(from,each [Вид взыскания]<>"Устный выговор"),
    mnth = Table.TransformColumns(filtr, {"Дата нарушения", Date.MonthName}),
    to = Table.Group(mnth, {"Дата нарушения"}, {"Количество", each List.Count(List.Distinct([Фамилия]))})
in
    to
[/vba]
а на формулах, например, так:
Код
=СЧЁТЗ(УНИК(ФИЛЬТР(Список_нарушений[Фамилия];(ТЕКСТ(Список_нарушений[Дата нарушения];"ММММ")=N3)*(Список_нарушений[Вид взыскания]<>"Устный выговор"))))
Автор - прохожий2019
Дата добавления - 01.12.2021 в 21:20
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка уникальных текстовых записей за период времени (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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