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

Вход

Регистрация

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

 

= Мир MS Excel/Вывод повторяющихся данных из списка - Мир MS Excel

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

Excel 2021
Добрый день, скажите, есть ли способ сделать формулами отборку повторяющих данных из списка?
А именно, должны выводиться все значения из списка в ст. В, которых 2 и больше, причем вывестись они должны в одном экземпляре.
Во вложенном примере я показал рядом вывод сводной таблицей, выделил зеленой заливкой, что должно вывестись.

В гугл-таблицах мне удалось реализовать при помощи функций UNIQUE и FILTER, но в эксель 2016 такого нет. А гугл-таблицы не тянут, там огромный массив данных, думал такое же соорудить в эксель.
К сообщению приложен файл: 7020337.xlsx (17.9 Kb)


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
СообщениеДобрый день, скажите, есть ли способ сделать формулами отборку повторяющих данных из списка?
А именно, должны выводиться все значения из списка в ст. В, которых 2 и больше, причем вывестись они должны в одном экземпляре.
Во вложенном примере я показал рядом вывод сводной таблицей, выделил зеленой заливкой, что должно вывестись.

В гугл-таблицах мне удалось реализовать при помощи функций UNIQUE и FILTER, но в эксель 2016 такого нет. А гугл-таблицы не тянут, там огромный массив данных, думал такое же соорудить в эксель.

Автор - Shtein
Дата добавления - 24.03.2021 в 15:13
Nic70y Дата: Среда, 24.03.2021, 16:22 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Фильтры по значению -- не равно -- 1


ЮMoney 41001841029809
 
Ответить
СообщениеФильтры по значению -- не равно -- 1

Автор - Nic70y
Дата добавления - 24.03.2021 в 16:22
Shtein Дата: Среда, 24.03.2021, 16:34 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
надо вывод результата на отдельный лист, чтобы постоянно обновлялось, в зависимости от внесенных данных в базе, это разве можно сделать фильтрами? Тут, видимо, надо макросами решать


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
Сообщениенадо вывод результата на отдельный лист, чтобы постоянно обновлялось, в зависимости от внесенных данных в базе, это разве можно сделать фильтрами? Тут, видимо, надо макросами решать

Автор - Shtein
Дата добавления - 24.03.2021 в 16:34
Serge_007 Дата: Среда, 24.03.2021, 16:48 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
Здравствуйте

надо вывод результата на отдельный лист, чтобы постоянно обновлялось, в зависимости от внесенных данных в базе
У Вас же сводная есть в файле. Вот её на отдельный лист, фильтр по значению "Больше 1" и обновляйте макросом, например при Activate листа со сводной


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЗдравствуйте

надо вывод результата на отдельный лист, чтобы постоянно обновлялось, в зависимости от внесенных данных в базе
У Вас же сводная есть в файле. Вот её на отдельный лист, фильтр по значению "Больше 1" и обновляйте макросом, например при Activate листа со сводной

Автор - Serge_007
Дата добавления - 24.03.2021 в 16:48
Nic70y Дата: Среда, 24.03.2021, 16:52 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
[vba]
Код
Sub u_16()
    Application.ScreenUpdating = False
    u_1 = Sheets("Лист2").Cells(Rows.Count, "a").End(xlUp).Row
    Sheets("Лист2").Range("a1:a" & u_1).Clear
    u_2 = Sheets("Лист1").Cells(Rows.Count, "a").End(xlUp).Row
    For Each u_3 In Sheets("Лист1").Range("b2:b" & u_2)
        u_4 = u_3.Row
        u_5 = Application.Match(u_3, Sheets("Лист1").Range("b1:b" & u_4 - 1), 0)
        u_6 = Application.Match(u_3, Sheets("Лист1").Range("b" & u_4 + 1 & ":b" & u_2 + 1), 0)
        v_5 = Application.IsNumber(u_5)
        v_6 = Application.IsNumber(u_6)
        If v_5 = False And v_6 Then
            u_7 = Sheets("Лист2").Cells(Rows.Count, "a").End(xlUp).Row + 1
            Sheets("Лист2").Range("a" & u_7) = u_3
        End If
    Next
    Application.ScreenUpdating = True
End Sub
[/vba]
К сообщению приложен файл: u_700.xlsm (23.0 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщение[vba]
Код
Sub u_16()
    Application.ScreenUpdating = False
    u_1 = Sheets("Лист2").Cells(Rows.Count, "a").End(xlUp).Row
    Sheets("Лист2").Range("a1:a" & u_1).Clear
    u_2 = Sheets("Лист1").Cells(Rows.Count, "a").End(xlUp).Row
    For Each u_3 In Sheets("Лист1").Range("b2:b" & u_2)
        u_4 = u_3.Row
        u_5 = Application.Match(u_3, Sheets("Лист1").Range("b1:b" & u_4 - 1), 0)
        u_6 = Application.Match(u_3, Sheets("Лист1").Range("b" & u_4 + 1 & ":b" & u_2 + 1), 0)
        v_5 = Application.IsNumber(u_5)
        v_6 = Application.IsNumber(u_6)
        If v_5 = False And v_6 Then
            u_7 = Sheets("Лист2").Cells(Rows.Count, "a").End(xlUp).Row + 1
            Sheets("Лист2").Range("a" & u_7) = u_3
        End If
    Next
    Application.ScreenUpdating = True
End Sub
[/vba]

Автор - Nic70y
Дата добавления - 24.03.2021 в 16:52
Egyptian Дата: Среда, 24.03.2021, 17:01 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Формулой.
Код
=IFERROR(INDEX($B$2:$B$24,MATCH(1,INDEX((COUNTIF($D$1:D1,$B$2:$B$24)=0)*(COUNTIF($B$2:$B$24,$B$2:$B$24)>=2),0),0)),"")
К сообщению приложен файл: 4770368.xlsx (13.3 Kb)


Сообщение отредактировал Egyptian - Среда, 24.03.2021, 17:04
 
Ответить
СообщениеФормулой.
Код
=IFERROR(INDEX($B$2:$B$24,MATCH(1,INDEX((COUNTIF($D$1:D1,$B$2:$B$24)=0)*(COUNTIF($B$2:$B$24,$B$2:$B$24)>=2),0),0)),"")

Автор - Egyptian
Дата добавления - 24.03.2021 в 17:01
Shtein Дата: Среда, 24.03.2021, 17:12 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
Egyptian, ух, мощно.. но, работает)) как же в гугл-таблицах это можно сделать проще. Там, правда, свои недостатки, при работе со сводными таблицами, условным форматированием


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
СообщениеEgyptian, ух, мощно.. но, работает)) как же в гугл-таблицах это можно сделать проще. Там, правда, свои недостатки, при работе со сводными таблицами, условным форматированием

Автор - Shtein
Дата добавления - 24.03.2021 в 17:12
прохожий2019 Дата: Среда, 24.03.2021, 17:12 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
Код
=СОРТ(ФИЛЬТР(ВЫБОР({1;2};УНИК(B2:B23);СЧЁТЕСЛИ(B2:B23;УНИК(B2:B23)));СЧЁТЕСЛИ(B2:B23;УНИК(B2:B23))>1);2;-1)
Код
=LET(x;УНИК(B2:B23);LET(y;СЧЁТЕСЛИ(B2:B23;x);СОРТ(ФИЛЬТР(ВЫБОР({1;2};x;y);y>1);2;-1)))
К сообщению приложен файл: 2954891.xlsx (14.4 Kb)
 
Ответить
Сообщение
Код
=СОРТ(ФИЛЬТР(ВЫБОР({1;2};УНИК(B2:B23);СЧЁТЕСЛИ(B2:B23;УНИК(B2:B23)));СЧЁТЕСЛИ(B2:B23;УНИК(B2:B23))>1);2;-1)
Код
=LET(x;УНИК(B2:B23);LET(y;СЧЁТЕСЛИ(B2:B23;x);СОРТ(ФИЛЬТР(ВЫБОР({1;2};x;y);y>1);2;-1)))

Автор - прохожий2019
Дата добавления - 24.03.2021 в 17:12
Shtein Дата: Среда, 24.03.2021, 17:22 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
прохожий2019, спасибо, тут есть функции из 365 эксель))


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
Сообщениепрохожий2019, спасибо, тут есть функции из 365 эксель))

Автор - Shtein
Дата добавления - 24.03.2021 в 17:22
прохожий2019 Дата: Среда, 24.03.2021, 21:47 | Сообщение № 10
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
есть функции из 365 эксель
ну да, он продвинулся немножко в сравнении с 2016... :)
в гугле вообще можно одной функцией
Код
=QUERY(QUERY(A1:A22;"select A, count(A)  group by A order by count(A) desc ");"where Col2>1")

зато в 2016 Excel уже есть PQ:[vba]
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[Бренд]],
    group = Table.Group(from, "Бренд", {"Количество", each Table.RowCount(_)}),
    sort = Table.Sort(group,{"Количество", Order.Descending}),
    to = Table.SelectRows(sort, each [Количество] > 1)
in
    to
[/vba]
К сообщению приложен файл: 4699023.xlsx (22.7 Kb)


Сообщение отредактировал прохожий2019 - Среда, 24.03.2021, 22:48
 
Ответить
Сообщение
есть функции из 365 эксель
ну да, он продвинулся немножко в сравнении с 2016... :)
в гугле вообще можно одной функцией
Код
=QUERY(QUERY(A1:A22;"select A, count(A)  group by A order by count(A) desc ");"where Col2>1")

зато в 2016 Excel уже есть PQ:[vba]
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[Бренд]],
    group = Table.Group(from, "Бренд", {"Количество", each Table.RowCount(_)}),
    sort = Table.Sort(group,{"Количество", Order.Descending}),
    to = Table.SelectRows(sort, each [Количество] > 1)
in
    to
[/vba]

Автор - прохожий2019
Дата добавления - 24.03.2021 в 21:47
Shtein Дата: Четверг, 25.03.2021, 08:54 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
я в гугл таблицах так делал))
Код
=unique(sort(FILTER('Сводка'!A5:A; СЧЁТЕСЛИ('Сводка'!A5:A;'Сводка'!A5:A)>=2);2;ИСТИНА;3;ИСТИНА))

спасибо за ваши способы yes
PQ, да есть, ну технически его можно было и в более ранних версиях доустановить
Я предполагаю, что это язык М, скрипт который?


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.


Сообщение отредактировал Shtein - Четверг, 25.03.2021, 09:19
 
Ответить
Сообщениея в гугл таблицах так делал))
Код
=unique(sort(FILTER('Сводка'!A5:A; СЧЁТЕСЛИ('Сводка'!A5:A;'Сводка'!A5:A)>=2);2;ИСТИНА;3;ИСТИНА))

спасибо за ваши способы yes
PQ, да есть, ну технически его можно было и в более ранних версиях доустановить
Я предполагаю, что это язык М, скрипт который?

Автор - Shtein
Дата добавления - 25.03.2021 в 08:54
прохожий2019 Дата: Четверг, 25.03.2021, 09:44 | Сообщение № 12
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
это язык М, скрипт который
он самый, но конкретно такой можно не писать, а наклацать кнопочками
 
Ответить
Сообщение
это язык М, скрипт который
он самый, но конкретно такой можно не писать, а наклацать кнопочками

Автор - прохожий2019
Дата добавления - 25.03.2021 в 09:44
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вывод повторяющихся данных из списка (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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