Как создать динамический список в Excel без пустых значений?
В некоторых случаях у вас есть список данных с пустыми ячейками, и вы хотите создать динамический список на основе этих данных, исключив пустые значения. Многие, вероятно, удаляют пустые ячейки вручную одну за другой, а затем формируют динамический список. Однако сейчас я покажу вам быстрый способ решить эту задачу прямо в Excel.
Создание динамического списка и удаление пустых значений
Создание динамического списка и удаление пустых значений
1. Выберите ячейку рядом с исходным списком и введите в неё формулу =IF(B2=«»,"",MAX(A$1:A1)+1), затем перетащите маркер автозаполнения вниз до нужного диапазона. Теперь рядом с данными появятся номера — пустые ячейки останутся без них. См. снимок экрана:
В приведённой выше формуле B2 — это первая ячейка диапазона, на основе которого вы будете создавать динамический список.
2. Затем перейдите в другой столбец и введите формулу =IFERROR(INDEX($B$2:$B$11,MATCH(ROW()-ROW($D$1),$A$2:$A$11,0)),«»), после чего протяните маркер автозаполнения вниз до появления пустых ячеек.
В приведённой выше формуле B2:B11 — это диапазон исходных данных, а A2:A11 — диапазон с номерами строк из шага 1.
3. Затем выделите ячейку или диапазон, в котором нужно создать динамический список без пустых значений, и нажмите Данные > Проверка данных. См. снимок экрана:
4. В диалоговом окне Проверка данных выберите значение Список из списка Тип данных и введите формулу =OFFSET(Sheet1!$C$1,1,0,MAX(Sheet1!$A:$A),1) в поле Источник. См. снимок экрана:
Примечание:
1. В Excel 2007 пользователи не могут использовать ссылки на другие листы или книги в качестве критериев для Проверки данных. Поэтому на шаге 3 необходимо выбрать пустую ячейку на текущем листе и ввести формулу =OFFSET($C$1,1,0,MAX($A:$A),1) в текстовое поле Источник на шаге 4.
2. В приведённой выше формуле C1 — это первая ячейка нового списка, созданного на шаге 2.
5. Нажмите ОК. После этого вы увидите, что динамический список создан без пустых значений.
Легко выделяйте дублирующиеся или уникальные значения в Excel |
Работаете с диапазоном данных в Excel и нужно быстро выделить дублирующиеся строки? С помощью Kutools для Excel и функции Выбрать дубликаты/уникальные ячейки вы мгновенно найдёте и выделите повторяющиеся или уникальные значения в любом диапазоне. А ещё можно применить заливку фона и цвет шрифта к этим значениям, чтобы они сразу бросались в глаза на общем фоне! |
 |
Kutools для Excel— Расширьте возможности Excel более чем 300 необходимыми инструментами, чтобы работать быстрее и проще, а также используйте функции ИИ для более умной обработки данных и повышения продуктивности.Получить сейчас |
Связанные статьи:
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек