ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ В КАЧЕСТВЕ. Бюллетень инновационных технологий экономические науки удк 004. 65 339. 5 Использование электронных таблиц в качестве
 Скачать 330.72 Kb.
|
|
БИТ 2019 Том 3 № 3 (11) 44 http://bitjournal.ru Бюллетень инновационных технологий ЭКОНОМИЧЕСКИЕ НАУКИ УДК 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 stabil- ity 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 unac- ceptable for most small businesses. The article shows the method of application of advanced information technology, using affordable and widespread software, on the example of ac- counting for foreign trade activities of small busi- nesses. The methods of linking spreadsheets and data sampling algorithms that simulate the operation of a simple database management system are pre- sented. 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, в последние несколько лет проводит актив- БИТ 2019 Том 3 № 3 (11) http://bitjournal.ru 45 ЭКОНОМИЧЕСКИЕ НАУКИ Бюллетень инновационных технологий ную политику по совершенствованию сво- его программного продукта. Для Excel раз- рабатываются и распространяются бес- платно и на коммерческой основе так назы- ваемые надстройки – дополнительные про- граммные модули. Они позволяют автома- тизировать решение типовых задач, обрабатывать большие наборы данных, подключаться к различным серверам баз данных для загрузки (импорта) и использо- вания достоверных данных глобального ха- рактера в электронных таблицах пользова- телей, а также выгружать электронные таб- лицы на платформу облачных вычислений Azure для совместного доступа своих корре- спондентов. Кто же является целевой аудиторией данных разработок? Крупные предприятия и организации, имеющие дело с гигант- скими массивами сложным образом взаи- мосвязанных данных, вынуждены забо- титься об эффективном размещении дан- ных на внешних носителях для обеспечения целостности и достоверности данных, при- емлемой скорости выборки и обработки данных, должны учитывать возможность реструктуризации данных, доступа к дан- ным по различным каналам связи и т.п. Яр- ким примером подобных информационных массивов являются базы данных, формиру- емые Федеральной таможенной службой Российской Федерации. Безусловно, в усло- виях необъявленной информационной войны остро стоит актуальный вопрос ин- формационной безопасности как отдель- ных предприятий и организаций, так и всей страны в целом. Очевидно, решение пере- численных выше задач может быть достиг- нуто только путем использования промыш- ленных систем управления базами данных (СУБД) типа Oracle и SQL Server. Таким образом, новые функции и инно- вационные технологии приложения Excel актуальны для среднего бизнеса. Перед ма- лым бизнесом чаще стоит проблема выжи- вания в сложной конкурентной среде, чем отслеживание и освоение новых информа- ционных технологий. Тем не менее, в Excel заложено немало неизвестных рядовым пользователям функций и информацион- ных технологий, освоение которых позво- ляет реализовать широкий спектр операций с данными, см., например, [4], упростить проведение учетных операций в малом биз- несе. Проведенные исследования. В данной статье представлена информационная тех- нология построения простейшей СУБД средствами приложения Excel. В качестве предметной области выбрана распростра- ненная область учета внешнеторговых опе- раций некоторого малого предприятия, осу- ществляющего закупку (импорт) сельскохо- зяйственной продукции за рубежом. При же- лании размеры и количество таблиц могут быть увеличены, а их расположение на ра- бочих листах – изменено. Данная техноло- гия основана на построении модели данных и реализуется типовыми или специальными средствами Excel, начиная с редакции 2013 года. Предварительно следует заметить, что в терминологии Excel любая электронная таблица (совокупность смежных строк или столбцов) называется базой данных. Более того, если такую базу данных определен- ным образом выделить среди других, она становится «настоящей» таблицей, точнее, так называемой «умной» таблицей, полу- чает имя и приобретает некоторые полез- ные качества. Поскольку технологии прило- жения Excel приближаются к технологиям СУБД, а не наоборот, в данной статье под базой данных будем понимать совокуп- ность взаимосвязанных «умных» электрон- ных таблиц. Вначале, как предполагает процесс по- строения некоторой базы данных, необхо- димо выполнить анализ предметной обла- сти и спроектировать ее информационно- логическую структуру данных. На основе анализа учетных данных требуется вы- брать атрибуты данных, подлежащих хра- нению в базе данных (столбцы), сгруппиро- вать их в объекты (таблицы) и установить связи между ними. Построение таблиц. Допустим, решено хранить объект «Поставщик» с атрибутами «Название фирмы», «Страна», «Адрес/кон- такт», «Менеджер», «Телефон». Данная таблица является базовой (независимой, справочной). Ее особенность состоит в том, что ее первый столбец «Название фирмы» должен быть первичным ключом, т.е. хра- нить уникальные, неповторяющиеся за- писи, отсортированные в порядке от А до Я. Создадим таблицу для объекта «По- ставщик». Сортировка столбца выполня- ется после его заполнения или после запол- нения всей таблицы. Наконец, преобразуем полученную таблицу в умную таблицу, для чего сделаем активной любую ячейку таб- лицы и выполним команды: ВСТАВКА → Таблицы → Таблица или Ctrl + T БИТ 2019 Том 3 № 3 (11) 46 http://bitjournal.ru Бюллетень инновационных технологий ЭКОНОМИЧЕСКИЕ НАУКИ или ГЛАВНАЯ → Форматировать как таблицу. Подтвердим, что таблица имеет заго- ловки, и при необходимости изменим ее ав- томатический формат. В поле «Имя» рас- крыв список, увидим имя, например, «Таб- лица1». Выделим ячейки таблицы без заго- ловков, перейдем на появившуюся вкладку КОНСТРУКТОР и в блоке «Свойства» изме- ним имя таблицы на «Поставщик» (рис. 1). Таким же требования предъявляются к объекту «Товар» с атрибутами «Наимено- вание товара», «Отпускная цена, руб.», «Группа». Будем подразделять товар на три группы: овощи, фрукты, ягоды. Создадим таблицу для объекта «Товар» и преобра- зуем ее в умную таблицу с именем «Товар» (рис. 2). Заметим, что связь между таблицами «Поставщик» и «Товар» неопределенная: один поставщик может продавать не- сколько видов товара, с другой стороны, один и тот же вид товара может быть при- обретен у разных поставщиков. Чтобы раз- решить данную неопределенность, в ин- формационно-логическую структуру дан- ных введем новый, вспомогательный объ- ект «Поставки». Предположим, что он со- держит атрибуты «Дата», «Количество, кг», «Стоимость». Для того, чтобы связать таблицы «По- ставщик» и «Поставки», введем в структуру таблицы «Поставки» столбец «Название фирмы». Он будет служить внешним («чу- жим») ключом, содержащим значения из со- ответствующего столбцы таблицы «Постав- щик». Значения во внешнем ключе могут по- вторяться. Для того, чтобы в таблице «Поставки» создать подстановку записей из таблицы «Поставщики», выделим столбец «Назва- ние фирмы» (без заголовка) и выполним ко- манды: ДАННЫЕ → Работа с данными → Про- верка данных В поле «Тип данных» в раскрываю- щемся списке выберем пункт «Список», определив в качестве источника в таблице «Поставщик» ячейки с названиями фирм. При активации ячеек в таблице «Поставки» справа от них появится знак раскрывающе- гося списка, из которого необходимо сде- лать выбор. При попытке ввести данное, от- личное от элементов списка, выводится со- общение об ошибке, и ввод данного отвер- гается. Таким образом, между таблицами об- разуется связь вида «один ко многим». Это значит, что в столбце «Название фирмы» таблицы «Поставщик» записаны лишь уни- кальные значения, а в столбце «Название фирмы» таблицы «Поставки» записаны, возможно, многократно, только данные из таблицы «Поставщик», и не могут встре- чаться иные значения. Аналогичным образом создается под- становка в таблице «Поставки» в столбце Рис. 1. Таблица «Поставщик» Рис. 2. Таблица «Товар» БИТ 2019 Том 3 № 3 (11) http://bitjournal.ru 47 ЭКОНОМИЧЕСКИЕ НАУКИ Бюллетень инновационных технологий «Товар» значений из столбца «Наименова- ние товара» таблицы «Товар». Порядок следования столбцов в таблице «Поставки» не имеет значения. Вычисляемое поле «Стоимость, руб.» таблицы «Поставки» определяется форму- лой = ВПР (N2;Товар;2;ЛОЖЬ)*О2, где ВПР – функция, которая ищет заданное значение в крайнем левом столбце таблицы и возвращает значение ячейки, находя- щейся в указанном столбце той же строки; N2 – адрес ячейки, в которой записано наименование искомого товара; Товар – имя таблицы, в которой ищется наименование товара, т.е. определяется номер строки, в которой оно записано; 2 – номер столбца «Отпускная цена, руб.» таблицы «Товар», из которого выби- рается значение стоимости указанного то- вара; ЛОЖЬ – признак того, что поиск произ- водится до точного совпадения наименова- ний товара; О2 – адрес ячейки, в которой записан вес товара. Таким образом, если, например, в таб- лице «Поставки» указан товар «Томат» и вес, равный 100 кг, Excel определит по таб- лице «Товар» стоимость указанного товара, например, 80 руб./кг, и умножит его на вес, записав в столбец «Стоимость, руб.» значе- ние 8000. Если, допустим, требуется таблицу «Поставки» дополнить неключевым столб- цом, например, «Страна», достаточно в ячейках данного столбца записать функцию вида: = ВПР (M2;Поставщик;2;ЛОЖЬ) По названию фирмы, записанному в ячейке с адресом М2, Excel найдет в таб- лице «Поставщик» номер строки, из которой во втором столбце выберет название страны (см. рис. 3). Умная таблица, кроме характерного цветового форматирования, позволяет вы- полнять фильтрацию текстовых и числовых Рис. 3. Таблица «Поставки» Рис. 4. Сводная таблица БИТ 2019 Том 3 № 3 (11) 48 http://bitjournal.ru Бюллетень инновационных технологий ЭКОНОМИЧЕСКИЕ НАУКИ данных по каждому столбцу, имеет очер- ченные границы. При добавлении новых строк и столбцов они автоматически вклю- чаются в состав умной таблицы, а формулы копируются. Таким образом, по информационно-ло- гической структуре данных построены и связаны отношениями «один ко многим» три таблицы. В Excel 2013 года и более поздних ре- дакций вместо подстановки в таблицу «По- ставки» столбцов «Название фирмы» и «Товар» достаточно построить модель дан- ных [5]. Для этого выполним команду ДАННЫЕ → Работа с данными → Отноше- ния. В диалоговом окне «Управление свя- зями» выберем создание связи между таб- лицами. В окне «Создание связи» первой укажем зависимую таблицу «Поставки» и ее внешний («чужой») ключ «Название фирмы», в который производится подста- новка. Второй укажем базовую таблицу «Поставщик» и ее первичный ключ «Назва- ние фирмы». Повторим операцию для вто- рой связи. В окне «Создание связи» первой укажем зависимую таблицу «Поставки» и ее внешний («чужой») ключ «Товар», в кото- рый производится подстановка. Второй ука- жем базовую таблицу «Товар» и ее первич- ный ключ «Наименование товара». Построение запроса. Кроме таблиц с данными, важным эле- ментом СУБД являются запросы, позволяю- щие делать поиск и выборку данных. В про- стейшем случае можно использовать филь- трацию данных любой из трех таблиц. Од- нако наибольший эффект для проведения анализа данных дает использование свод- ной таблицы и сводной диаграммы. В диа- логовом окне «Создание сводной таб- лицы», выбрав таблицу «Поставки», уста- новим флажок «Добавить эти данные в мо- дель данных». В процессе конструирования структуры сводной таблицы в окне «Поля сводной таблицы» перейдем на вкладку «Все». Доступными окажутся поля всех таб- лиц: «Поставщик», «Товар» и «Поставки». Выбирая поля таблиц, фильтруя и меняя их расположение в макете сводной таблицы, можно получить самые разнообразные дан- ные для проведения аналитической ра- боты. После изменения данных в таблицах требуется обновление сводной таблицы. Построение формы. Формы в СУБД используются для удоб- ства просмотра, изменения и ввода данных в таблицы данных. Для рассматриваемой базы данных форма, с помощью которой пользователь вводит данные в таблицу «Поставки», может иметь вид, показанный ниже (рис. 5). Рассмотрим ее составные ча- сти. Ячейки верхней таблицы, выделенные желтым цветом, предназначены для ввода Рис. 5. Форма для ввода данных в таблицу «Поставки» БИТ 2019 Том 3 № 3 (11) http://bitjournal.ru 49 ЭКОНОМИЧЕСКИЕ НАУКИ Бюллетень инновационных технологий данных пользователем, незакрашенные ячейки заполняются автоматически. Дата поставки формируется функцией = СЕГОДНЯ ( ). Формулы для вычисления стоимости товара и определения страны приведены выше. Во второй таблице формы объединя- ются данные, которые необходимо доба- вить к таблице «Поставки». Данную опера- цию можно выполнить вручную или запи- сать макрос, связав его с кнопкой «Внести запись». Ключевые инструкции макроса за- писываются следующим образом: N = Worksheets ("Таб- лицы").UsedRange.Rows.Count Worksheets (" Таблицы").Range ("A14:F14").Copy Worksheets (" Таблицы").Cells (N+1,12).PasteSpecial Paste:= xlPasteVal- ues В первой инструкции определяется но- мер N последней занятой строки на листе «Таблицы». Во второй инструкции выполня- ется копирование строки данных, сформи- рованной во второй таблице формы. В тре- тьей инструкции выполняется вставка ско- пированной строки в таблицу «Поставки» в строку, следующую за последней занятой строкой. Формирование отчета. Еще одна составная часть СУБД – от- чет, формируемый для вывода данных на печать. В простейшем случае он представ- ляет собой таблицу с реквизитами «Номер счета», «Дата», «Название фирмы», «Страна», «Наименование товара», «Коли- чество, кг», «Стоимость» (рис. 6). Отчет формируется по номеру счета, фактически по номеру записи в таблице «Поставки». Дата определяется функцией = ИНДЕКС (Поставки;B1;1), которая возвращает значение ячейки, нахо- дящейся в первом столбце таблицы «По- ставки», номер строки которой задан ячей- кой с адресом B1. Значения остальных реквизитов опре- деляются аналогичным образом. Таким образом, подробно представ- лена методика применения перспективной информационной технологии формирова- ния несложной системы с использованием доступного и широко распространенного программного обеспечения Microsoft Excel, позволяющая вести учетные операции на малом предприятии. Показаны формулы для расчета реквизитов таблиц, предназна- ченных для хранения и выборки данных. Сложность базы данных может быть увели- чена, а структура форм и отчетов легко настроена на потребности пользователя. Данная методика универсальна и легко вос- производится на компьютерах любой мощ- ности, оснащенных табличным процессо- ром независимо от его разработчика. Экс- плуатация системы учета хозяйственных операций и внесение в нее изменений не требуют от пользователя высокой квалифи- кации, и данная система рекомендуется к использованию на предприятиях малого бизнеса. Список литературы 1. Путин В.В. Послание Федеральному Собра- нию. – URL: http://kremlin.ru/events/president/news/56957 (Дата обращения: 07.08.2019). 2. Росстат впервые раскрыл долю малого и среднего бизнеса в ВВП. – URL: https://ru.investing.com (Дата обращения: 07.08.2019). 3. Сектор малого и среднего предприниматель- ства: Россия и Мир //Институт экономики роста им. П.А. Столыпина. – URL: http://stolypin.¬institute/wp- 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/article/Создание-связи-между-двумя-табли- цами-в-excel-fe1b6be7-1d85-4add-a629- 8a3848820be3 (Дата обращения: 07.08.2019).. Поступила в редакцию 09.08.2019 Сведения об авторе: Сальников Игорь Алексеевич – кандидат технических наук, доцент, заведующий кафедрой инфор- матики и информационных таможенных технологий Санкт-Петербургского филиала Российской тамо- женной академии, e-mail: igor.sal.spb@yandex.ru Электронный научно-практический журнал "Бюллетень инновационных технологий" (ISSN 2520-2839) является сетевым средством массовой информации регистрационный номер Эл № ФС77-73203 по вопросам публикации в Журнале обращайтесь по адресу bitjournal@yandex.ru |