Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Базы данных


Rambler's Top100


Counter CO.KZ

Базы данных (список)
Список - это таблица, созданная по принципу базы данных. Первая строка списка должна содержать названия полей. При желании Вы можете сохранить рабочую книгу содержащую список в формате .dbf (database file)
  1. Как облегчить работу со списком ? 2004
  2. Как переместить данные вниз только в одном столбце ? 2004
  3. Как узнать основные правила работы и особенности автофильтра ? 2004
  4. Как создать автофильтр только в одном столбце ? 2004
  5. Как фильтровать список содержащий пустые строки ? 01.03.2007
  6. Как отобразить только нужные записи списка ? 2004
  7. Как после применения автофильтра автоматически подсвечивать заголовки столбцов ? 20.04.2007
  8. Как получить список уникальных (неповторящихся) значений ? 16.03.2012
  9. Как удалить все повторы (дубли) и оставить только уникальные (неповторящиеся) значения ? NEW 26.12.2015
  10. Как создать "умную" таблицу (список) ? NEW 22.09.2016
  11. Как вывести текст в алфавитном порядке ? 23.01.2005
  12. Как отсортировать данные с учётом регистра ? 10.08.2011
  13. Как заполнить список возрастающими значениями, например : 7, 10, 13 ... ? 10.06.2006
  14. Как проверить существует ли в списке конкретный текст ? 24.01.2005
  15. Как создать и применить свой собственный список автозаполнения ? 05.04.2005

  • Ответ :
    Для оптимизации работы со списком нужно присвоить диапазону ячеек имя База_данных
    1. Выделите диапазон ячеек содержащих Ваш список (вместе с заголовками)
    2. В поле Имя (в левой части строки формул) введите База_данных и нажмите ENTER.
  • Ответ :
    Выделите ячейку, которая находится над нужной ячейкой (или диапазоном ячеек)
    Нажмите и удерживайте клавишу SHIFT, подведите курсор мышки к самому краю (углу) ячейки (курсор должен изменить свой вид и принять форму двойной линии со стрелками) и тяните вниз до ячейки с которой должен начинаться диапазон ячеек.
    Примечание : подобные действия по своей сути являются вставкой новых ячеек, со сдвигом вниз или вправо : меню Вставка команда Ячейки
  • Ответ :
  • Фильтрация данных в списке позволяет отобразить только те данные, которые соответствуют заданным условиям отбора.
  • В рабочем листе может быть использован только один автофильтр.
  • Автофильтр можно применить для одного столбца, а также создать его в любой части списка.
  • Фильтр не использует строку в которой находится, а следовательно фильтр, который был создан без использования названия полей или расположен в любой другой части списка будет работать не вполне корректно.
  • Вы можете использовать фильтры для разных столбцов и с различными условиями отбора.
  • Если данные в одном из столбцов были отфильтрованы, то при использовании фильтра в другом столбце Вы можете использовать только те данные, которые видны в уже отфильтрованном списке.
  • Если кнопка фильтра синего цвета, то данный фильтр является "рабочим", т.е. в нём заданы условия (критерии) отбора.
  • С помощью фильтра Вы можете применить только два условия отбора.
  • При задании условий фильтр не различает строчные или прописные буквы.
  • Вы можете использовать символы подстановки ? и *
  • Если для определения суммы Вы используете кнопку Автосумма Σ на стандартной панели, то при подсчёте суммы будут использованы данные, отображённые на экране, а при изменении условий сумма будет автоматически изменяться.
  • При сортировке тоже учитываются только отображённые данные.
  • При создании диаграммы также учитываются только отображённые (отфильтрованные) данные, но при изменении условий отбора диаграмма будет автоматически изменяться.
  • При выводе на печать будут видны только отфильтрованные данные, без самого автофильтра.
  • Ответ :
    Выделите несколько строк в одном столбце (допускается выделение всего столбца).
    В меню Данные выберите пункт Фильтр, затем выберите команду Автофильтр.
  • Ответ :
    Для того, чтобы отфильтровать список содержащий пустые строки, выделите предварительно весь список (включая все строки, в т.ч. и пустые), а затем в меню Данные выберите пункт Фильтр и команду Автофильтр.

    Примечание : Если Вы используете этот [FAQ], то проблем с применением автофильтра в списке, который содержит пустые строки, просто не возникнет, конечно при условии корректности имени и ссылки на диапазон.
  • Ответ :
    Выделите любую ячейку в списке или строку, где содержатся названия полей.
    В меню Данные выберите пункт Фильтр, затем выберите команду Автофильтр.
    В правой части ячейки (ячеек) появится автофильтр (в виде кнопки с треугольником), нажмите на кнопку и выберите нужный вариант отбора записей.
    Автофильтр предполагает несколько возможностей отбора записей :
  • Выберите нужное значение в раскрывающемся списке для показа только тех ячеек в столбце, которые содержат выбранные Вами данные.
  • В раскрывающемся списке выберите Условие, далее в диалоговом окне Пользовательский автофильтр установите критерии отбора :
  • В списках расположенных слева Вам нужно выбрать необходимое условие (критерий) отбора.
  • В списках расположенных справа Вы можете ввести вручную (или выбрать из списка) данные, на основе которых Вы хотите использовать фильтр.

    Примечание : Символ * используется для подстановки любого количества символов, а ? для одного единственного символа.
  • Ответ :
    Для того, сразу после применения автофильтра, цвет заливки автоматически изменился во всех (или только выбранных) заголовках столбца, проделайте следующее :

    Вариант I.
    Выделите все ячейки, которые являются частью заголовка таблицы, иначе называемой шапкой. В меню Формат выберите команду Условное форматирование. В стандартном диалоговом окне, в поле со списком выберите Формула, а в текстовом поле введите эту формулу :
    =СЧЁТЗ($B3:$B102)<>ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;$B3:$B102)

    После чего, кликните кнопку Формат, установите нужные параметры форматирования (при этом, Вы можете изменить не только цвет заливки, но и, например, цвет шрифта) и подтвердите все изменения нажатием кнопки OK.

    Предполагается, что :
    Ячейки этих строк $3:$102 будут участвовать в фильтрации.

    Вариант II.
    В меню Вставка выберите пункт Имя, далее выберите команду Присвоить. В диалоговом окне Присвоение имени в поле Имя введите нужное имя, например ЕФИЛЬТР, а в поле Формула введите =ПОЛУЧИТЬ.ДОКУМЕНТ(85) и нажмите кнопку ОК.
    Затем, выделите все ячейки, которые являются частью заголовка таблицы, иначе называемой шапкой. В меню Формат выберите команду Условное форматирование. В стандартном диалоговом окне, в поле со списком выберите Формула, и в текстовом поле введите эту формулу =ЕФИЛЬТР
    После чего, кликните кнопку Формат, установите нужные параметры форматирования (при этом, Вы можете изменить не только цвет заливки, но и, например, цвет шрифта) и подтвердите все изменения нажатием кнопки OK.
  • Ответ :
    Для того, чтобы получить список уникальных, т.е. неповторящихся значений :
    1) Выделите все ячейки списка, включая заголовок, иначе называемой шапкой.
    2) В меню Данные выберите пункт Фильтр и команду Расширенный фильтр.
    3) Если Вы пропустили первый пункт или ошибочно выделили не тот диапазон ячеек, то в поле Исходный диапазон введите адрес нужного диапазона или щелкните на кнопку расположенную справа и выделите ячейки.
    4) Теперь, определитесь с тем, где должен располагаться список уникальных значений, если, например, в другом рабочем листе, то установите переключатель напротив Фильтровать список на месте, если же Вы сразу хотите скопировать полученные данные в ячейки активного рабочего листа, то установите переключатель напротив Скопировать результат в другое место и в поле Поместить результат в диапазон введите или выберите ячейку, с которой должен начинать список неповторяющихся значений (не забывая, что эта ячейка не должна пересекаться с ячейками исходного списка)
    5) Затем, обязательно установите переключатель напротив Только уникальные записи и кликните кнопку OK.
    6) Если Вы выбрали фильтровать на месте, то теперь можно скопировать полученный результат в ячейки другого рабочего листа, или оставить всё как есть, если список уникальных значений может представлять собой отфильтрованный список.
  • Ответ :
    Если Вы работаете с версиями 95-2003, то удалить повторы (дубли) можно, в несколько этапов :
    1) Получить список уникальных (неповторяющихся) значений, например, с помощью предыдущего совета и, например, скопировать полученные данные в новый рабочий лист.
    2) Затем, просто удалить лист с исходной таблицей (разумеется, если кроме таблицы там нет ничего нужного и ценного), либо очистить ячейки (в основном, для того, чтобы не перепутать старые и новые данные)
    3) И переименовать новый лист (если был удалён старый), либо вырезать CTRL+X уникальные данные и вставить их на место исходной таблицы CTRL+V

    Если же Вы используете 2007(или старше), то :
    Выделите все ячейки списка, если таблица большая, можно просто выделить любую ячейку.
    На вкладке Данные выберите команду Удалить повторы.
    Если в появившемся стандартном диалоговом окне появились "лишние" столбцы, то снимите "флажок" у ненужных.
    Если таблица не содержит заголовок, иначе называемой шапкой, то снимите "флажок" напротив Мои данные содержат заголовки и кликните кнопку OK.
  • Ответ : Актуально для MS Excel 2003, 2007
    Если Вы работаете с версией 2003, то создать список ("умную" таблицу) можно, так :
    1) Выделите необходимый диапазон (выделение несмежных диапазонов не допускается), а затем выберите один из вариантов
    Вариант I.
    2) Воспользуйтесь сочетанием клавиш CTRL+L
    Вариант II.
    2) В меню Данные выберите пункт Список и команду Создать список
    3) В появившемся стандартном диалоговом окне Создание списка установите "флажок" напротив Список с заголовками, если в выделенном диапазоне действительно наличествует заголовок(шапка) и подтвердите свой выбор нажатием кнопки Ok.

    Если Вы работаете с версией 2007, то создать "умную" таблицу (список) можно, так :
    1) Выделите необходимый диапазон (выделение несмежных диапазонов не допускается), а затем выберите один из вариантов
    Вариант I.
    2) Воспользуйтесь сочетанием клавиш CTRL+L или CTRL+T
    Вариант II.
    2) На закладке Вставка кликните кнопку Таблица
    Вариант III.
    2) На закладке Главная кликните кнопку Форматировать как таблицу и выберите наиболее приемлемый стиль таблицы.
    3) В появившемся стандартном диалоговом окне Создание таблицы установите "флажок" напротив Таблица с заголовками, если в выделенном диапазоне действительно наличествует заголовок(шапка) и подтвердите свой выбор нажатием кнопки Ok.
  • Ответ :
    Определитесь, данные из каких именно ячеек списка Вам необходимо вывести в алфавитном порядке.

    Вариант I.
    Если Вам необходимо отсортировать весь список/таблицу, то просто выделите любую ячейку из списка, в таком случае MS Excel автоматически выделит весь список, состоящий из смежных ячеек.
    Вариант II.
    При необходимости можно выделить только необходимую часть списка/таблицы, но в таком случае Вы можете получить стандартное предупреждение о том, что выделен не весь диапазон с данными.

    В меню Данные выберите пункт Сортировка, и если появится стандартное диалоговое окно Обнаружены данные вне указанного диапазона, то установите переключатель напротив Сортировать в пределах указанного диапазона и нажмите на кнопку Сортировка. Затем в появившемся стандартном диалоговом окне Сортировка диапазона нажмите кнопку OK.

    Примечание : При необходимости можно изменить порядок/приоритет сортировки.
  • Ответ :
    Алгоритм, позволяющий отсортировать данные таблицы по возрастанию/убыванию, довольно подробно расписан в предыдущем совете, однако, в некоторых случаях требуется выполнить сортировку таблицу учитывая регистр , и если Вы столкнулись именно с такой ситуацией, то после появления окна Сортировка диапазона кликните кнопку Параметры и в диалоговом окне Параметры сортировки установите "флажок" напротив Учитывать регистр и кликните Ok.
  • Ответ :
    Предположим, что нам предстоит заполнить диапазон "A2:A16" возрастающими значениями 7, 10, 13 ... и т.д.
    Вариант I.
    1. Выделите нужный диапазон A2:A16 и введите начальное значение списка 7 (завершив ввод нажатием клавиши ENTER)
    2. Затем в меню Правка выберите пункт Заполнить, далее выберите команду Прогрессия и в появившемся стандартном диалоговом окне, в поле Шаг введите 3 и нажмите кнопку OK.
    Вариант II.
    1. Выделите первую ячейку A2 нужного диапазона и введите начальное значение списка 7
    2. Затем подведите курсор мышки к правому нижнему углу ячейки (курсор должен изменить свой вид и принять форму крестика) кликните правой кнопкой мышки и не отпуская, тяните вниз до последней ячейки A16 диапазона, после чего отпустите кнопку мышки.
    3. В появившемся контекстном меню выберите команду Прогрессия и в появившемся диалоговом окне, в поле Шаг введите 3 и нажмите кнопку OK.
    Вариант III.
    1. Выделите первые две ячейки A2:A3 нужного диапазона и введите два первых значения списка 7 и 10
    2. Затем подведите курсор мышки к правому нижнему углу ячейки (курсор должен изменить свой вид и принять форму крестика) кликните левой кнопкой мышки и не отпуская, тяните вниз до последней ячейки A16 диапазона, после чего отпустите кнопку мышки.
    Вариант IV.
    1. Выделите первые две ячейки A2:A3 нужного диапазона и введите два первых значения списка 7 и 10
    2. Затем подведите курсор мышки к правому нижнему углу выделенного диапазона (курсор должен изменить свой вид и принять форму крестика) кликните правой кнопкой мышки и не отпуская, тяните вниз до последней ячейки A16 диапазона, после чего отпустите кнопку мышки.
    3. В появившемся контекстном меню выберите команду Заполнить
    Вариант V.
    1. Выделите первую ячейку A2 нужного диапазона и введите начальное значение списка 7
    2. Затем выделите остальные ячейки диапазона A3:A16 введите формулу =A2+3 и нажмите сочетание клавиш CTRL + ENTER.
  • Ответ :
    Выделите ячейку, следующую сразу за последней заполненной ячейкой в столбце.
    Вариант I. Затем нажмите на правую кнопку мышки и в появившемся контекстном меню выберите пункт Выбрать из списка.
    Вариант II. Просто нажмите комбинацию клавиш ALT + [cтрелка вниз]

    Обратите внимание на следующие моменты :
  • Данный способ действует только для одного столбца
  • В списке будут отображаться только :
    - уникальные записи списка
    - текстовые значения, т.е. в нём не будет числовых значений и дат.
  • Если список имеет пустые ячейки, то появившийся список будет содержать данные только из последнего диапазона смежных ячеек.
  • Если все ячейки в столбце имеют смежные/заполненные ячейки с аналогичным списком в соседнем столбце, то последнее ограничение автоматически снимается.
  • Ответ :
    Создание : В меню Сервис выберите команду Параметры, далее выберите закладку Списки. В поле Импорт списка из ячеек щелкните на кнопку расположенную справа и выделите диапазон ячеек содержащие данные, которые Вы предполагаете в дальнейшем использовать в качестве списка и нажмите ENTER. Затем нажмите на кнопку Импорт и кнопку OK.

    Создать список автозаполнения можно и вручную : для этого в меню Сервис выберите команду Параметры и выберите закладку Списки. Затем в списке Элементы списка введите нужные элементы списка, разделяя их ввод нажатием ENTER, после чего нажмите кнопку Добавить и кнопку ОК.

    Применение : Выделите нужный диапазон ячеек и введите любой элемент списка (желательно первый), после чего нажмите ENTER.
    В меню Правка выберите пункт Заполнить, далее выберите команду Прогрессия. В диалоговом окне Прогрессия и установите переключатель напротив текста автозаполнение в разделе Тип и нажмите кнопку ОК.

    Вопросы - Синонимы
  • Как применить автофильтр ?
  • Как выполнить сортировку списка ?


  • Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

  • © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff