Использование списков для ввода данных в Excel

Ввод данных в Excel через форму

Множество разнообразных компьютерных программ, включая «самую главную программу в мире» — MS Windows, ведут общение с пользователем при помощи выпадающих диалоговых окон. Эти окна представляют собой формы, состоящие из надписей, изображений, полей для.

. ввода данных, флажков, переключателей, списков, кнопок и прочих элементов управления.

Задача формы – выпадающего окна – предоставить пользователю простой, понятный и удобный интерфейс, который обеспечит в режиме диалога возможность эффективно осуществлять ввод данных пользователем в программу и вывод программой результатов для считывания пользователем.

Стандартно при работе со значительными объемами информации вне зависимости от того, какое программное обеспечение используется, поступают следующим образом:

1. Создают таблицы базы данных.

2. Создают формы для ввода данных в таблицы.

3. Создают необходимые запросы к таблицам базы данных.

4. Формируют отчеты на основании запросов для вывода на печать.

В предыдущих статьях этого цикла мы рассмотрели создание таблицы базы данных и познакомились с некоторыми механизмами создания запросов и отчетов: с сортировкой данных, с автофильтром и расширенным фильтром.

В этой (пятой в цикле) статье рассмотрим п.2 вышеизложенного алгоритма – вызов и использование формы для ввода данных.

Форма Excel. Вводим данные!

Для чего нужна какая-то форма?! Бери таблицу — и пиши прямо в нее очередную строку информации! Зачем еще что-то выдумывать?

Мы так и делали при создании нашего небольшого и простого примера базы данных БД2 «Выпуск металлоконструкций участком №2», с которым работаем во всех статьях этого цикла.

Таблица маленькая, все столбцы-поля, относящиеся к одной записи, помещаются на экран и доступны взгляду пользователя без использования полос прокрутки. Но так бывает редко! Реальные таблицы баз данных часто содержат не шесть, как в нашем примере, а десять, двадцать и более столбцов-полей!

При вводе данных в такие таблицы Excel «напрямую» при заполнении строк для перемещения по столбцам необходимо каждый раз нажимать клавишу «Tab», а для перехода к новой записи на новую строку пользоваться прокруткой и мышью или клавишами «Enter» и «<—» («стрелка влево») для возврата на первое поле очередной строки.

Справедливости ради следует уточнить, что переходы по таблице, являющейся именованным диапазоном и объявленной списком, как в нашем примере, осуществляются только при помощи клавиши «Tab». Дойдя до последнего столбца у правой границы списка и нажав в очередной раз клавишу «Tab», мы перейдем на следующую строку и первый столбец у левой границы списка.

Главным и почти единственным преимуществом использования формы при вводе данных, на мой взгляд, является возможность видеть одновременно все столбцы-поля одной записи на экране, расположенными построчно.

Еще одним небольшим, но несомненным плюсом ввода данных через форму является то, что такая работа развивает у начинающего пользователя интуитивное восприятие «оконного» интерфейса.

Задача №8:

Ввести очередную строку информации в базу данных через форму.

Рассмотрим ввод данных в Excel при помощи формы на примере добавления очередной записи в базу данных БД2 «Выпуск металлоконструкций участком №2».

1. Открываем в MS Excel файл database.xls.

2. Активируем («щелкаем мышкой») любую ячейку внутри таблицы базы.

3. Выполняем команду главного меню программы «Данные» — «Форма…».

4. В появившемся окне, представляющем собой форму с именем «БД2» (по имени листа книги Excel, на котором расположена таблица), нажимаем кнопку «Добавить».

5. Заполняем окна данными новой записи, например, так, как показано ниже на снимке экрана. Переходим от окна к окну при помощи клавиатуры, нажимая клавишу «Tab».

6. Нажимаем кнопку формы «Закрыть» или «Enter» на клавиатуре. Ввод данных в Excel успешно осуществлен — новая запись добавлена в базу!

На снимке экрана в таблице базы данных внизу расположилась строка «Итог», которую можно вызвать, выполнив команду главного меню «Данные» — «Список» — «Строка итогов». Пощелкайте мышкой по ячейкам этой строки! В появляющихся выпадающих списках можно настроить получение самых разнообразных итогов. Кроме банальной суммы можно получить для каждого столбца индивидуально: среднее, количество, количество чисел, максимум, минимум, смещенное отклонение, смещенную дисперсию! Очень полезная функция списка!

Если в форме «БД2» воспользоваться полосой прокрутки или кнопками «Далее» и «Назад», то можно, быстро перемещаясь по базе данных, просматривать интересующие записи.

Итоги.

Ввод данных в Excel через форму, являющийся альтернативой прямой записи информации в таблицу, безусловно, находит своих приверженцев среди специалистов, привыкших работать с большими базами данных и среди операторов, вносящих ежедневно значительные объемы данных в различные базы.

Выбирайте удобный для себя способ ввода данных и знайте, что у вас всегда есть, как минимум, два варианта решения этой задачи. О некоторых преимуществах использования формы уже было сказано выше.

Следующая статья завершит укрупненный обзор темы хранения и управления большими объемами информации в Excel. Вы узнаете о самом важном – о том, ради чего и затевался весь этот цикл статей.

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, пишите отзывы, вопросы и замечания в комментариях внизу страницы.

Работа со списками данных в Excel

Добрый день уважаемый читатель!

Сегодня я хочу поговорить об одной из основных возможностях — это работа со списками данных в Excel. К самим спискам можно отнести практически любые структурированные данные, такие как, номера телефонов, адреса, ФИО, номенклатурные наименования товаров, перечень заведений, поставщики, сотрудники и много-много другой информации, своего рода база данных. Я думаю, с такими данными вы сталкивались, а значится и инструменты для систематизации и анализа таких данных будут очень полезны, особенно при создании дашбордов. По большому счёту от обычной таблицы списки ничем особым не отличаются, за исключением своих размеров, они достаточно велики. При работе со списками используют понятия: для строк – записи, а для столбиков – поля.

Для примера используем список или базу данных сотрудников и их личных данных: Для создания правильных списков данных, с которыми эффективно можно работать, необходимо следовать нескольким правилам, а именно:

• За каждым столбиком должна быть закреплена информация только одного типа. Например, в столбик с данными о днях рождениях вводится только такие данные, с именами сотрудников, только имена, и смешение типов данных недопустимы;
• Информацию лучше всего делить по максимум. Например, ФИО стоить разделить на три разных поля, так как поиск и работа с данными будет легче (по имени можно поздравить в связи с праздником);
• В обязательном порядке каждое поле должно иметь заголовок, несмотря на то, что с многоуровневыми «шапками» Excel не очень умело умеет работать;
• В списке должны отсутствовать пустые строки и столбцы, так как это определяется программой как окончание созданного списка и в дальнейшем создаются проблемы и ошибки при отображении данных;
• Размещение иных данных в стороне от списка не рекомендуются, так как в момент наложения любого из фильтров они будут скрыты.

Для удобства работы и использование списков есть возможность улучшить навигацию по ней, используя закрепление областей или разделения рабочего окна.

При работе с объёмными таблицами любой пользователь сталкивается с проблемой, когда заголовки полей уходят за пределы окна и в связи с этим появляется неудобства пользования. Для избегания этого необходимо закрепить «шапку» нашей таблицы, что бы полоса прокрутки не влияла на отображения первых строк и столбцов ваших списков. Это можно осуществить в панели управления, вкладка «Вид», блок «Окно» и нажать выпадающий список «Закрепить область».

Вариантов закрепить область прокрутки всего три, это:

1. Закрепить область – производится закрепление области слева и сверху от текущей ячейки, то есть по горизонтали и вертикали одновременно;
2. Закрепить верхнюю строку – закрепление по горизонтали сверху установленной ячейки таблицы;
3. Закрепить первый столбец – произвести вертикальное закрепление списков слева от установленной ячейки.

Также существует возможность разделения рабочей области одновременно на четыре части для независимой работы и прокручивания данных, получая возможность одновременно работать и в начале и в конце списка. Для разделения вам необходимо на панели управления, во вкладке «Вид», в блоке «Окно» нажать кнопку «Разделить», предварительно установив курсор на ячейку, по границам которой и будет происходить разделение. Отключить разделения можно повторно нажав на туже самую кнопку. Сами же данные в списках, возможно, отбирать, используя несколько инструментов, выбор которых зависит от ваших целей. Для этих задач можно использовать:

Отбор с помощью фильтра

Excel предоставляет возможность отфильтровать ваши списки по заданным критериям двумя вариантами, с помощью:

1. Расширенного фильтра;
2. Автофильтра.

Если вы будете использовать автофильтр, то данные, которые не соответствуют указанным условиям, будут спрятаны. В документе отражаться будут только отобранные по критериям записи в одном столбике.

Способностей расширенного фильтра гораздо больше, что позволит делать отбор записей по нескольким условиям и даже переносить их в другой диапазон рабочей книги.

Детально работу с фильтрами, я описал в статье «Автофильтр в Excel» с которой вы можете ознакомиться, перейдя по соответствующей ссылке.

Создаем промежуточные отчёты

Очень часто возникает необходимость группировать данные списков по определенным показателям с расчётами по ним итогов. Так создаются удобные и очень полезные в работе отчёты и анализы, прекрасный инструмент для любого бухгалтера и экономиста. Создать такой детализированный список данных в Excel с выделением групп и подбитием итогов по группам и общий по полю, не очень трудно. Всё это можно произвести в несколько шагов, но обязательным условием применения промежуточных итогов к спискам, это сортировка данных по полю для которого создается итог. Подробно и в деталях об этом можно узнать, прочитав статью «Промежуточные итоги в Excel», перейдя по ссылке.

Сортируем свои списки

При формировании своих списков, практически всегда информация формируется в произвольном порядке, что затрудняет ее эффективное использование. Для избежания этого и удобства работы, в Excel существует инструмент «Сортировка». Запуск производится в панели инструментов, вкладка «Данные» и кнопка «Сортировка». В появившемся диалоговом окне вы можете выбрать поле сортировки, установить любой приоритет для очередности сортировки, а также выбрать критерии (убывание или возрастание). Детально о возможностях этого инструмента можете описано в статье «Сортировка данных в Excel».

Работаем со сводными таблицами

Это улучшенная версия промежуточных итогов и условного форматирования. А более точно, это своеобразное подобие перекрестных запросов по базе данных. Сводные таблицы являют собой динамические структуры, которые умеют выводить данные содержащиеся в БД в разных вариантах детализации при использовании срезов.

Для создания сводных таблиц данные могут отбираться из любых источников: из внешних баз данных, с любого рабочего листа или книги, а также на основе ранее созданной сводной таблицы.

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

Группируем элементы таблицы

Иногда для удобства навигации по вашей базе данных или для сворачивания некоторых элементов таблицы можно использовать инструменты «Группировка» и «Разгруппировать». Эта возможность позволит свернуть данные, которые в данный момент вам не интересны и отображать их не стоит. Очень полезный инструмент, когда возникает необходимость создать диапазон печати для некоторых данных.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное о работе со списками данных в Excel вам пригодилось и было понятным. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Создание или удаление настраиваемого списка для сортировки и подстановки данных

Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В Excel есть встроенные списки дней недели и месяцев года, но вы можете создавать и свои настраиваемые списки.

Чтобы понять, что представляют собой настраиваемые списки, полезно ознакомиться с принципами их работы и хранения на компьютере.

Сравнение встроенных и настраиваемых списков

В Excel есть указанные ниже встроенные списки дней недели и месяцев года.

Пн, Вт, Ср, Чт, Пт, Сб, Вс

Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье

янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек

Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь

Примечание: Изменить или удалить встроенный список невозможно.

Вы также можете создать свой настраиваемый список и использовать его для сортировки или заполнения. Например, чтобы отсортировать или заполнить значения по приведенным ниже спискам, нужен настраиваемый список, так как соответствующего естественного порядка значений не существует.

Высокое, Среднее, Низкое

Большое, Среднее, Малое

Север, Юг, Восток, Запад

Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель

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

Примечание: Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.

Создать настраиваемый список можно двумя способами. Если список короткий, можно ввести его значения прямо во всплывающем окне. Если список длинный, можно импортировать значения из диапазона ячеек.

Введение значений напрямую

Чтобы создать настраиваемый список этим способом, выполните указанные ниже действия.

В Excel 2010 и более поздних версиях выберите пункты Файл > Параметры > Дополнительно > Общие > Изменить списки.

В Excel 2007 нажмите кнопку Microsoft Office и выберите пункты Параметры Excel > Популярные > Основные параметры работы с Excel > Изменить списки.

Выберите в поле Списки пункт НОВЫЙ СПИСОК и введите данные в поле Элементы списка, начиная с первого элемента.

После ввода каждого элемента нажимайте клавишу ВВОД.

Завершив создание списка, нажмите кнопку Добавить.

На панели Списки появятся введенные вами элементы.

Нажмите два раза кнопку ОК.

Создание настраиваемого списка на основе диапазона ячеек

Выполните указанные ниже действия.

В диапазоне ячеек введите сверху вниз значения, по которым нужно выполнить сортировку или заполнение. Выделите этот диапазон и, следуя инструкциям выше, откройте всплывающее окно “Списки”.

Убедитесь, что ссылка на выделенные значения отображается в окне Списки в поле Импорт списка из ячеек, и нажмите кнопку Импорт.

На панели Списки появятся выбранные вами элементы.

“Параметры” > “Дополнительно” > “Общие” > “Изменить списки”. Если вы используете Excel 2007, нажмите кнопку Office и выберите “Параметры Excel” > “Популярные” > “Изменить списки”.” />

Два раза нажмите кнопку ОК.

Примечание: Настраиваемый список можно создать только на основе значений, таких как текст, числа, даты и время. На основе формата, например значков, цвета ячейки или цвета шрифта, создать настраиваемый список нельзя.

Выполните указанные ниже действия.

По приведенным выше инструкциям откройте диалоговое окно “Списки”.

Выделите список, который нужно удалить, в поле Списки и нажмите кнопку Удалить.

Настраиваемые списки добавляются в реестр компьютера, чтобы их можно было использовать в других книгах. Если вы используете настраиваемый список при сортировке данных, он также сохраняется вместе с книгой, поэтому его можно использовать на других компьютерах, в том числе на серверах с Службы Excel, для которых может быть опубликована ваша книга.

Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки в параметрах Excel: его можно выбрать только в столбце Порядок диалогового окна Сортировка. Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для команды Заполнить.

При необходимости можно добавить такой список в реестр компьютера или сервера, чтобы он был доступен в Параметрах Excel во всплывающем окне Списки. Для этого выберите во всплывающем окне Сортировка в столбце Порядок пункт Настраиваемый список, чтобы отобразить всплывающее окно Списки, а затем выделите настраиваемый список и нажмите кнопку Добавить.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.