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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 3
  • 1
  • 2
  • 3
  • »
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вывод уникальных текстовых значений таблицы. (Формулы/Formulas)
Вывод уникальных текстовых значений таблицы.
Rioran Дата: Вторник, 15.07.2014, 15:37 | Сообщение № 1
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
Всем привет и хорошего настроения!

Есть куча способов получить уникальные значения столбца или строки. А что, если значения раскиданы по всей таблице, в разных столбцах? Ранее знатоки решали похожую задачу для чисел. А что, если значения текстовые, как во вложении? Там нужны другие формулы.

Было бы очень интересно взглянуть на решение именно формулой. А то макросом любой Rioran может.


Господа формулисты! Покажите, пожалуйста, чудо =)
К сообщению приложен файл: Unique_Values.xlsx(9.1 Kb)


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
СообщениеВсем привет и хорошего настроения!

Есть куча способов получить уникальные значения столбца или строки. А что, если значения раскиданы по всей таблице, в разных столбцах? Ранее знатоки решали похожую задачу для чисел. А что, если значения текстовые, как во вложении? Там нужны другие формулы.

Было бы очень интересно взглянуть на решение именно формулой. А то макросом любой Rioran может.


Господа формулисты! Покажите, пожалуйста, чудо =)

Автор - Rioran
Дата добавления - 15.07.2014 в 15:37
_Boroda_ Дата: Среда, 16.07.2014, 01:54 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15979
Репутация: 6209 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Такой вариант
Код
=ИНДЕКС(B$3:H$14;МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(L$5:L5;B$3:H$14))*ДЛСТР(B$3:H$14);СТРОКА($1:$12)));ОСТАТ(МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(L$5:L5;B$3:H$14))*ДЛСТР(B$3:H$14);СТРОКА($1:$12)+СТОЛБЕЦ(A:G)%%));1)/1%%)
Без извращений, просто тупо бегает слева направо потом вниз и собирает значения.
В файле еще вариант покороче есть
К сообщению приложен файл: Unique_Values_1.xlsx(10.3 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТакой вариант
Код
=ИНДЕКС(B$3:H$14;МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(L$5:L5;B$3:H$14))*ДЛСТР(B$3:H$14);СТРОКА($1:$12)));ОСТАТ(МИН(ЕСЛИ(НЕ(СЧЁТЕСЛИ(L$5:L5;B$3:H$14))*ДЛСТР(B$3:H$14);СТРОКА($1:$12)+СТОЛБЕЦ(A:G)%%));1)/1%%)
Без извращений, просто тупо бегает слева направо потом вниз и собирает значения.
В файле еще вариант покороче есть

Автор - _Boroda_
Дата добавления - 16.07.2014 в 01:54
HoBU4OK Дата: Среда, 16.07.2014, 08:12 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 302
Репутация: 14 ±
Замечаний: 0% ±

Excel 2010
А можно усложнить задачу?...
Чтобы при продолжении формулы значения не повторялись...(выделено Желтым)
К сообщению приложен файл: 4898669.xlsx(11.5 Kb)


Я думал, ты остроглазый лев, а ты слепая собака :-)

Сообщение отредактировал HoBU4OK - Среда, 16.07.2014, 08:13
 
Ответить
СообщениеА можно усложнить задачу?...
Чтобы при продолжении формулы значения не повторялись...(выделено Желтым)

Автор - HoBU4OK
Дата добавления - 16.07.2014 в 08:12
_Boroda_ Дата: Среда, 16.07.2014, 09:16 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15979
Репутация: 6209 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А можно усложнить задачу?...
Можно. Усложняйте. Что у Вас получилось?

У меня так
К сообщению приложен файл: 11224433.xlsx(10.6 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
А можно усложнить задачу?...
Можно. Усложняйте. Что у Вас получилось?

У меня так

Автор - _Boroda_
Дата добавления - 16.07.2014 в 09:16
Rioran Дата: Среда, 16.07.2014, 09:35 | Сообщение № 5
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
Александр, классная формула!

Если к ней приладить простейший костыль (условное форматирование или формула СЧЁТЕСЛИ от начала столбца до каждой конкретной ячейки), то можно отслеживать, в какой момент в столбце значения повторяются. И этим найти количество уникальных значений, если количество это заранее не известно.

Вижу, что судя по твоей формуле - размеры таблицы надо знать заранее. И при добавлении новых строк/столбцов формулу надо будет корректировать. Реально ли через ДВССЫЛ и СЧЁТЗ добавить формуле гибкости в этом плане? Например, если таблица на одном листе, а формула на другом для удобства.

Кстати, что означает знак двойного процента? Самое адекватное, что выдаёт гугление - что-то про DOS и пакетные файлы. При этом само %% или "%%" загуглить низя - видимо, воспринимается как спец-символ поиска.

*******

Пришлось думать самому =) Ага, значит, деление на 1-цу с двойным процентом - это как если умножить число на 10'000...


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279


Сообщение отредактировал Rioran - Среда, 16.07.2014, 09:43
 
Ответить
СообщениеАлександр, классная формула!

Если к ней приладить простейший костыль (условное форматирование или формула СЧЁТЕСЛИ от начала столбца до каждой конкретной ячейки), то можно отслеживать, в какой момент в столбце значения повторяются. И этим найти количество уникальных значений, если количество это заранее не известно.

Вижу, что судя по твоей формуле - размеры таблицы надо знать заранее. И при добавлении новых строк/столбцов формулу надо будет корректировать. Реально ли через ДВССЫЛ и СЧЁТЗ добавить формуле гибкости в этом плане? Например, если таблица на одном листе, а формула на другом для удобства.

Кстати, что означает знак двойного процента? Самое адекватное, что выдаёт гугление - что-то про DOS и пакетные файлы. При этом само %% или "%%" загуглить низя - видимо, воспринимается как спец-символ поиска.

*******

Пришлось думать самому =) Ага, значит, деление на 1-цу с двойным процентом - это как если умножить число на 10'000...

Автор - Rioran
Дата добавления - 16.07.2014 в 09:35
Rioran Дата: Среда, 16.07.2014, 09:52 | Сообщение № 6
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, спасибо, это было на поверхности, ещё в предыдущем посте сам догадался =)


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
Сообщение_Boroda_, спасибо, это было на поверхности, ещё в предыдущем посте сам догадался =)

Автор - Rioran
Дата добавления - 16.07.2014 в 09:52
122334boss Дата: Среда, 16.07.2014, 09:54 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 200
Репутация: 0 ±
Замечаний: 20% ±

_Boroda_,
ваше решение интересное, но если уникальных значений меньше чем 9, то в нижних ячейках повторяется верхнее значение "мед".
 
Ответить
Сообщение_Boroda_,
ваше решение интересное, но если уникальных значений меньше чем 9, то в нижних ячейках повторяется верхнее значение "мед".

Автор - 122334boss
Дата добавления - 16.07.2014 в 09:54
122334boss Дата: Среда, 16.07.2014, 10:09 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 200
Репутация: 0 ±
Замечаний: 20% ±

_Boroda_,
спасибо за помощь, но вы меня не совсем правильно поняли, в вашем примере, в таблице уникальных 9 значений (Желе, Мёд, Перец, Сахар, Сок, Соль, Соя, Сыр, Торт) если убрать одно из них в таблице B3:Н14 то ваша формула будет повторять первое найденное значение, а точнее значение ячейки В3, но если из ячейки В3 убрать значение, то все работает, а вместо повторений будут нолики.
 
Ответить
Сообщение_Boroda_,
спасибо за помощь, но вы меня не совсем правильно поняли, в вашем примере, в таблице уникальных 9 значений (Желе, Мёд, Перец, Сахар, Сок, Соль, Соя, Сыр, Торт) если убрать одно из них в таблице B3:Н14 то ваша формула будет повторять первое найденное значение, а точнее значение ячейки В3, но если из ячейки В3 убрать значение, то все работает, а вместо повторений будут нолики.

Автор - 122334boss
Дата добавления - 16.07.2014 в 10:09
Rioran Дата: Среда, 16.07.2014, 10:10 | Сообщение № 9
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
Александр,

А сколько времени у тебя ушло на написание формулы? Ты раньше решал похожую задачу?


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
СообщениеАлександр,

А сколько времени у тебя ушло на написание формулы? Ты раньше решал похожую задачу?

Автор - Rioran
Дата добавления - 16.07.2014 в 10:10
_Boroda_ Дата: Среда, 16.07.2014, 10:29 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15979
Репутация: 6209 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
но вы меня не совсем правильно поняли,
Я-то Вас правильно понял. Просто почему-то файл в сообщении 4 приложился старый. Сейчас попытался перевложить, получилось только с 3-го раза - все время во вложении оказывался старый файл. Не знаю, почему так.
Смотрите теперь.

А сколько времени у тебя ушло на написание формулы?
Да почти нисколько. Сел, да написал. Я не могу себе позволить долго над одной задачей думать, иначе на другие времени не хватит. Правда, первоначальный вариант был раза в полтора длиньшее.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
но вы меня не совсем правильно поняли,
Я-то Вас правильно понял. Просто почему-то файл в сообщении 4 приложился старый. Сейчас попытался перевложить, получилось только с 3-го раза - все время во вложении оказывался старый файл. Не знаю, почему так.
Смотрите теперь.

А сколько времени у тебя ушло на написание формулы?
Да почти нисколько. Сел, да написал. Я не могу себе позволить долго над одной задачей думать, иначе на другие времени не хватит. Правда, первоначальный вариант был раза в полтора длиньшее.

Автор - _Boroda_
Дата добавления - 16.07.2014 в 10:29
122334boss Дата: Среда, 16.07.2014, 10:41 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 200
Репутация: 0 ±
Замечаний: 20% ±

_Boroda_,
спасибо, это друге дело все работает.
а что значит честная и не совсем? и есть ли в формуле ограничения в объеме обрабатываемого диапазона? интересуюсь просто для развития...
 
Ответить
Сообщение_Boroda_,
спасибо, это друге дело все работает.
а что значит честная и не совсем? и есть ли в формуле ограничения в объеме обрабатываемого диапазона? интересуюсь просто для развития...

Автор - 122334boss
Дата добавления - 16.07.2014 в 10:41
_Boroda_ Дата: Среда, 16.07.2014, 10:59 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15979
Репутация: 6209 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
"Не совсем честная" - это значит, что в ячейке М5 на самом деле не текст "Формула:", а нолик. А текст "Формула:" выводится с помощью формата ячейки.
Ограничения на объем теоретически нет (в пределах листа Excel, конечно), а практически все зависит от мощности машины. Сколько у Вас строк, столбцов и примерно сколько уникальных?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение"Не совсем честная" - это значит, что в ячейке М5 на самом деле не текст "Формула:", а нолик. А текст "Формула:" выводится с помощью формата ячейки.
Ограничения на объем теоретически нет (в пределах листа Excel, конечно), а практически все зависит от мощности машины. Сколько у Вас строк, столбцов и примерно сколько уникальных?

Автор - _Boroda_
Дата добавления - 16.07.2014 в 10:59
122334boss Дата: Среда, 16.07.2014, 11:10 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 200
Репутация: 0 ±
Замечаний: 20% ±

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

Автор - 122334boss
Дата добавления - 16.07.2014 в 11:10
HoBU4OK Дата: Среда, 16.07.2014, 11:18 | Сообщение № 14
Группа: Проверенные
Ранг: Обитатель
Сообщений: 302
Репутация: 14 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, в чем разница (файлы прилагаются)?
и возможно ли скрыть в вычислениях повторяющиеся "0" или "мёд"
К сообщению приложен файл: 6476902.xlsx(10.8 Kb) · 9622183.xlsx(10.6 Kb)


Я думал, ты остроглазый лев, а ты слепая собака :-)
 
Ответить
Сообщение_Boroda_, в чем разница (файлы прилагаются)?
и возможно ли скрыть в вычислениях повторяющиеся "0" или "мёд"

Автор - HoBU4OK
Дата добавления - 16.07.2014 в 11:18
122334boss Дата: Среда, 16.07.2014, 11:48 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 200
Репутация: 0 ±
Замечаний: 20% ±

HoBU4OK,
посмотрите 4 сообщение в этой теме, там есть рабочий файл
 
Ответить
СообщениеHoBU4OK,
посмотрите 4 сообщение в этой теме, там есть рабочий файл

Автор - 122334boss
Дата добавления - 16.07.2014 в 11:48
HoBU4OK Дата: Среда, 16.07.2014, 12:08 | Сообщение № 16
Группа: Проверенные
Ранг: Обитатель
Сообщений: 302
Репутация: 14 ±
Замечаний: 0% ±

Excel 2010
У меня почему то открывается "Только для чтения" :( %) :(


Я думал, ты остроглазый лев, а ты слепая собака :-)
 
Ответить
СообщениеУ меня почему то открывается "Только для чтения" :( %) :(

Автор - HoBU4OK
Дата добавления - 16.07.2014 в 12:08
122334boss Дата: Среда, 16.07.2014, 12:20 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 200
Репутация: 0 ±
Замечаний: 20% ±

_Boroda_,
можно узнать, что с моим вопросом?
 
Ответить
Сообщение_Boroda_,
можно узнать, что с моим вопросом?

Автор - 122334boss
Дата добавления - 16.07.2014 в 12:20
_Boroda_ Дата: Среда, 16.07.2014, 12:28 | Сообщение № 18
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15979
Репутация: 6209 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
можно узнать, что с моим вопросом?
А почто Вы диапазоны в формуле на поменяли?
Держите. И тяните формулу на 294 ячейки вниз

HoBU4OK, Попробуйте в этом файле
К сообщению приложен файл: 5132381_1.xlsx(16.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
можно узнать, что с моим вопросом?
А почто Вы диапазоны в формуле на поменяли?
Держите. И тяните формулу на 294 ячейки вниз

HoBU4OK, Попробуйте в этом файле

Автор - _Boroda_
Дата добавления - 16.07.2014 в 12:28
HoBU4OK Дата: Среда, 16.07.2014, 13:23 | Сообщение № 19
Группа: Проверенные
Ранг: Обитатель
Сообщений: 302
Репутация: 14 ±
Замечаний: 0% ±

Excel 2010
:( нет, мой 2003, открывать не хочет... :(


Я думал, ты остроглазый лев, а ты слепая собака :-)
 
Ответить
Сообщение:( нет, мой 2003, открывать не хочет... :(

Автор - HoBU4OK
Дата добавления - 16.07.2014 в 13:23
_Boroda_ Дата: Среда, 16.07.2014, 13:38 | Сообщение № 20
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15979
Репутация: 6209 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Тьфу, я и не заметит, что у Вас 2003.
Ловите.
К сообщению приложен файл: 11224433_1.xls(34.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТьфу, я и не заметит, что у Вас 2003.
Ловите.

Автор - _Boroda_
Дата добавления - 16.07.2014 в 13:38
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вывод уникальных текстовых значений таблицы. (Формулы/Formulas)
  • Страница 1 из 3
  • 1
  • 2
  • 3
  • »
Поиск:

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