|
Базы данных (список)
Список - это таблица, созданная по принципу базы данных.
Первая строка списка должна содержать названия полей. При желании Вы
можете сохранить рабочую книгу содержащую список в формате .dbf (database file)
- Как облегчить работу со списком ? 2004
- Как переместить данные вниз только в одном столбце ? 2004
- Как узнать основные правила работы и особенности автофильтра ? 2004
- Как создать автофильтр только в одном столбце ? 2004
- Как фильтровать список содержащий пустые строки ? 01.03.2007
- Как отобразить только нужные записи списка ? 2004
- Как после применения автофильтра автоматически подсвечивать
заголовки столбцов ? 20.04.2007
- Как получить список уникальных (неповторящихся) значений ?
16.03.2012
- Как удалить все повторы (дубли) и оставить только уникальные
(неповторящиеся) значения ? NEW 26.12.2015
- Как создать "умную" таблицу (список) ?
NEW 22.09.2016
- Как вывести текст в алфавитном порядке ? 23.01.2005
- Как отсортировать данные с учётом регистра ? 10.08.2011
- Как заполнить список возрастающими значениями, например :
7, 10, 13 ... ? 10.06.2006
- Как проверить существует ли в списке конкретный текст ? 24.01.2005
- Как создать и применить свой собственный список автозаполнения ?
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.
В меню Правка выберите пункт Заполнить, далее выберите команду Прогрессия.
В диалоговом окне Прогрессия и установите переключатель напротив текста автозаполнение
в разделе Тип и нажмите кнопку ОК.
|
|