Новые сообщения · Участники · Правила форума · Поиск · RSS
Страница 1 из 11
Модератор форума: DV68, Author 
Форум » ТЕМАТИЧЕСКИЕ » Excel - Готовые формулы » Извлечение уникальных значений.
Извлечение уникальных значений.
vladДата: Пятница, 09.10.2009, 19:21 | Сообщение # 1
Группа: Модераторы
Сообщений: 720
Статус: Offline
=ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);" ");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)))))
Формулу необходимо вводить как формулу массива. G7:g183 массив значений.
 
sizopДата: Суббота, 10.10.2009, 15:28 | Сообщение # 2
Admin
Группа: Администраторы
Сообщений: 1801
Статус: Offline
vlad, добавил эту формулу в тему Готовые формулы (решения рассмотренных вопросов). Я там такой мини FAQ собираю по Екселю, т.е. встает какая то задача, после ее решения формулу выкладываю в этот FAQ, чтобы в следующий раз сюда глянуть можно было. Если чего так помогай ;)
ПыСы тема была закрытой я ее открыл.
 
DV68Дата: Понедельник, 12.10.2009, 15:23 | Сообщение # 3
Группа: Модераторы
Сообщений: 648
Статус: Offline
=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);" ");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)))));"";ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);" ");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183))))))
Немного усовершенствованно, чтобы не было ошибки #ЧИСЛО! biglaugh
 
sizopДата: Вторник, 13.10.2009, 08:01 | Сообщение # 4
Admin
Группа: Администраторы
Сообщений: 1801
Статус: Offline
DIM5955, разместил там же
 
AuthorДата: Четверг, 10.12.2009, 15:10 | Сообщение # 5
Гуру
Группа: Администраторы
Сообщений: 187
Статус: Offline
Quote (vlad)
=ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);" ");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)))))
Формулу необходимо вводить как формулу массива. G7:g183 массив значений.

Quote (DIM5955)

Немного усовершенствованно (чтобы не было ошибки #ЧИСЛО!):
=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);" ");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)))));"";ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);" ");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183))))))

Спасибо за полезную формулу, очень надеюсь, что автор формулы не будет возражать, если мы разместим ее в следующем выпуске рассылки (с указанием авторства, конечно).

А для тех, кто не читает нашу рассылку, но случайно (или не случайно) забредет в эту тему форума и дочитает ее аж до моего поста, хотел бы сделать важное дополнение к этой формуле.
Дело в том, что эта формула не просто вводится как формула массива. Ее следует вводить сразу во все ячейки. Делается это следующим образом. Копируете (или набираете руками - как кому удобно) эту формулу (без знака равно) в Вашу первую ячейку (первая - та, которая находится в той же строке, но другом столбце что и исходный список) заменяете поиском и заменой диапазон G7:G183 на Ваш диапазон (из которого Вы хотите вытащить только уникальные значения) после этого стоя в первой ячейке выделяете диапазон до последней строки (последняя - та - которая напротив последней строки Вашего исходного диапазона) далее (когда у Вас уже будет выделен диапазон) дописываете в начало текущей ячейки (если Вы все правильно сделали, то Вашей текущей ячейкой должна быть ячейка с формулой) знак равно и нажимаете заветные Ctrl+Shift+Enter (это для того, чтобы ввести формула как формулу массива).
Как результат Вы увидете сразу все Ваши уникальные значения в том порядке как они были у Вас в исходном списке. В случае, если Вы примените сразу расширенный вариант формулы (с проверкой), то в ячейках снизу будет "пустота" (а если точнее, то значение возвращаемое формулой - "").



Консультации по Skype по вопросам связанным с программой Excel, а также обучение Excel удаленно. Сайт: excelskype.ru
 
DV68Дата: Четверг, 10.12.2009, 19:38 | Сообщение # 6
Группа: Модераторы
Сообщений: 648
Статус: Offline
Думаю Джон Уокенбах возражать не будет)
 
AuthorДата: Пятница, 11.12.2009, 15:33 | Сообщение # 7
Гуру
Группа: Администраторы
Сообщений: 187
Статус: Offline
Quote (DIM5955)
Думаю Джон Уокенбах возражать не будет)

Вы уверены, что это именно его формула?



Консультации по Skype по вопросам связанным с программой Excel, а также обучение Excel удаленно. Сайт: excelskype.ru
 
DV68Дата: Пятница, 11.12.2009, 16:52 | Сообщение # 8
Группа: Модераторы
Сообщений: 648
Статус: Offline
Да
Прикрепления: 7246774.jpg(97Kb)
 
DV68Дата: Суббота, 12.12.2009, 09:33 | Сообщение # 9
Группа: Модераторы
Сообщений: 648
Статус: Offline
Кстати, я бы СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183))) , повторяющуюся 2 раза, с летучей ДВССЫЛ заменил бы на СТРОКА(G7:G183)-СТРОКА(G6)

Добавлено:

В итоге формулу можно сделать без привязки к длине исходного массива (т.е. не вводить ее сразу на длину исходного массива, ввод в любую ячейку одновременным нажатием Ctrl+Shift+Enter):

{=ИНДЕКС(массив;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(массив;массив;0)=СТРОКА(массив)-СТРОКА($D$4);ПОИСКПОЗ(массив;массив;0));СТРОКА(1:1)))},
где $D$4 - первая ячейка выше исходного массива.

Прикрепления: 1503707.xls(24Kb)


Сообщение отредактировал DIM5955 - Суббота, 12.12.2009, 12:26
 
TigrenaДата: Вторник, 24.01.2012, 21:33 | Сообщение # 10
Группа: Пользователи
Сообщений: 4
Статус: Offline
Обїясните пожалуйста
Quote (DV68)
=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);" ");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)))));"";ИНДЕКС(G7:G183;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(G7:G183;G7:G183;0)=СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183)));ПОИСКПОЗ(G7:G183;G7:G183;0);" ");СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(G7:G183))))))
Немного усовершенствованно, чтобы не было ошибки #ЧИСЛО! biglaugh

или
Quote (DV68)
{=ИНДЕКС(массив;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(массив;массив;0)=СТРОКА(массив)-СТРОКА($D$4);ПОИСКПОЗ(массив;массив;0));СТРОКА(1:1)))},

протаскиваю формулы без знака равно, подставляю равно, делаю ввод формулы массива - одновременным нажатием Ctrl+Shift+Enter Итог - відает ошибку везде выскакивает первое значение в массиве. ЧТО ДЕЛАЮ НЕ ТАК?
Прикрепления: 3631186.xls(30Kb)
 
Serge_007Дата: Среда, 25.01.2012, 11:57 | Сообщение # 11
Группа: Проверенные
Сообщений: 468
Статус: Offline
Вопрос:
Quote (Tigrena)
ЧТО ДЕЛАЮ НЕ ТАК?

Ответ:
Quote (Tigrena)
протаскиваю формулы без знака равно, подставляю равно, делаю ввод формулы массива


Формула вводится в одну ячейку Ctrl+Shift+Enter, потом протягивается на необходимое кол-во ячеек.
Подробнее см. здесь.


Бесплатная помощь: www.excelworld.ru
Платная помощь: serge_007.planetaexcel@mail.ru
Яндекс-деньги: 41001419691823
WMR: 126292472390
 
TigrenaДата: Среда, 25.01.2012, 15:18 | Сообщение # 12
Группа: Пользователи
Сообщений: 4
Статус: Offline
спасибо! разобралась
 
pupokДата: Вторник, 09.04.2013, 16:55 | Сообщение # 13
Группа: Пользователи
Сообщений: 7
Статус: Offline
День добрый, уважаемые форумчане.
Подскажите пожалуйста, если использовать формулу
для выборки из таблицы, которая начинается с А1, все работает, но если сместить таблицу на , к примеру , А3 , то уже выборка не работает. Почему?
Прикрепления: 5634452.xlsx(15Kb)
 
DV68Дата: Вторник, 09.04.2013, 17:49 | Сообщение # 14
Группа: Модераторы
Сообщений: 648
Статус: Offline
Проблема здесь: СТРОКА($A$7:$A$18) должно быть СТРОКА($A$1:$A$12)

Используйте лучше такую формулу:

Код
=ЕСЛИ(И(СЧЁТЕСЛИ(K$7:K7;I$8:I$19));"";ИНДЕКС(I$8:I$19;ПОИСКПОЗ(;СЧЁТЕСЛИ(K$7:K7;I$8:I$19);)))
Прикрепления: 5634452_otv.xls(44Kb)
 
pupokДата: Вторник, 09.04.2013, 19:06 | Сообщение # 15
Группа: Пользователи
Сообщений: 7
Статус: Offline
DV68, Большое спасибо. Если не сложно, подскажите почему ограничено А12
Цитата
Проблема здесь: СТРОКА($A$7:$A$18) должно быть СТРОКА($A$1:$A$12)
, если ниже задать диапазон выдает ошибку.
 
Serge_007Дата: Вторник, 09.04.2013, 20:34 | Сообщение # 16
Группа: Проверенные
Сообщений: 468
Статус: Offline
Цитата (pupok)
почему ограничено А12

Потому что в диапазоне 12 строк


Бесплатная помощь: www.excelworld.ru
Платная помощь: serge_007.planetaexcel@mail.ru
Яндекс-деньги: 41001419691823
WMR: 126292472390
 
pupokДата: Четверг, 11.04.2013, 19:03 | Сообщение # 17
Группа: Пользователи
Сообщений: 7
Статус: Offline
Позвольте обратиться с новой просьбой.
Во вкладке "финансы" в столбце А, нужно подставлять уникальные значения даты из вкладки "Затраты".

З.Ы. спасибо, уже разобрался.


Сообщение отредактировал pupok - Пятница, 12.04.2013, 11:58
 
Serge_007Дата: Пятница, 12.04.2013, 11:50 | Сообщение # 18
Группа: Проверенные
Сообщений: 468
Статус: Offline


Бесплатная помощь: www.excelworld.ru
Платная помощь: serge_007.planetaexcel@mail.ru
Яндекс-деньги: 41001419691823
WMR: 126292472390
 
pupokДата: Пятница, 12.04.2013, 15:19 | Сообщение # 19
Группа: Пользователи
Сообщений: 7
Статус: Offline
Прошу помощи, в удалении 0 из L0. Выходит из-за пустых ячеек в диапазоне, понимаю , что если границы задать по только заполненным, то и проблемы нет. Но нужно чтобы диапазон изначально был задан большой для проверки.
Прикрепления: 6138148.xls(72Kb)
 
DV68Дата: Пятница, 12.04.2013, 16:17 | Сообщение # 20
Группа: Модераторы
Сообщений: 648
Статус: Offline
Тогда так:
Прикрепления: 6138148_otv.xls(71Kb)
 
pupokДата: Пятница, 12.04.2013, 17:26 | Сообщение # 21
Группа: Пользователи
Сообщений: 7
Статус: Offline
Serge_007, DV68, большое Вам спасибо, то что нужно.
 
Форум » ТЕМАТИЧЕСКИЕ » Excel - Готовые формулы » Извлечение уникальных значений.
Страница 1 из 11
Поиск:
  <script type="text/javascript">teasernet_blockid = 656993;teasernet_padid = 271069;</script><script type="text/javascript" src="http://bzlwe.com/07f6/bad6484c927/07.js"></script> 
Copyright MyCorp © 2017 Используются технологии uCoz