Пов`язаний список, що випадає в excel за алфавітом.

Як зробити випадаючий список в Excel за алфавітом, щоб прізвище вибрати по букві алфавіту? Коли багато прізвищ в списку, що випадає, то зручно зробити в випадаючому списку алфавіт, а потім, вибравши потрібну букву - вибрати прізвище. Для цього потрібно зробити пов`язані випадають списки Excel.
Наприклад, на першому аркуші у нас основна таблиця, в яку будемо вставляти випадає з прізвищами.
На другому аркуші книги Excel зробимо список прізвищ. Сортуємо цей список в алфавітному порядку. Список такої.

Як зробити з великого списку з повторюваними прізвищами список без повторів для списку, дивіться в статті "Створення списку в Excel без повторів".
Тепер дамо ім`я цього списку. Як присвоїти ім`я діапазону, читайте в статті «Діапазон в Excel» тут. Ми назвали діапазон - «Прізвища».
Щоб змінювалися автоматично межі діапазону при додаванні або зменшенні прізвищ, потрібно зробити його динамічним. Дивіться в статті «Щоб розмір таблиці Excel змінювався автоматично».
Тепер нам потрібно створити список букв, на які починаються прізвища в нашому списку.
Як написати алфавіт в Excel формулою.
Спочатку в стовпці С пишемо звичайний алфавіт. Це можна швидко зробити формулою. В осередку З пишемо формулу.
= СИМВОЛ (192 + СТРОКА (Z1) -1)
192 - це код букви А. Копіюємо формулу вниз по стовпчику до букви Я. Алфавіт готовий.
Тепер в сусідньому стовпці потрібно написати тільки ті букви, з яких починаються прізвища в нашому списку. Тут нам допоможе формула масиву Excel. В осередку D2 ми написали таку довгу формулу.
= ЕСЛІОШІБКА (ІНДЕКС ($ C $ 2: $ C $ 33-НАЙМЕНШИЙ (ЯКЩО (СЧЁТЕСЛІ
(Фаміліі- $ C $ 2: $ C $ 33 "*") gt; 0-СТРОКА ($ C $ 2: $ C $ 33) - "") -
СТРОКА (Z1)) - СТРОКА ($ C $ 1)) - "")
Це формула масиву, П.Е. натискаємо не просто «Enter», а поєднання клавіш «Shift» + «Ctrl» + «Enter». Формула буде укладена в фігурні дужки. Простягаємо формулу вниз по стовпчику. У нас вийшло так.

Відео: Створення випадаючих списків в Excel

У формулі стоїть діапазон С2: С33 - це діапазон букв всього алфавіту в стовпці С.
Тепер в клітинку D1 встановимо, що випадає для букв стовпця D. У діалоговому вікні «Створення імені» в рядку «Діапазон» написали таку формулу.
= Зміщений (Аркуш2! $ D $ 2 --- СЧЁТЕСЛІ (Аркуш2! $ D $ 2: $ D $ 33 - "*?"))

Відео: Як зробити випадаючий список в Excel | Сума продажів по менеджерам в Excel # Бізнес #Excel # Навчання

Дамо ім`я комірки D1. Ми присвоїли ім`я комірки D1 - «Алфавіт».
Тепер виділяємо діапазон комірок з прізвищами в стовпці А і створюємо іменований діапазон. Ім`я діапазону напишемо - «Прізвище». У діалоговому вікні «Створення імені» в рядку «Діапазон» пишемо таку формулу.
= Зміщений (Аркуш2! $ A $ 1-ПОИСКПОЗ (Алфавіт "*" - Прізвища-0) - СЧЁТЕСЛІ (Прізвища-Алфавіт "*"))
Вийшло так.

Тепер залишилося створити список, що випадає. Той, до якого ми йшли.
У осередок Е1 вставляємо випадає через функцію «Перевірка даних». Як зробити випадаючий список в Excel, дивіться в статті "Список, що випадає в Excel". Діалогове вікно заповнили так.

Усе. Тепер в осередку D1 вибираємо букву, а в осередку Е1 з`явиться список, що випадає список прізвищ тільки на цю букву. Наприклад. Свяханний випадає в Excel за алфавітом.

або

Зовсім випадають списки в Excel за алфавітом.

Ці списки, що випадають з буквами і прізвищами можна встановити на іншому аркуші книги. Наприклад, в бланку, тощо
В Excel можна зробити дуже швидко, що випадає без додаткового списку з даними. Про це способі дивіться в статті «Як зробити випадаючий список в Excel без списку».

Поділися в соціальних мережах:

Схожі