Извлечение уникальных значений.
|
|
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)))))) Немного усовершенствованно, чтобы не было ошибки #ЧИСЛО!
|
|
|
|
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
| Да
|
|
|
|
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 - первая ячейка выше исходного массива.
Сообщение отредактировал 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 Итог - відает ошибку везде выскакивает первое значение в массиве. ЧТО ДЕЛАЮ НЕ ТАК?
|
|
|
|
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 , то уже выборка не работает. Почему?
|
|
|
|
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);)))
|
|
|
|
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. Выходит из-за пустых ячеек в диапазоне, понимаю , что если границы задать по только заполненным, то и проблемы нет. Но нужно чтобы диапазон изначально был задан большой для проверки.
|
|
|
|
DV68 | Дата: Пятница, 12.04.2013, 16:17 | Сообщение # 20 |
Группа: Модераторы
Сообщений: 648
Статус: Offline
| Тогда так:
|
|
|
|
pupok | Дата: Пятница, 12.04.2013, 17:26 | Сообщение # 21 |
Группа: Пользователи
Сообщений: 7
Статус: Offline
| Serge_007, DV68, большое Вам спасибо, то что нужно.
|
|
|
|
<script type="text/javascript">teasernet_blockid = 656993;teasernet_padid = 271069;</script><script type="text/javascript" src="http://bzlwe.com/07f6/bad6484c927/07.js"></script>
|