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

Вход

Регистрация

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

 

= Мир MS Excel/Связанный список уникальных значений - Мир MS Excel

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

В приложенном файле (dd.xlsx)два вспомогательных столбца. Помогите, пожалуйста, изменить немного их - категории (уникальные) вытянуть в строку. Под уникальными категориями расположить значения, относящиеся к данной категории, оставив так же только уникальные (в примере не дублируются, но иногда могут дублироваться потому что).

И выпадающие связанные списки на основе вспомогательных столбиков иметь возможность продлить вниз (а не как сейчас - для двух ячеек только). И было бы хорошо иметь в выпадающем списке сортировку по алфавиту.
Для примера выпадающий список на одном листе с данными, но в идеале он буде на другом листе и заполняться по мере необходимости путем выбора, построчно.

Ковырялся и на других примерах из интернет, но не все получается. Дело в том, что существующие столбцы А и Б нежелательно трогать (исходные столбы). например той же сортировкой. Просто на их основе сделать связанный выпадающий список на втором листе, можно в тех же столбах.

То же самое, по идее, как и в файле unique-distinct-dependent-lists1 - на втором листе уникальные данные для исходных, в таком же расположении. Только что выпадающие списки построчно на отдельный лист, не только всего два поля с выбором.
К сообщению приложен файл: dd.xlsx (10.6 Kb) · unique-distinct.xls (29.0 Kb)
 
Ответить
СообщениеВ приложенном файле (dd.xlsx)два вспомогательных столбца. Помогите, пожалуйста, изменить немного их - категории (уникальные) вытянуть в строку. Под уникальными категориями расположить значения, относящиеся к данной категории, оставив так же только уникальные (в примере не дублируются, но иногда могут дублироваться потому что).

И выпадающие связанные списки на основе вспомогательных столбиков иметь возможность продлить вниз (а не как сейчас - для двух ячеек только). И было бы хорошо иметь в выпадающем списке сортировку по алфавиту.
Для примера выпадающий список на одном листе с данными, но в идеале он буде на другом листе и заполняться по мере необходимости путем выбора, построчно.

Ковырялся и на других примерах из интернет, но не все получается. Дело в том, что существующие столбцы А и Б нежелательно трогать (исходные столбы). например той же сортировкой. Просто на их основе сделать связанный выпадающий список на втором листе, можно в тех же столбах.

То же самое, по идее, как и в файле unique-distinct-dependent-lists1 - на втором листе уникальные данные для исходных, в таком же расположении. Только что выпадающие списки построчно на отдельный лист, не только всего два поля с выбором.

Автор - w00t
Дата добавления - 17.02.2016 в 12:51
VIDEO56 Дата: Среда, 17.02.2016, 13:00 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 262
Репутация: 23 ±
Замечаний: 0% ±

Excel 2010
Если правильно понял!
К сообщению приложен файл: __.xlsx (13.6 Kb)


Всем удачного дня!
 
Ответить
СообщениеЕсли правильно понял!

Автор - VIDEO56
Дата добавления - 17.02.2016 в 13:00
китин Дата: Среда, 17.02.2016, 13:12 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Если правильно понял!
К сообщению приложен файл: 9005066.xlsx (14.2 Kb)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
Если правильно понял!

Автор - китин
Дата добавления - 17.02.2016 в 13:12
_Boroda_ Дата: Среда, 17.02.2016, 13:16 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Давайте определимся уже как-то - Вам что в итоге получить нужно?
В строку вытянуть или в столбцы, как в примере?

Может, Вы сделаете файл, максимально приближенный к действительности (без вот этого и подобных моментов - "в примере не дублируются, но иногда могут дублироваться")? И в этом же файле вручную сделайте так, как Вы хотите, чтобы получилось.

А то смотрите, сколько еслиправильнопонимателей появилось.
Если правильно понял!
Если правильно понял!

Это показатель того, что все как-то очень уж расплывчато.


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

Может, Вы сделаете файл, максимально приближенный к действительности (без вот этого и подобных моментов - "в примере не дублируются, но иногда могут дублироваться")? И в этом же файле вручную сделайте так, как Вы хотите, чтобы получилось.

А то смотрите, сколько еслиправильнопонимателей появилось.
Если правильно понял!
Если правильно понял!

Это показатель того, что все как-то очень уж расплывчато.

Автор - _Boroda_
Дата добавления - 17.02.2016 в 13:16
w00t Дата: Среда, 17.02.2016, 13:31 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 131
Репутация: 3 ±
Замечаний: 0% ±

Понял )
Второй вариант почти близко.
В примерно файлике оставил суть. Листы по порядку, начиная с первого.
К сообщению приложен файл: 6442600.xlsx (17.9 Kb)
 
Ответить
СообщениеПонял )
Второй вариант почти близко.
В примерно файлике оставил суть. Листы по порядку, начиная с первого.

Автор - w00t
Дата добавления - 17.02.2016 в 13:31
_Boroda_ Дата: Среда, 17.02.2016, 14:56 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Смотрите именованные диапазоны (Контрл F3)
К сообщению приложен файл: 6442600_1.xlsx (18.7 Kb)


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

Автор - _Boroda_
Дата добавления - 17.02.2016 в 14:56
w00t Дата: Среда, 17.02.2016, 15:33 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 131
Репутация: 3 ±
Замечаний: 0% ±

Круто, то самое, спасибо, буду разбираться. Только не смог понять, что значит три буквы "яяя" в любой формуле на среднем листе?
И попутно вопрос, если на последнем листе в дропдаун выбираем в столбе А другое значение, то в столбе B автоматически бы очищалась ячейка? (потому что нужно будет выбрать другое значение)
 
Ответить
СообщениеКруто, то самое, спасибо, буду разбираться. Только не смог понять, что значит три буквы "яяя" в любой формуле на среднем листе?
И попутно вопрос, если на последнем листе в дропдаун выбираем в столбе А другое значение, то в столбе B автоматически бы очищалась ячейка? (потому что нужно будет выбрать другое значение)

Автор - w00t
Дата добавления - 17.02.2016 в 15:33
_Boroda_ Дата: Среда, 17.02.2016, 15:48 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
что значит три буквы "яяя"
я - символ с максимальным кодом, яяя - самое "большое" слово (яя не подходит - есть такая речка).
если на последнем листе в дропдаун выбираем в столбе А другое значение, то в столбе B автоматически бы очищалась ячейка?
Это только макросом. Можно красить неверные значения условным форматированием.
К сообщению приложен файл: 6442600_2.xlsx (18.8 Kb)


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

Автор - _Boroda_
Дата добавления - 17.02.2016 в 15:48
w00t Дата: Среда, 17.02.2016, 17:19 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 131
Репутация: 3 ±
Замечаний: 0% ±

Спасибо большое. Макросом не нужно, именно формулой, как сейчас условное форматирование делает.
 
Ответить
СообщениеСпасибо большое. Макросом не нужно, именно формулой, как сейчас условное форматирование делает.

Автор - w00t
Дата добавления - 17.02.2016 в 17:19
w00t Дата: Суббота, 20.02.2016, 14:50 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 131
Репутация: 3 ±
Замечаний: 0% ±

К сообщению приложен файл: 6442600_2.xlsx(19Kb)

Не смог понять одну штуку (где она меняется). Вставка пустых строк на первом листе (в исходной таблице). Нужно вверху вставить три пустые строки, но при этом на втором по порядку листе данные съезжают.
Наверное, в диспетчере имен Заг1 и Заг2 подправить, но как?

Точнее в каждом из листов по три пустых строки сверху нужно. Пытался сделать, вроде везде нормально, кроме если как первый лист менять.


Сообщение отредактировал w00t - Суббота, 20.02.2016, 14:55
 
Ответить
Сообщение
К сообщению приложен файл: 6442600_2.xlsx(19Kb)

Не смог понять одну штуку (где она меняется). Вставка пустых строк на первом листе (в исходной таблице). Нужно вверху вставить три пустые строки, но при этом на втором по порядку листе данные съезжают.
Наверное, в диспетчере имен Заг1 и Заг2 подправить, но как?

Точнее в каждом из листов по три пустых строки сверху нужно. Пытался сделать, вроде везде нормально, кроме если как первый лист менять.

Автор - w00t
Дата добавления - 20.02.2016 в 14:50
_Boroda_ Дата: Суббота, 20.02.2016, 15:00 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если именно пустые строки, то нужно прибавить 3 (в общем случае - кол-во пустых значений в ячейках выше заголовка)
К сообщению приложен файл: 6442600_2_1.xlsx (18.6 Kb)


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

Автор - _Boroda_
Дата добавления - 20.02.2016 в 15:00
w00t Дата: Суббота, 20.02.2016, 15:22 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 131
Репутация: 3 ±
Замечаний: 0% ±

Да, так, спасибо :)
В массиве проще было, там -3 от строки отнял, а тут затупил
К сообщению приложен файл: 1271559.xlsx (18.6 Kb)
 
Ответить
СообщениеДа, так, спасибо :)
В массиве проще было, там -3 от строки отнял, а тут затупил

Автор - w00t
Дата добавления - 20.02.2016 в 15:22
_Boroda_ Дата: Суббота, 20.02.2016, 18:44 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
В массиве проще было

Давайте я словами объясню логику формулы
Код
=Лист3!$A$5:ИНДЕКС(Лист3!$A:$A;СЧЁТЗ(Лист3!$A:$A)+3)

1. Мы знаем, с какой ячейки нам нужно, чтобы список начинался, поэтому сразу пишем ссылку на А5 - это начало.

2. Мы не знаем, где список будет заканчиваться, поэтому вынуждены искать ячейку окончания. Причем искать так, чтобы формула была не тяжелой (типа СУММПРОИЗВ) и не волатильной (типа СМЕЩ). ИНДЕКС - очень легкая функция, поэтому засовываем в него сразу весь столбец А и ищем номер последней заполненной ячейки в этом столбце.

3. Если бы у нас в столбце А были заполнены ячейки, начиная с А1 и дальше по порядку, то нас вполне устроила бы тоже очень легкая функция СЧЁТЗ(А:А) - она как раз даст количество заполненных ячеек, которое совпадает с порядковым номером последней заполненной ячейки, который нам и нужен для ИНДЕКСа в доме, который построил Смит. Но у нас сверху есть еще пустые строки, поэтому СЧЁТЗ даст нам порядковый номер ячейки, котроая расположена выше искомой на столько, сколько у нас этих пустых первых строк (я уж не говорю про то, что в значениях столбца А не должно быть промежутков - это подразумевается изначально). Поэтому мы пишем СЧЁТЗ(А:А)+3

4. Но пустые строки сверху просто так не добавляют. Наличие таких строк обычно подразумевает возможность их заполнения, иначе зачем они? И вот мы снова возвращаемся к вопросу о количестве пустых ячеек в диапазоне А1:А3, ведь не обязательно же будут заполнены все 3 строки, может быть заполнена одна, две, три или ни одной. Я обычно пишу так:
Код
СЧЁТЗ(A:A)+ЧСТРОК(A$1:A$4)-СЧЁТЗ(A$1:A$4)
и тогда полная формула будет выглядеть вот так:
Код
=A5:ИНДЕКС(A:A;СЧЁТЗ(A:A)+ЧСТРОК(A$1:A$4)-СЧЁТЗ(A$1:A$4))
Она хоть и не короткая, но зато очень легкая


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

Давайте я словами объясню логику формулы
Код
=Лист3!$A$5:ИНДЕКС(Лист3!$A:$A;СЧЁТЗ(Лист3!$A:$A)+3)

1. Мы знаем, с какой ячейки нам нужно, чтобы список начинался, поэтому сразу пишем ссылку на А5 - это начало.

2. Мы не знаем, где список будет заканчиваться, поэтому вынуждены искать ячейку окончания. Причем искать так, чтобы формула была не тяжелой (типа СУММПРОИЗВ) и не волатильной (типа СМЕЩ). ИНДЕКС - очень легкая функция, поэтому засовываем в него сразу весь столбец А и ищем номер последней заполненной ячейки в этом столбце.

3. Если бы у нас в столбце А были заполнены ячейки, начиная с А1 и дальше по порядку, то нас вполне устроила бы тоже очень легкая функция СЧЁТЗ(А:А) - она как раз даст количество заполненных ячеек, которое совпадает с порядковым номером последней заполненной ячейки, который нам и нужен для ИНДЕКСа в доме, который построил Смит. Но у нас сверху есть еще пустые строки, поэтому СЧЁТЗ даст нам порядковый номер ячейки, котроая расположена выше искомой на столько, сколько у нас этих пустых первых строк (я уж не говорю про то, что в значениях столбца А не должно быть промежутков - это подразумевается изначально). Поэтому мы пишем СЧЁТЗ(А:А)+3

4. Но пустые строки сверху просто так не добавляют. Наличие таких строк обычно подразумевает возможность их заполнения, иначе зачем они? И вот мы снова возвращаемся к вопросу о количестве пустых ячеек в диапазоне А1:А3, ведь не обязательно же будут заполнены все 3 строки, может быть заполнена одна, две, три или ни одной. Я обычно пишу так:
Код
СЧЁТЗ(A:A)+ЧСТРОК(A$1:A$4)-СЧЁТЗ(A$1:A$4)
и тогда полная формула будет выглядеть вот так:
Код
=A5:ИНДЕКС(A:A;СЧЁТЗ(A:A)+ЧСТРОК(A$1:A$4)-СЧЁТЗ(A$1:A$4))
Она хоть и не короткая, но зато очень легкая

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

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