Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как назвать листы на основе значений ячеек (из списка) в Excel?

Как сделать выпадающий список в Excel

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

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

Создаем простой выпадающий список

Для этого введите данные, которые вы хотите видеть в списке, в ячейки A1:A7. Теперь выберите ячейку, в которой вы хотите создать выпадающий список — B2.

Как сделать выпадающий список в Excel (Эксель)

Перейдите на вкладку «Данные» и нажмите на кнопку «Проверить данные».

Выберите «Список» в поле «Тип данных» на вкладке «Параметры». В поле «Источник» можно вводить значения различными способами:

1 — введите значения из списка вручную, разделяя их точками с запятой;

2 — указать диапазон ячеек, в которые вводятся данные для выпадающего списка;

Щелкните правой кнопкой мыши на ячейках, которые уже имеют имена, и выберите «Присвоить название».

Затем введите «Имя» помеченных данных.

Выделите ячейку B2 и введите «=» в поле «Источник», а затем имя, которое вы создали.

Мы создали простой раскрывающийся список в Excel.

Заголовок столбца следует использовать, если необходимо заполнить значениями каждую строку. Затем выберите не одну ячейку, а диапазон — B2:B9. Затем вы можете выбрать значение каждой ячейки из выпадающего списка.

Добавляем значения в выпадающий список – динамический список

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

На вкладке Главная нажмите «Форматировать как таблицу» и выберите любой стиль.

Пожалуйста, подтвердите расположение данных и установите флажок в поле «Таблица с заголовками».

В верхней части напишите заголовок таблицы «Сотрудники» и заполните ее данными.

Читайте так же:
Как легко извлечь содержимое комментариев в Excel?

Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных» . В следующем окне, в поле «Источник» , пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]») . У меня одна таблица на листе, поэтому пишу «Таблица1» , если будет вторая – «Таблица2» , и так далее.

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

Выпадающий список со значениями с другого листа

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

На Листе 2 выберите ячейку или диапазон ячеек и нажмите кнопку «Проверка данных».

Установите курсор в поле «Источник» на Листе 1 и выделите нужный диапазон ячеек.

На Листе 2 после добавления имен на Лист 1 они появятся в выпадающем списке.

Создаем зависимые выпадающие списки

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

В английском языке первое называется «First Name», а второе — «Last Name». «Last name» — второе, «report» — третье. Третье — «Отец».

Будет создан еще один диапазон, в котором будут прописаны имена. Назовем его «Сотрудники».

Создайте первый выпадающий список, который будет включать имена диапазонов. Чтобы проверить данные в ячейке E1, выберите «Проверить данные» на вкладке «Данные».

Выберите «Список» для поля «Тип данных» и либо поместите «=Employees» в поле источника, либо выберите диапазон ячеек, в которых должно появиться имя.

Читайте так же:
Как изменить регистр текста в Excel?

Первый выпадающий список создан. Теперь необходимо создать второй список в ячейке F2, который зависит от первого. В первом случае мы выбираем «Имя». При выборе второго варианта появится список имен, а при выборе третьего — список фамилий.

Затем нажмите кнопку «Проверка данных» в выбранной ячейке. В поле «Тип данных» выберите «Список», а в поле источника введите =SOURCE($E$1). В данном случае E1 — это первый выпадающий список.

Учитывая этот принцип, можно создавать зависимые выпадающие списки.

Для того чтобы ввести значения в диапазон, которому присвоено имя, например «Фамилия». Перейдите на вкладку «Формулы» и нажмите кнопку «Менеджер имен.». Выберите диапазон «Фамилия» в поле имени. В последней ячейке напишите C10 вместо C3 . Нажмите на галочку. По мере расширения диапазона вы можете добавлять данные, которые будут автоматически появляться в выпадающем меню.

Составление перечней наименований

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

Мы продемонстрируем наш метод решения этой задачи на примере столбца A, в котором содержатся названия факультетов:

1. Выделите диапазон ячеек А2:А11 и скопируйте его содержимое в буфер обмена.
2. Поместите табличный курсор в ячейку А15 и выполните вставку из буфера обмена. Вставку можно произвести практически в любую свободную область листа. Если диапазон выделяемых ячеек достаточно большой, можно скопировать весь столбец и поместить его на чистый рабочий лист.
3. Выделите весь вставленный диапазон ячеек.
4. На стандартной панели управления нажмите кнопку Сортировка по возрастанию или Сортировка по убыванию. В результате этой операции все названия отделов будут упорядочены в порядке возрастания или убывания по алфавитному признаку.

Читайте так же:
Как найти конкретное значение в объединенных ячейках в Excel?

Рис. 5.30. Алгоритм именования

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

И нажмите [Ctrl+Enter] (при этом формула отобразится во всех ячейках диапазона). Если две вертикально соседние ячейки имеют одинаковое содержимое, функция возвращает значение ЯЯЯ. В противном случае возвращается значение в ячейке ниже. (Буквы «YAYA» используются потому, что маловероятно, что такое имя существует в списке).

6. Скопируйте ячейки из диапазона В15:В24 в буфер обмена, а затем вставьте их в предварительно выделенную ячейку С15. Эту операцию необходимо выполнить посредством диалогового окна Специальная вставка, нажав в нем переключатель Значения. В диапазоне С15:С24 теперь будут находиться не формулы, а значения.
7. Выполните сортировку указанного диапазона ячеек, нажав кнопку Сортировка по возрастанию. Ячейки со значениями ЯЯЯ будут расположены в нижней части области, а названия отделов — в верхней.
8. Выделите диапазон ячеек с названиями отделов и вставьте полученный таким образом список в нужное место таблицы. В примере на рис. 5.2 это ячейки G2:G4.

Рисунок 5.31. Внешний вид листа Excel после операции выборки.

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

Читайте так же:
Как легко и быстро преобразовать таблицу PDF в таблицу Excel?

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

Рис.6.Создание структуры

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

Рис. 7. Результат операции группировки

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

Эксель-регал

Сводные таблицы позволяют группировать данные из списков, из нескольких диапазонов консолидации или из внешних баз данных. Мастер сводных таблиц появляется, когда вы нажимаете на пункт меню Данные под названием Сводная таблица (Рисунок 8).

Рис.8. Мастер кросстаба — Шаг 1.

Перейдите ко второму шагу мастера после выбора первого варианта из списка. На втором шаге выберите список, который вы хотите обработать, а затем перейдите к третьему шагу. Третий шаг мастера (рис. В конструкторе обработки данных списка выполняется обработка данных списка.

Рис.10.Перекрестная таблица — Шаг 3.

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

Читайте так же:
Как изменить указанное значение или цвет ячейки при установленном флажке в Excel?

Рис.11. Конструирование групповой операции по полю «Вес»

Чтобы убрать поле из области проектирования, перетащите его из области.

Нажмите «Далее», чтобы перейти к четвертому шагу мастера. Четвертый шаг — выбрать лист, на котором нужно разместить поворотную таблицу. Здесь вы можете выбрать создание сводной таблицы на новом листе и завершить работу мастера. Полученная сводная таблица показана на рисунке 12.

Рис.12.Сводная таблица, полученная в результате работы мастера

Изменение выбора на панели «Сводные таблицы» на групповую операцию возвращает вас к мастеру перекрестных таблиц. Измените запрос, добавив поле Фамилия в область данных. Сводная таблица будет выглядеть так, как показано на рис. 13.

Рис.13. Сводная таблица с групповой операцией по полю «Фамилия»

Возвращаясь к мастеру создания перекрестной таблицы, добавьте поле Пол в область Столбец и поле Возраст в область Страница. Как видно на рисунке 14, в результате получилась перекрестная таблица.

Рис.14.Результирующая сводная таблица

Поле ‘Страница’ отличается от полей ‘Строка’ и ‘Колонка’ тем, что позволяет выполнять групповую операцию над всеми значениями поля сразу или над одним выбранным значением. В приведенном выше примере выбрано значение «19».

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector