CC BY
18УДК 004.65:339.5
ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ В КАЧЕСТВЕ ИНСТРУМЕНТА ДЛЯ ПОСТРОЕНИЯ СИСТЕМЫ УПРАВЛЕНИЯ БАЗОЙ ДАННЫХ НА ПРИМЕРЕ УЧЕТА ВНЕШНЕТОРГОВЫХ ОПЕРАЦИЙ Сальников И.А.
Санкт-Петербургский имени В.Б. Бобкова филиал Российской таможенной академии
THE USE OF SPREADSHEETS AS A TOOL FOR BUILDING THE DATABASE MANAGEMENT SYSTEM ON THE EXAMPLE OF THE ACCOUNTING OF
FOREIGN TRADE OPERATIONS
Salnikov I.A.
St. Petersburg named after V.B. Bobkova branch of the Russian Customs Academy
Аннотация
Малый бизнес является основой экономической стабильности государства. Декларируемый в Российской Федерации значительный рост данного сектора экономики обостряет проблему ведения хозяйственного учета на малых предприятиях. Внедрение и сопровождение сложных коммерческих программных продуктов неприемлемо для большинства малых предприятий.
В статье показана методика применения перспективной информационной технологии, использующей доступное и широко распространенное программное обеспечение, на примере учета внешнеторговой деятельности предприятия малого бизнеса. Представлены методы связывания электронных таблиц и алгоритмы выборки данных, имитирующие работу простейшей системы управления базой данных.
Ключевые слова: информационные технологии, электронные таблицы, база данных, межтабличные связи, малый бизнес.
Abstract
Small business is the basis of economic stability of the state. The significant growth of this sector of the economy declared in the Russian Federation exacerbates the problem of economic accounting at small enterprises. Implementation and maintenance of complex commercial software products is unacceptable for most small businesses.
The article shows the method of application of advanced information technology, using affordable and widespread software, on the example of accounting for foreign trade activities of small businesses. The methods of linking spreadsheets and data sampling algorithms that simulate the operation of a simple database management system are presented.
Keywords: information technologies, spreadsheets, database, inter-table relations, small business.
Президент Российской Федерации В.В. Путин 1 марта 2018 года в Послании Федеральному Собранию отметил развитие малого предпринимательства в качестве одного из важнейших масштабных резервов экономического роста страны [1]. По данным сайта Investing.com со ссылкой на Рос-стат, доля малого и среднего бизнеса в России в 2017 году составила 21,9% от ВВП страны [2]. К сравнению, по данным Института экономического развития имени Г.В. Столыпина, в развитых странах мира, где малый бизнес является основой экономической стабильности государства, данный показатель находится в пределах от 48 до 68% [3].
Проведение предприятиями торговых сделок, связанных с оборотом товаров и
услуг, сопровождается фиксацией торговых операций с использованием персональных компьютеров. Нетрудно предположить, что основным программным инструментом, применяемым в компьютерах большинства малых предприятий, служит приложение Microsoft Excel. Малое предприятие имеет ограниченную численность работников и, как правило, не в состоянии финансировать приобретение, настройку и сопровождение сложных программных систем или заказного программного обеспечения.
Компания Microsoft, несмотря на наличие в сети Интернет свободно распространяемых аналогов приложения Excel, включая облачное приложение Google Tables, в последние несколько лет проводит актив-
ную политику по совершенствованию своего программного продукта. Для Excel разрабатываются и распространяются бесплатно и на коммерческой основе так называемые надстройки - дополнительные программные модули. Они позволяют автоматизировать решение типовых задач, обрабатывать большие наборы данных, подключаться к различным серверам баз данных для загрузки (импорта) и использования достоверных данных глобального характера в электронных таблицах пользователей, а также выгружать электронные таблицы на платформу облачных вычислений Azure для совместного доступа своих корреспондентов.
Кто же является целевой аудиторией данных разработок? Крупные предприятия и организации, имеющие дело с гигантскими массивами сложным образом взаимосвязанных данных, вынуждены заботиться об эффективном размещении данных на внешних носителях для обеспечения целостности и достоверности данных, приемлемой скорости выборки и обработки данных, должны учитывать возможность реструктуризации данных, доступа к данным по различным каналам связи и т.п. Ярким примером подобных информационных массивов являются базы данных, формируемые Федеральной таможенной службой Российской Федерации. Безусловно, в условиях необъявленной информационной войны остро стоит актуальный вопрос информационной безопасности как отдельных предприятий и организаций, так и всей страны в целом. Очевидно, решение перечисленных выше задач может быть достигнуто только путем использования промышленных систем управления базами данных (СУБД) типа Oracle и SQL Server.
Таким образом, новые функции и инновационные технологии приложения Excel актуальны для среднего бизнеса. Перед малым бизнесом чаще стоит проблема выживания в сложной конкурентной среде, чем отслеживание и освоение новых информационных технологий. Тем не менее, в Excel заложено немало неизвестных рядовым пользователям функций и информационных технологий, освоение которых позволяет реализовать широкий спектр операций с данными, см., например, [4], упростить проведение учетных операций в малом бизнесе.
Проведенные исследования. В данной статье представлена информационная технология построения простейшей СУБД
средствами приложения Excel. В качестве предметной области выбрана распространенная область учета внешнеторговых операций некоторого малого предприятия, осуществляющего закупку (импорт) сельскохозяйственной продукции за рубежом. При желании размеры и количество таблиц могут быть увеличены, а их расположение на рабочих листах - изменено. Данная технология основана на построении модели данных и реализуется типовыми или специальными средствами Excel, начиная с редакции 2013 года.
Предварительно следует заметить, что в терминологии Excel любая электронная таблица (совокупность смежных строк или столбцов) называется базой данных. Более того, если такую базу данных определенным образом выделить среди других, она становится «настоящей» таблицей, точнее, так называемой «умной» таблицей, получает имя и приобретает некоторые полезные качества. Поскольку технологии приложения Excel приближаются к технологиям СУБД, а не наоборот, в данной статье под базой данных будем понимать совокупность взаимосвязанных «умных» электронных таблиц.
Вначале, как предполагает процесс построения некоторой базы данных, необходимо выполнить анализ предметной области и спроектировать ее информационно-логическую структуру данных. На основе анализа учетных данных требуется выбрать атрибуты данных, подлежащих хранению в базе данных (столбцы), сгруппировать их в объекты (таблицы) и установить связи между ними.
Построение таблиц. Допустим, решено хранить объект «Поставщик» с атрибутами «Название фирмы», «Страна», «Адрес/контакт», «Менеджер», «Телефон». Данная таблица является базовой (независимой, справочной). Ее особенность состоит в том, что ее первый столбец «Название фирмы» должен быть первичным ключом, т.е. хранить уникальные, неповторяющиеся записи, отсортированные в порядке от А до Я.
Создадим таблицу для объекта «Поставщик». Сортировка столбца выполняется после его заполнения или после заполнения всей таблицы. Наконец, преобразуем полученную таблицу в умную таблицу, для чего сделаем активной любую ячейку таблицы и выполним команды:
ВСТАВКА ^ Таблицы ^ Таблица
или
Ctrl + T
1 Название фирмы Hi Страна В Адрес/Контакт В Менеджер □
2 Agro Bene, GKT Турция Altinova Sinan Mahallesi ANSO Sok.No229 Kepez Antalya Kadiroglu Railya
3 Al rehab со, LLC Египет Каир, проспект Салах Салем, умаратэл-убур5, этаж 15, офис 7 Мекки Надер
4 Elbardy for Export and import. Branch Египет Giza, City Star Towers No. 6, fourth floor, office No. 5-6 Gomaa Nadiay
5 Premium Group, LS Турция Cankaya mah. Ataturk cad. 26/122 Akdeniz Mersin Карпова Вероника
6 SaadatFood Industries Пакистан Area Mohniroad 04100 Sargodha Punjab
7 Аврасторг Белоруссия [email protected] Диана Петрикина
8 Сула Плюс, ООО Белоруссия [email protected] Соболь Маргарита
Рис. 1. Таблица «Поставщик»
I Наименование товара | D Отпускная цена, руб. В Групп a Q
Банан 45 Фрукты
Картофель 20 Овощи
Лук 35 Овощи
Мандарин 70 Фрукты
Слива 65 Фрукты
Томат 80 Овощи
Финик 100 Фрукты
Яблоко 50 Фрукты
Клубника 120 Ягоды
Малина 170 Ягоды 11
Рис. 2. Таблица «Товар»
или
ГЛАВНАЯ ^ Форматировать как таблицу.
Подтвердим, что таблица имеет заголовки, и при необходимости изменим ее автоматический формат. В поле «Имя» раскрыв список, увидим имя, например, «Таб-лица1». Выделим ячейки таблицы без заголовков, перейдем на появившуюся вкладку КОНСТРУКТОР и в блоке «Свойства» изменим имя таблицы на «Поставщик» (рис. 1).
Таким же требования предъявляются к объекту «Товар» с атрибутами «Наименование товара», «Отпускная цена, руб.», «Группа». Будем подразделять товар на три группы: овощи, фрукты, ягоды. Создадим таблицу для объекта «Товар» и преобразуем ее в умную таблицу с именем «Товар» (рис. 2).
Заметим, что связь между таблицами «Поставщик» и «Товар» неопределенная: один поставщик может продавать несколько видов товара, с другой стороны, один и тот же вид товара может быть приобретен у разных поставщиков. Чтобы разрешить данную неопределенность, в информационно-логическую структуру данных введем новый, вспомогательный объект «Поставки». Предположим, что он содержит атрибуты «Дата», «Количество, кг», «Стоимость».
Для того, чтобы связать таблицы «Поставщик» и «Поставки», введем в структуру таблицы «Поставки» столбец «Название
фирмы». Он будет служить внешним («чужим») ключом, содержащим значения из соответствующего столбцы таблицы «Поставщик». Значения во внешнем ключе могут повторяться.
Для того, чтобы в таблице «Поставки» создать подстановку записей из таблицы «Поставщики», выделим столбец «Название фирмы» (без заголовка) и выполним команды:
ДАННЫЕ ^ Работа с данными ^ Проверка данных
В поле «Тип данных» в раскрывающемся списке выберем пункт «Список», определив в качестве источника в таблице «Поставщик» ячейки с названиями фирм. При активации ячеек в таблице «Поставки» справа от них появится знак раскрывающегося списка, из которого необходимо сделать выбор. При попытке ввести данное, отличное от элементов списка, выводится сообщение об ошибке, и ввод данного отвергается.
Таким образом, между таблицами образуется связь вида «один ко многим». Это значит, что в столбце «Название фирмы» таблицы «Поставщик» записаны лишь уникальные значения, а в столбце «Название фирмы» таблицы «Поставки» записаны, возможно, многократно, только данные из таблицы «Поставщик», и не могут встречаться иные значения.
Аналогичным образом создается подстановка в таблице «Поставки» в столбце
Дата щНазвание фирмы О Товар Н Количество, кг В Стоимость, руб. Страна
12.06.2019 Premium Group, LS Томат 100 аооо Турция
15.06.2019 Сула Плюс, ООО Картофель 200 4000 Белору
20.06.2019 Saadat Food Industries Мандарин 50 3500 Пакистг
25.06.2019 Аврасторг Лук 100 3500 Белору
27.06.2019 Agro Bene, GKT Томат 100 S000 Турция
15.06.2019 Alrehabco, LLC Яблоко 150 7500 Египет
29.06.2019 Elbardy for Export and import, Branch Финик 100 10000 Египет
30.06.2019 Сула Плюс, ООО Яблоко 200 10000 Белору
31.06.2019 Premium Group, LS Клубника 100 12000 Турция
02.06.2019 Alrehabco, LLC Малина 200 34000 Египет
Рис. 3. Таблица «Поставки»
Дата (несколько элементов) 3
Названия
Сумма по столбцу Стоимость, руб. столбцов В
п Овощи и Фрукты И Ягоды Общий итог
Названия строк D
0 Белоруссия 3500 10000 13500
Аврасторг 3500 3500
Сула Плюс, ООО 10000 10000
d Египет 10000 34000 44000
Alrehab со, LLC 34000 34000
Elbardy for Export and import, Branch 10000 10000
d Пакистан 3500 3500
Saadat Food Industries 3500 3500
dТурция В000 12000 20000
Agro Bene, GKT аооо аооо
Premium Group, LS 12000 12000
Общий итог 11500 23500 46000 81000
Рис. 4. Сводная таблица
«Товар» значений из столбца «Наименование товара» таблицы «Товар». Порядок следования столбцов в таблице «Поставки» не имеет значения.
Вычисляемое поле «Стоимость, руб.» таблицы «Поставки» определяется формулой
= ВПР (№;Товар;2;ЛОЖЬ)*О2, где ВПР - функция, которая ищет заданное значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки;
N2 - адрес ячейки, в которой записано наименование искомого товара;
Товар - имя таблицы, в которой ищется наименование товара, т.е. определяется номер строки, в которой оно записано;
2 - номер столбца «Отпускная цена, руб.» таблицы «Товар», из которого выбирается значение стоимости указанного товара;
ЛОЖЬ - признак того, что поиск производится до точного совпадения наименований товара;
О2 - адрес ячейки, в которой записан вес товара.
Таким образом, если, например, в таблице «Поставки» указан товар «Томат» и вес, равный 100 кг, Excel определит по таблице «Товар» стоимость указанного товара, например, 80 руб./кг, и умножит его на вес, записав в столбец «Стоимость, руб.» значение 8000.
Если, допустим, требуется таблицу «Поставки» дополнить неключевым столбцом, например, «Страна», достаточно в ячейках данного столбца записать функцию вида:
= ВПР (М2;Поставщик;2;ЛОЖЬ)
По названию фирмы, записанному в ячейке с адресом М2, Excel найдет в таблице «Поставщик» номер строки, из которой во втором столбце выберет название страны (см. рис. 3).
Умная таблица, кроме характерного цветового форматирования, позволяет выполнять фильтрацию текстовых и числовых
л А В С D Е F
1 Форма для ввода данных в таблицу "Поставки"
г
3 Дата 09.0S.Z019
4 Фирма Сидоров
5 Товар Нет данных
6 Цена #н/д
7 Количество 0
S Стоимость #н/д
9
10
11 Запись, добавляемая к таблице "Продажи"
12
13 Дата Фирма Товар Количество Стоимость Страна
14 09.08.2019 Нет данных Нет данных 0 г- #н/д Сидоров
15
16
17
18 Внести запись
14
Рис. 5. Форма для ввода данных в таблицу «Поставки»
данных по каждому столбцу, имеет очерченные границы. При добавлении новых строк и столбцов они автоматически включаются в состав умной таблицы, а формулы копируются.
Таким образом, по информационно-логической структуре данных построены и связаны отношениями «один ко многим» три таблицы.
В Excel 2013 года и более поздних редакций вместо подстановки в таблицу «Поставки» столбцов «Название фирмы» и «Товар» достаточно построить модель данных [5]. Для этого выполним команду ДАННЫЕ ^ Работа с данными ^ Отношения.
В диалоговом окне «Управление связями» выберем создание связи между таблицами. В окне «Создание связи» первой укажем зависимую таблицу «Поставки» и ее внешний («чужой») ключ «Название фирмы», в который производится подстановка. Второй укажем базовую таблицу «Поставщик» и ее первичный ключ «Название фирмы». Повторим операцию для второй связи. В окне «Создание связи» первой укажем зависимую таблицу «Поставки» и ее внешний («чужой») ключ «Товар», в который производится подстановка. Второй укажем базовую таблицу «Товар» и ее первичный ключ «Наименование товара».
Построение запроса.
Кроме таблиц с данными, важным элементом СУБД являются запросы, позволяющие делать поиск и выборку данных. В простейшем случае можно использовать фильтрацию данных любой из трех таблиц. Однако наибольший эффект для проведения анализа данных дает использование сводной таблицы и сводной диаграммы. В диалоговом окне «Создание сводной таблицы», выбрав таблицу «Поставки», установим флажок «Добавить эти данные в модель данных». В процессе конструирования структуры сводной таблицы в окне «Поля сводной таблицы» перейдем на вкладку «Все». Доступными окажутся поля всех таблиц: «Поставщик», «Товар» и «Поставки». Выбирая поля таблиц, фильтруя и меняя их расположение в макете сводной таблицы, можно получить самые разнообразные данные для проведения аналитической работы. После изменения данных в таблицах требуется обновление сводной таблицы.
Построение формы.
Формы в СУБД используются для удобства просмотра, изменения и ввода данных в таблицы данных. Для рассматриваемой базы данных форма, с помощью которой пользователь вводит данные в таблицу «Поставки», может иметь вид, показанный ниже (рис. 5). Рассмотрим ее составные части. Ячейки верхней таблицы, выделенные желтым цветом, предназначены для ввода
данных пользователем, незакрашенные ячейки заполняются автоматически.
Дата поставки формируется функцией = СЕГОДНЯ ( ).
Формулы для вычисления стоимости товара и определения страны приведены выше.
Во второй таблице формы объединяются данные, которые необходимо добавить к таблице «Поставки». Данную операцию можно выполнить вручную или записать макрос, связав его с кнопкой «Внести запись». Ключевые инструкции макроса записываются следующим образом: N = Worksheets ("Таб-лицы").UsedRange.Rows.Count Worksheets ("Таблицы").Range
("A14:F14").Copy Worksheets ("Таблицы")^^ (N+1,12).PasteSpecial Paste:= xlPasteVal-ues
В первой инструкции определяется номер N последней занятой строки на листе «Таблицы». Во второй инструкции выполняется копирование строки данных, сформированной во второй таблице формы. В третьей инструкции выполняется вставка скопированной строки в таблицу «Поставки» в строку, следующую за последней занятой строкой.
Формирование отчета.
Еще одна составная часть СУБД - отчет, формируемый для вывода данных на печать. В простейшем случае он представляет собой таблицу с реквизитами «Номер счета», «Дата», «Название фирмы»,
Список литературы
1. Путин В.В. Послание Федеральному Собранию. - URL: http://kremlin.ru/events/president/news/56957 (Дата обращения: 07.08.2019).
2. Росстат впервые раскрыл долю малого и среднего бизнеса в ВВП. - URL: https://ru.investing.com (Дата обращения: 07.08.2019).
3. Сектор малого и среднего предпринимательства: Россия и Мир //Институт экономики роста им. П.А. Столыпина. - URL: httpV/stolypm.-institute/wp-
«Страна», «Наименование товара», «Количество, кг», «Стоимость» (рис. 6).
Отчет формируется по номеру счета, фактически по номеру записи в таблице «Поставки». Дата определяется функцией
= ИНДЕКС (Поставки;В1;1), которая возвращает значение ячейки, находящейся в первом столбце таблицы «Поставки», номер строки которой задан ячейкой с адресом B1.
Значения остальных реквизитов определяются аналогичным образом.
Таким образом, подробно представлена методика применения перспективной информационной технологии формирования несложной системы с использованием доступного и широко распространенного программного обеспечения Microsoft Excel, позволяющая вести учетные операции на малом предприятии. Показаны формулы для расчета реквизитов таблиц, предназначенных для хранения и выборки данных. Сложность базы данных может быть увеличена, а структура форм и отчетов легко настроена на потребности пользователя. Данная методика универсальна и легко воспроизводится на компьютерах любой мощности, оснащенных табличным процессором независимо от его разработчика. Эксплуатация системы учета хозяйственных операций и внесение в нее изменений не требуют от пользователя высокой квалификации, и данная система рекомендуется к использованию на предприятиях малого бизнеса.
content/uploads/2018/07/issledovanie-ier-msp-27.07.18.pdf (Дата обращения: 07.08.2019).
4. Все статьи из рубрики: Excel. - URL: https://lumpics.ru/category/excel (Дата обращения: 07.08.2019).
5. Создание связи между двумя таблицами в Excel. - URL: https://support.office.com/ru-ru/artide/Создание-связи-между-двумя-табли-цами-в-excel-fel b6be7-1d85-4add-a629-8a3848820be3 (Дата обращения: 07.08.2019)..
Поступила в редакцию 09.08.2019
Электронный научно-практический журнал "Бюллетень инновационных технологий" (ISSN 2520-2839) является сетевым средством массовой информации регистрационный номер Эл № ФС77-73203 по вопросам публикации в Журнале обращайтесь по адресу [email protected]
Сведения об авторе:
Сальников Игорь Алексеевич - кандидат технических наук, доцент, заведующий кафедрой информатики и информационных таможенных технологий Санкт-Петербургского филиала Российской таможенной академии, e-mail: [email protected]
