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

Вход

Регистрация

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

 

= Мир MS Excel/Извлечение уникальных значений - Мир MS Excel

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

Добрый день
Необходимо разбить данные по ячейкам для каждой строки и их всех полученных ячеек выявить уникальные значения списком.
Помогите, пожалуйста, с задачей.
Заранее спасибо
К сообщению приложен файл: data.xlsx(8.7 Kb)
 
Ответить
СообщениеДобрый день
Необходимо разбить данные по ячейкам для каждой строки и их всех полученных ячеек выявить уникальные значения списком.
Помогите, пожалуйста, с задачей.
Заранее спасибо

Автор - shepenindmitrii
Дата добавления - 23.12.2020 в 20:29
tatianaTP Дата: Среда, 23.12.2020, 20:39 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 5 ±
Замечаний: 0% ±

Excel 2013
уточните, что и где должно получиться в результате на примере одной позиции
К сообщению приложен файл: data01.xlsx(12.2 Kb)
 
Ответить
Сообщениеуточните, что и где должно получиться в результате на примере одной позиции

Автор - tatianaTP
Дата добавления - 23.12.2020 в 20:39
shepenindmitrii Дата: Среда, 23.12.2020, 21:29 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Вот у Вас получилась таблица с разбивкой значений по ячейкам. В данной таблице некоторые значения встречаются неоднократно.

Как сделать список уникальных значений этой таблицы, то есть вывести столбцом / строкой все уникальные значения из данной таблицы (убрать дубликаты)
 
Ответить
СообщениеВот у Вас получилась таблица с разбивкой значений по ячейкам. В данной таблице некоторые значения встречаются неоднократно.

Как сделать список уникальных значений этой таблицы, то есть вывести столбцом / строкой все уникальные значения из данной таблицы (убрать дубликаты)

Автор - shepenindmitrii
Дата добавления - 23.12.2020 в 21:29
Pelena Дата: Среда, 23.12.2020, 22:21 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 17020
Репутация: 3670 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Так подойдёт?
Формулу взяла из этой темы
К сообщению приложен файл: 5192725.xlsx(16.3 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеТак подойдёт?
Формулу взяла из этой темы

Автор - Pelena
Дата добавления - 23.12.2020 в 22:21
shepenindmitrii Дата: Четверг, 24.12.2020, 14:24 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Pelena, я могу Вас попросить, пожалуйста, по шагам объяснить мне данную формулу?
Я на портале недавно и не со всеми формулами знаком и уже час не могу разобраться

Заранее спасибо.
 
Ответить
СообщениеPelena, я могу Вас попросить, пожалуйста, по шагам объяснить мне данную формулу?
Я на портале недавно и не со всеми формулами знаком и уже час не могу разобраться

Заранее спасибо.

Автор - shepenindmitrii
Дата добавления - 24.12.2020 в 14:24
Pelena Дата: Четверг, 24.12.2020, 19:27 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 17020
Репутация: 3670 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Код
=ЕСЛИОШИБКА(ИНДЕКС(A$1:AE$24;МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24);СТРОКА($1:$24));9^9);ОСТАТ(МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24);СТРОКА($1:$24)+СТОЛБЕЦ(A:AE)%%));1)/1%%);"")

Объяснялка:
1) основная функция ИНДЕКС, которая возвращает искомые значения, первым аргументом имеет исследуемый диапазон ИНДЕКС(A$1:AE$24;
2) так как исследуемый диапазон двумерный (прямоугольный), функция ИНДЕКС для правильной работы требует в качестве второго аргумента номер строки, а третьего - номер столбца
3) номер строки вычисляется так МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24);СТРОКА($1:$24));9^9). То есть сначала проверяется, что очередное значение ещё не было выведено в предыдущих ячейках. За это отвечает часть НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24)). Можно было написать по-другому СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24)<>0, возможно, так понятнее.Эта функция даст массив из значений ИСТИНА/ЛОЖЬ. Этот массив можно увидеть, если в строке формул выделить указанный фрагмент и нажать клавишу F9 на клавиатуре. Вторая часть ДЛСТР(A$1:AE$24) выводит длину каждого значения в исследуемом диапазоне, чтобы отсеять пустые ячейки, где длина текста равна 0. Произведение НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24) вернёт массив нулей и чисел. Ноль - значит ячейка пустая или значение уже было выведено раньше, а число (длина текста), если значение ещё не выводилось.
Функция ЕСЛИ возвращает массив номеров строк СТРОКА($1:$24), если значение ещё не выводилось и ЛОЖЬ, если значение уже выведено. Все эти массивы также можно увидеть с помощью клавиши F9. И, наконец, функция МИН возвращает минимальное значение строки интересующего значения. Таким образом, мы получаем номер строки для функции ИНДЕКС
4) номер столбца вычисляется очень похоже. То есть часть МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24);СТРОКА($1:$24) выполняется так же, как описано в п.3. Но здесь к номеру строки СТРОКА($1:$24) прибавляется номер столбца, делённый на 10000. Деление на 10000 даёт использование %%. Если в исследуемом диапазоне строк/столбцов меньше 100, достаточно использовать один знак %, т.е. делить на 100. Таким образом каждая нужная нам ячейка получает в соответствие уникальное значение, например, 5,0011 означает, что ячейка находится пятой строке и 11-м столбце. Как и в п.3 функция МИН возвращает наименьшее из таких значений. НО нас в данный момент интересует только номер столбца, поэтому с помощью функции ОСТАТ(...;1) мы убираем целую часть значения (остаётся, например, 0,0011) и умножаем на 10000, чтобы получить номер столбца
5) ну и ЕСЛИОШИБКА() обрабатывает значение ошибки, когда уже все уникальные значения выведены в столбец.

Как-то так. В заключении совет: почитайте статью Как разобраться в работе сложной формулы


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
Код
=ЕСЛИОШИБКА(ИНДЕКС(A$1:AE$24;МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24);СТРОКА($1:$24));9^9);ОСТАТ(МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24);СТРОКА($1:$24)+СТОЛБЕЦ(A:AE)%%));1)/1%%);"")

Объяснялка:
1) основная функция ИНДЕКС, которая возвращает искомые значения, первым аргументом имеет исследуемый диапазон ИНДЕКС(A$1:AE$24;
2) так как исследуемый диапазон двумерный (прямоугольный), функция ИНДЕКС для правильной работы требует в качестве второго аргумента номер строки, а третьего - номер столбца
3) номер строки вычисляется так МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24);СТРОКА($1:$24));9^9). То есть сначала проверяется, что очередное значение ещё не было выведено в предыдущих ячейках. За это отвечает часть НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24)). Можно было написать по-другому СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24)<>0, возможно, так понятнее.Эта функция даст массив из значений ИСТИНА/ЛОЖЬ. Этот массив можно увидеть, если в строке формул выделить указанный фрагмент и нажать клавишу F9 на клавиатуре. Вторая часть ДЛСТР(A$1:AE$24) выводит длину каждого значения в исследуемом диапазоне, чтобы отсеять пустые ячейки, где длина текста равна 0. Произведение НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24) вернёт массив нулей и чисел. Ноль - значит ячейка пустая или значение уже было выведено раньше, а число (длина текста), если значение ещё не выводилось.
Функция ЕСЛИ возвращает массив номеров строк СТРОКА($1:$24), если значение ещё не выводилось и ЛОЖЬ, если значение уже выведено. Все эти массивы также можно увидеть с помощью клавиши F9. И, наконец, функция МИН возвращает минимальное значение строки интересующего значения. Таким образом, мы получаем номер строки для функции ИНДЕКС
4) номер столбца вычисляется очень похоже. То есть часть МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(AH$1:AH1;A$1:AE$24))*ДЛСТР(A$1:AE$24);СТРОКА($1:$24) выполняется так же, как описано в п.3. Но здесь к номеру строки СТРОКА($1:$24) прибавляется номер столбца, делённый на 10000. Деление на 10000 даёт использование %%. Если в исследуемом диапазоне строк/столбцов меньше 100, достаточно использовать один знак %, т.е. делить на 100. Таким образом каждая нужная нам ячейка получает в соответствие уникальное значение, например, 5,0011 означает, что ячейка находится пятой строке и 11-м столбце. Как и в п.3 функция МИН возвращает наименьшее из таких значений. НО нас в данный момент интересует только номер столбца, поэтому с помощью функции ОСТАТ(...;1) мы убираем целую часть значения (остаётся, например, 0,0011) и умножаем на 10000, чтобы получить номер столбца
5) ну и ЕСЛИОШИБКА() обрабатывает значение ошибки, когда уже все уникальные значения выведены в столбец.

Как-то так. В заключении совет: почитайте статью Как разобраться в работе сложной формулы

Автор - Pelena
Дата добавления - 24.12.2020 в 19:27
shepenindmitrii Дата: Пятница, 25.12.2020, 11:14 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Pelena,
спасибо Вам огромное за детальную и понятную подсказку и совет.
Я им воспользовался и все стало ясно.
 
Ответить
СообщениеPelena,
спасибо Вам огромное за детальную и понятную подсказку и совет.
Я им воспользовался и все стало ясно.

Автор - shepenindmitrii
Дата добавления - 25.12.2020 в 11:14
shepenindmitrii Дата: Пятница, 25.12.2020, 15:36 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Pelena, а что означает 9^9?
 
Ответить
СообщениеPelena, а что означает 9^9?

Автор - shepenindmitrii
Дата добавления - 25.12.2020 в 15:36
Pelena Дата: Пятница, 25.12.2020, 16:31 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 17020
Репутация: 3670 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Это просто большое число, заведомо бОльшее всех других (девять в девятой степени)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЭто просто большое число, заведомо бОльшее всех других (девять в девятой степени)

Автор - Pelena
Дата добавления - 25.12.2020 в 16:31
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение уникальных значений (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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