Построение иерархических списков в электронных таблицах Текст научной статьи по специальности «Компьютерные и информационные науки»

S «

s §

X

§

л s о

X X

w

SS

Он

со О

и

W

£ о

С

W

3 х

л

<

й

H «

УДК 004.9

построение иерархических списков в электронных таблицах

Сальников Игорь Алексеевич

Санкт-Петербургский имени В.Б. Бобкова филиал Российской таможенной академии, заведующий кафедрой информатики и информационных таможенных технологий, e-mail: igor.sal.spb@yandex.ru

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

Ключевые слова: электронные таблицы; иерархические списки; связанные списки; функции; электронная форма

CONSTRUCTING HIERARCHICAL LISTS IN SPREADSHEETS

Salnikov Igor A.

Russian Customs Academy St.-Petersburg branch named after Vladimir Bobkov, Head of Department of Computer Science and IT-supported Information Customs Technologies, PhD, Docent, e-mail: igor.sal.spb@yandex.ru

The article presents the construction technologies of simple and hierarchical lists, differing by the degree of complexity, ease of use in everyday work, as well as restrictions imposed on the conditions of their possible use

Keywords: spreadsheets; hierarchical lists; linked lists; functions; electronic form

Для цитирования: Сальников И.А. Построение иерархических списков в электронных таблицах // Ученые записки Санкт-Петербургского имени В.Б. Бобкова филиала Российской таможенной академии. 2019. № 3 (71). С. 64-69.

Для анализа данных, преимущественно представленных в числовой форме, как правило, применяются электронные таблицы. Для зации массивов ячеек в электронных таблицах используются различные приемы, например, именование диапазона ячеек, объявление диапазона ячеек таблицей, создание списков и форм ввода данных, связывание таблиц в единую базу данных, использование средств программирования языка Visual Basic [1, 2].

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

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

и фильтровать таблицы данных, строить для них итоговые отчеты в виде сводных таблиц, решать задачи оптимизации и обратные задачи вида x = f -1 (y), визуализировать зависимость, существующую между рядами данных и т.д., можно утверждать, что Microsoft Excel является мощной вычислительной средой, которая во многих случаях способна исключить программирование как способ решения задач.

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

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

Рассмотрим типовые ситуации, возникающие в процессе построения электронных таблиц, и проанализируем способы их разрешения.

Ситуация 1. В ячейки таблиц часто требуется вводить одни и те же данные.

Решение 1.1. Простейшее решение заключается в повторном вводе данных либо копировании

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

В ситуации, когда требуется ввести большой по объему текст (обозначим как Т), целесообразно создать для него элемент автозамены (обозначим как И) - короткое информативное имя. После ввода И Microsoft Excel автоматически заменит его на Т. Недостаток данного решения заключается в необходимости помнить все имена элементов автозамены.

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

Для формирования всплывающего списка достаточно одновременно нажать клавиши «Alt» и «I». Отметим, что в списке не будут показаны повторяющиеся элементы, что облегчает выбор.

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

Решение 1.3.

Первый вариант: создать список, выделить ячейки с элементами списка. Выполнить команды:

Файл ^ Параметры Excel ^ Дополнительно ^ Общие ^ ,

Создать списки для сортировки и заполнения ^ ( ) Изменить списки ^ Импорт ^ ОК ^ ОК

В результате элементы списка будут записаны в шаблон (сохранятся в памяти) Microsoft Excel. Чтобы воспроизвести элементы списка в любой рабочей книге, достаточно записать в ячейку один из элементов списка и протянуть ячейку за правый нижний угол.

Стандартными элементами списка являются полные и сокращенные названия месяцев и дней недели.

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

Второй вариант: сначала выполнить последовательность команд (1) до пункта «Импорт», затем ввести элементы списка и дважды нажать кнопку «ОК».

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

Решение 1.4.

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

Данные ^ Проверка данных ^ Тип данных ^ Список ^ Источник ^ <Выделить диапазон ячеек с элементами списка> ( 2 ^ ОК

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

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

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

Второй вариант: создать список, выделить ячейки, в которых требуется показывать элементы списка, затем выполнить команды (2).

Аналогично Решению 1.3, предлагается создать макрос, записав в него последовательность команд (2).

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

Формулы ^ Определенные имена ^ , ,

Диспетчер имен (3)

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

Решение 1.5. Настроить ленту, добавив на нее вкладку «Разработчик». Создать список, перейти на вкладку «Разработчик» и выполнить команды:

Вставить ^ Элементы управления формы ^ , , Поле со списком ( 2

Обвести границы ячейки, в которой создается раскрывающийся список, и щелчком мыши вызвать контекстное меню поля со списком «Формат объекта». В окне «Формат элемента управления» на вкладке «Элемент управления» в поле «Формировать список по диапазону» необходимо задать диапазон ячеек, содержащих список, в окне «Связь с ячейкой» - ячейку, в которой будет выводиться порядковый номер выбранного элемента управления, и нажать кнопку «ОК» (рис. 1).

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

Рис. 1. Построение списка с использованием элемента управления формы

контекстное меню данного объекта, что усложняет и замедляет процесс ввода элементов списка.

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

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

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

Анализировать состояние элемента управления формы без привлечения средств системы программирования сложно [3].

Для решения проблемы достаточно воспользоваться функцией ИНДЕКС, которая выводит элемент по его номеру в списке.

Например, применительно к данным, показанным на рис. 1, в ячейке Е1 запишем функцию =ИНДЕКС(Л2:Л4;Б1;1). В результате, если в поле

со списком, закрывающем ячейку С1, выбрать, допустим, элемент списка «Петров П.П.», тогда в ячейке D1 отобразится число 2 - порядковый номер данного элемента, а в ячейке E1 - выбранный элемент списка «Петров П.П.» (рис. 2).

Заметим, что элемент «Поле со списком» не привязан к какой-либо ячейке и может быть перемещен в любое место рабочей области рабочего листа.

Решение 1.6. Настроить ленту, добавив на нее вкладку «Разработчик», если она отсутствует. Создать список, перейти на вкладку «Разработчик» и выполнить команды:

Вставить ^ Элементы ActiveX ^ Поле со списком (5)

Обвести границы ячейки, в которой создается раскрывающийся список, и вызвать контекстное меню поля со списком «Свойства». В окне «Properties» (Свойства) на вкладке «Элемент управления» в столбце «Name» (Имя свойства) необходимо найти свойство «ListFillRange» (Область списка). В поле справа (Значение свойства) необходимо щелкнуть мышью, указав позицию ввода, записать диапазон ячеек, в которых находятся элементы списка, например, A2:A4 (символы латиницы). Аналогичным образом найдем свойство «LinkedCell»

Рис. 2. Выбор элемента списка и вывод его атрибутов

(Связанная ячейка) и укажем поле справа адрес ячейки, в которую будет выведен выбранный элемент списка, например, E4. Ввод значений свойств всегда следует завершать нажатием клавиши «Enter» или переключением на другие свойства объекта.

После вставки поля со списком, которое фактически является объектом типа Combobox, на вкладке «Разработчик» автоматически включается режим конструктора, позволяющий настраивать свойства объектов ActiveX. Вместо вызова контекстного меню объектов достаточно нажать кнопку «Свойства элемента управления», расположенную в группе кнопок «Элементы управления». Чтобы воспользоваться построенным списком, необходимо на вкладке «Разработчик» отключить режим конструктора.

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

Ситуация 2. Рассмотрим технологии построения иерархических списков, имеющих место в случае, когда после выбора элемента из одного (главного) списка второй (подчиненный) список формируется в зависимости от выбора, сделанного в главном списке.

Примеры ситуаций:

Из списка подразделений таможни (главного списка) требуется выбрать одно из подразделений X, а из списка работников (подчиненного списка) -конкретного работника Y(X). При этом если в первом списке выбран, например, отдел кадров, то подчиненный список должен формироваться только из работников отдела кадров.

Из списка марок автомобилей требуется выбрать некоторую марку X, а во втором списке, состоящем только из наименований моделей выбранной марки, - конкретную модель Y(X).

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

Выберем две ячейки для построения главного списка. В одной из них запишем название списка «Марка». Выделим в таблице заголовок, который содержит элементы главного списка и присвоим ему имя «Марка» в поле «Имя». Во второй ячейке главного списка создадим список марок автомобилей, используя кнопку «Проверка данных», выбрав в качестве источника данных список «Марка» (Решение 1.4, третий вариант).

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

Рис. 3. Построение иерархических списков

воспользуемся функцией ДВССЫЛ, выбрав текст в качестве аргумента (рис. 3).

Таким образом, если в главном списке выбрать, например, марку «Lada», то в подчиненном списке появятся только наименования отечественных моделей: Granta, Largus, Xray и Vesta.

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

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

Решение 2.3. Предположим, что исходная таблица, используемая для построения иерархических списков, имеет вид двух столбцов с именами «Марка» и «Модель». Поскольку Microsoft Excel работает с упорядоченными таблицами, необходимо выполнить двухуровневую сортировку таблицы: сначала по первому столбцу, затем - по второму. В результате сортировки в начале первого столбца появятся

повторяющиеся названия одной модели, затем -названия второй модели и наконец, названия третьей модели.

В произвольной ячейке создадим список, состоящий из уникальных (неповторяющихся) значений из столбца «Марка». Воспользуемся данным списком в качестве источника для построения главного списка «Марка».

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

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

Первый параметр функции СМЕЩ - адрес начальной ячейки: достаточно указать самую верхнюю левую ячейку таблицы, например, А2.

Второй параметр функции - сдвиг относительно начальной ячейки вниз, чтобы перейти

Рис. 4. Определение элементов подчиненного списка

к марке автомобилей, выбранной в главном списке. Он вычисляется с помощью функции ПОИСКПОЗ. Ее аргументы: марка автомобиля из главного списка, диапазон, в котором необходимо искать марку автомобиля, и число 0 для поиска точного совпадения.

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

Четвертый параметр - количество строк, в которых производится поиск, выполняется с помощью функции СЧЁТЕСЛИ. Первый аргумент данной функции - диапазон поиска (весь столбец), а второй аргумент - элемент, выбранный в главном списке.

Пятый параметр - количество столбцов, которые необходимо вывести в подчиненном списке, равен 1.

Правило вычисления динамического диапазона ячеек показано на рис. 4.

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

Таким образом, в статье представлены технологии построения простых и иерархических списков,

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

Библиографический список:

1. Павлов Н.В. Microsoft Excel: Готовые решения - бери и пользуйся! М.: Книга по требованию, 2014. 382 с.

2. Павлов Н.В. Microsoft Excel: Мастер формул. Подробное руководство по «высшему пилотажу» в формулах и функциях. М.: Книга по требованию, 2017. 240 с.

3. Сальников И.А., Сальников В.И. Алгоритмизация расчета таможенных платежей при ввозе товаров на таможенную территорию Таможенного союза физическими лицами // Ученые записки Санкт-Петербургского имени В.Б. Бобкова филиала Российской таможенной академии. 2018. № 2 (66). С. 102-108.