Автоматизация процесса распознавания и сравнения файлов Excel. Поиск минимальной цены для каждого наименования. Решение проблемы по причине существующих различий в минимальной партии товара. Тестирование программы с Excel файлами с разным числом строк.
Аннотация к работе
Номенклатура продукции, предоставляемая поставщиками, очень сильно различается как названием и артикулом, так единицей измерения и ценой. В качестве исходных данных будем использовать файлы счетов поставщиков в формате Excel при этом необходимо обеспечить максимальную совместимость и независимость работы программы от версии Excel файлов. В качестве примера возьмем двух поставщиков: Поставщик Товар Ед.изм Цена Как видно из таблицы, у двух поставщиков различается перечень предлагаемых товаров, наименование, единицы измерения и, соответственно, стоимость. В результате анализа и структуризации мы должны получить таблицу, в которой одинаковые товары объединяем в одну строку: Наименование Поставщик 1 Поставщик 2· В результате выполнения курсовой работы была разработана программа «Отдел снабжения» реализованная на языке Delphi 5, что позволило максимально визуализировать программу.
Введение
В деятельности отделов снабжения предприятий часто возникает необходимость выбора поставщика с наименьшей ценой товара путем анализа предложений большого количества разношерстных поставщиков. Номенклатура продукции, предоставляемая поставщиками, очень сильно различается как названием и артикулом, так единицей измерения и ценой.
Как правило, анализировать приходится коммерческие предложения (счета протоколы, протоколы согласования цен и т.д.) предоставляемые в виде файлов формата Excel. Причем у каждого поставщика различна не только структура файлов и номенклатура указанного товара, но и минимальный объем единичной партии товара. Все это сильно затрудняет прямое сравнение средствами Excel и удлиняет время необходимое для рассмотрения предложений поставщиков.
Автоматизация процесса распознавания и сравнения файлов Excel позволит облегчить работу отдела снабжения.
1. Постановка задачи
Исходя из опыта работы в отделе снабжения, можно составить перечень основных требований к разрабатываемому программному продукту. В качестве исходных данных будем использовать файлы счетов поставщиков в формате Excel при этом необходимо обеспечить максимальную совместимость и независимость работы программы от версии Excel файлов.
Программа должна будет открывать файлы задаваемые пользователем и выбирать из них необходимую информацию. Для обеспечения удобства пользования, выбор файлов должен предоставляться непосредственно через программу. При этом никаких ограничений на число файлов быть не должно.
После открытия выбранных файлов, информация должна считываться и подвергаться анализу с учетом возможных различий в структуре данных, предоставленных поставщиками.
Результатом анализа должна стать единая таблица, в которую будут сведены все товары предлагаемые поставщиками. Информация должна быть распределена таким образом, чтобы одинаковые товары независимо от их названия и артикула у разных поставщиков находились в одной строке. Кроме этого, при расчете стоимости товара должна учитываться единица измерения (минимальная партия) товара.
В качестве примера возьмем двух поставщиков: Поставщик Товар Ед.изм Цена
Поставщик 1 Гвозди 12 см (250 шт./уп) уп 100000
Поставщик 2 Гв-дь арт. 173212 шт 450
Поставщик 2 Гв-дь арт. 173213 шт. 460
Как видно из таблицы, у двух поставщиков различается перечень предлагаемых товаров, наименование, единицы измерения и, соответственно, стоимость. В результате анализа и структуризации мы должны получить таблицу, в которой одинаковые товары объединяем в одну строку: Наименование Поставщик 1 Поставщик 2
Артикул Ед. изм. Цена Артикул Ед. изм. Цена
Гвоздь 120 мм Гвозди 12 см (250 шт./уп) уп 100000 Гв-дь арт. 173212 шт 450
Гвоздь 130 мм Гв-дь арт. 173213 шт 460
В процессе поиска минимальной цены для каждого наименования программа должна обрабатывать только те позиции, которые реально представлены у каждого поставщика. В случае, когда наименование присутствует только у одного поставщика, его цена и будет минимальной.
В итоговом отчете необходимо видеть не только минимальную цену, но и разницу между минимальной ценой и остальными ценами в процентах.
Дополнительно предусмотрим возможность экспорта полученного отчета в Excel, как наиболее распространенный и универсальный инструмент для работы с таблицами.
2. Разработка алгоритма решения задачи
С учетом поставленной задачи разработаем алгоритм, позволяющий обеспечить универсальность, надежность и удобство работы программного продукта.
Всю программу разделим на три основные блока: загрузка данных пользователем, обработка данных программой и выгрузка результата. Рассмотрим каждую часть в отдельности и разработаем общую логику алгоритма.
Загрузка данных пользователем: Логично предположить что формат (структура) XLS файла у каждого поставщика будет своя. С учетом требования универсальности мы не будем останавливаться на какой-то определенной форме XLS счета. В этом случае после выбора файла XLS счета, предоставим пользователю возможность самому выбрать диапазон позиций в XLS счете, с которыми он хотел бы в дальнейшем работать. Для однозначной идентификации товара у данного поставщика будем использовать связку НАИМЕНОВАНИЕ - ЕД.ИЗМ. Это позволит закрыть вопросы возникающие в случае если у поставщика наименование одинаковое, а минимальный размер партии разный (соответственно и цена). Следовательно, после выбора конкретного XLS файла, пользователь будет выбирать диапазон значений НАИМЕНОВАНИЙ и ЕДИНИЦ ИЗМЕРЕНИЯ. Для этого после выбора XLS файла его необходимо открыть (например только для чтения) предоставив пользователю возможность выбрать необходимое. После выбора - файл закрывать.
Вместе с выбором конкретного XLS файла пользователь должен будет указать ПОСТАВЩИКА. Это позволит повысить надежность и универсальность алгоритма ибо указание имени поставщика в имени файла ухудшит универсальность и надежность алгоритма. Как показывает опыт работы поставщики часто допускают ошибки в именовании файлов.
После ввода информации о XLS файле, задания диапазонов НАИМЕНОВАНИЕ-ЕД.ИЗМ-ЦЕНА и выбора поставщика, пользователь нажав кнопку добавляет введенное в список файлов для дальнейшей обработки. Необходимо обеспечить не только возможность добавления, но и удаления файлов из этого списка.
В итоге работу блока загрузки данных представим так: - Выбираем XLS файл счета.
- Задаем имя поставщика
- Указываем диапазоны НАИМЕНОВАНИЕ - ЕД.ИЗМ. - ЦЕНА
- Добавляем введенную информацию в список файлов
- В случае необходимости пользователь удаляет выбранную информацию из списка.
Обработка данных программой: После выбора всех файлов пользователем, программа начинает их обработку. Тут необходимо разработать основную логику работы.
Последовательно открываем каждый XLS файл из списка и загружаем из них НАИМЕНОВАНИЕ, ЕДИНИЦУ ИЗМЕРЕНИЯ и ЦЕНУ.
Для сравнения цепочек «НАИМЕНОВАНИЕ-Ед.изм.» у разных поставщиков необходимо разработать алгоритм однозначной идентификации товаров. В данном случае применим базу данных товаров «НОМЕНКЛАТУРА» в следующем виде.
Таблица 1
Наименование1
Наименование2
Наименование3
Таблица 2
Поставщик Наименование Ед.изм. Множитель
Поставщик1 Наименование1 Ед.изм. 1 Мн1
Поставщик2 Наименование1 Ед.изм. 2 Мн2
В таблице 1 будут находится наименования товаров которые удобны и понятны пользователю для однозначной идентификации товара.
Таблица 2 подчинена таблице 1. Т.е. у каждого наименования в Таблице 1 своя информация из Таблицы 2.
Такая структура позволит хранить соответствие между наименованиями товаров конкретных поставщиков и наименованием, используемым и понятным пользователю. Для однозначной идентификации товаров поставщиков мы будем использовать цепочку «Наименование-ед. изм.».
Основная проблема возникает по причине существующих различий в минимальной партии товара. Т.е. один поставщик может продавать товар упаковками по 250 шт. в каждой, а другой - поштучно. Логично предположить что цена товара у этих поставщиков будет отличаться в одну сторону, а стоимость того объема поставки, который нам необходим - в другую. Для согласования минимальных партий товара и приведения их к одному виду применим множитель для каждого товара поставщика. Посредством множителя закроем вопрос с различиями в минимальных партиях товара. Множитель будем использовать при вычислении цены товара. Так мы сможем рассчитать цены товаров для случая одинаковой партии. Минимальную партию товара пользователь выбирает сам. Т.е. если один товар продается упаковками по 250 шт., а второй поштучно - логично сделать множитель для первого товара - 1, а для второго - 250. Этим мы уравняем партии до 250 шт. и сможем сравнивать реальные цены на товар.
По опыту работы можно отметить что для небольших фирм продавцов число наименований в Таблице 1 будет порядка 500. Для крупных более 1000. С учетом того, что количество поставщиков для каждой позиции может быть достаточно большим, а количество пользователей программы мы ничем не ограничиваем применим многопользовательскую базу данных.
Следовательно, после получения информации о каждом товаре из XLS файла мы должны будем осуществить поиск данного товара в нашей базе данных. Если товар не будет найден мы позволяем пользователю добавить товар в базу «НОМЕНКЛАТУРА» и затем используем введенную им информацию дальше. Когда товар в базе найден мы можем узнать его ПОЛЬЗОВАТЕЛЬСКОЕ НАИМЕНОВАНИЕ и МНОЖИТЕЛЬ.
Результирующая таблица будет состоять из перечня пользовательских наименований, которым в соответствие будут поставлены товары предложенные поставщиками. Т.е. каждая строка таблицы будет однозначно идентифицировать товар необходимый пользователю и показывать какие поставщики и по какой цене этот товар предлагают. Вместе с тем, мы можем сразу сравнить цены поставщиков (посредством применения множителя) и выделить минимальную цену по каждой позиции.
Общий алгоритм работы представим так: - Пользователь дает команду обработки перечня XLS файлов
- Программа открывает каждый файл и начинает считывать с него построчно НАИМЕНОВАНИЕ, ЕД.ИЗМ., ЦЕНУ.
- После считывания позиции программа начинает поиск товара в базе «НОМЕНКЛАТУРА».
- Если товар не найден мы даем пользователю возможность добавления товара в базу с указанием его множителя
- Если товар найден - мы берем его множитель и пользовательское наименование и осуществляем его добавление в результирующую таблицу с учетом того, что товар с таким пользовательским наименованием в результирующей таблице мог быть добавлен ранее. В этом случае мы просто записываем параметры (наименование, поставщик, ед. изм., цена) товара в существующую строку и соответствующий данному поставщику столбец. Иначе просто добавляем в таблицу новую строку и заносим в нее параметры (наименование, поставщик, ед. изм., цена) товара и пользовательское наименование.
- Одновременно с занесением информации в результирующую таблицу осуществляем расчет цен исходя из множителей и находим поставщика с минимальной ценой и стоимостью (исходя из множителя) для каждого товара.
- После занесения в результирующую таблицу всех данных показываем ее пользователю.
Выгрузка результата: Основным блоком, в котором необходимо обеспечить максимальную универсальность будет выгрузка данных. Как указывалось ранее, выгрузку будем осуществлять в Excel. Логика работы блока будет следующая: - Показываем результирующую таблицу
- Пользователь выбирает перечень данных, которые он хотел бы экспортировать в Excel
- После нажатия кнопки экспорт информация в виде таблицы копируется в Excel
- После завершения экспорта, таблица в Excel обрисовывается рамками и файл отчета показывается пользователю.
3. Разработка программы
Для разработки программы выберем язык высокого уровня типа Delphi 5. Это позволит максимально использовать визуализацию в программе. Также понадобится поддержка работы с Excel и базами данных. Как было указано ранее, мы выбираем многопользовательскую реляционную базу данных. В качестве СУБД выберем Firebird. Эта СУБД обладает подходящими нам характеристиками: бесплатность, надежность, высокое быстродействие, поддержка триггеров, индексов и т.д. Кроме этого, для работы внутренних таблиц необходим будет BDE(v5) под Delphi 5. Для внутренних целей программы применим таблицы располагаемые в ОЗУ.
Разделим программу на четыре основные формы и продумаем логику их взаимосвязи: Главная форма.
На ней расположим таблицу с параметрами Excel файлов, основные контроли ввода и кнопки добавления (удаления) информации в таблице. Кроме этого разместим на главной форме меню с пунктами «Номенклатура» и «Обработать».
Осуществить выбор файла XLS пользователь сможет нажав на кнопку возле нужного контроля.
Указание диапазона наименований, единиц измерения и цен будет доступно пользователю после нажатия кнопки «Задать». В этом случае текущий выбранный пользователем файл Excel будет автоматически открыт и пользователю останется только выделить в нем соответствующий диапазон значений и кликнуть по тому в программе к которому будет относится выбранный диапазон. Программа прочитает параметры выделенного диапазона и занесет их в выбранный.
Выбор поставщика будем осуществлять в форме «Поставщики» появляющейся по нажатию соответствующей кнопки.
После выбора меню «Номенклатура» мы вызовем соответствующую форму.
Выбор меню «Обработать» вызовет запуск процедуры обработки выбранных пользователем файлов Excel. Результатом обработки станет появление формы «Итог…».
Форма «Поставщики». Появляется по нажатию соответствующей кнопки на главной форме.
На форме расположим таблицу с поставщиками, контролы для редактирования и кнопки добавления, редактирования и удаления поставщиков. Также поставим кнопку «Выбрать».
Нажатием соответствующих кнопок и заполнением контролов пользователь может осуществлять добавление, редактирование и удаление информации о поставщиках.
Нажатие кнопки «Выбрать» вызовет процедуру запоминания параметров выбранного поставщика и передачу их в основную форму.
Форма «Номенклатура».
Вызывается из основной формы выбором соответствующего меню либо в процессе работы модуля обработки XLS файлов. На форме расположены две таблицы. Основная, с перечнем пользовательской номенклатуры и подчиненная - с указанием товаров поставщиков связанных с текущим выбранным пользовательским наименованием.
На форме расположены дополнительные контролы и кнопки позволяющие добавлять, удалять и изменять соответствующую информацию.
Форма «Итог…».
Вызывается после окончания процедуры обработки всех выбранных Excel файлов. На форме находится таблица с результатами процедуры обработки. Кроме этого, есть для выбора перечня экспортируемой в Excel информации и кнопка запуска процедуры экспорта.
Форма «О программе».
Вызывается пунктом меню на основной форме.
4. Результаты тестирования
Для тестирования программы подготовим набор требований к тестовым данным позволяющий проверить работоспособность программы в самых критических ситуациях.
- Тестирование с пустой базой данных
- Тестирование с полной базой данных
- Частичное заполнение базы данных
- Количество строк с данными в файлах не одинаковое
- Необходимо протестировать вариант, когда в разных файлах нет одинаковых товаров
- Проверить многократный экспорт с разным количеством экспортируемых данных из одной и той же результирующей таблицы.
В результате тестирования с пустой базой данных программа выдала пользователю запрос на добавление данных в базу «Наименований». После добавления всех данных программа обработала и выдала результирующую таблицу.
При тестировании с полной базой данных, программа составила результирующую таблицу без выдачи запросов на добавление информации в базу данных.
При частичном заполнении базы, программа в процессе обработки информации выдала запрос на добавление только недостающей информации в базу.
При тестировании программы с Excel файлами с неодинаковым числом строк и с полностью отличающимся перечнем товаров программа корректно обработала данные и выдала результирующую таблицу.
При многократном экспорте результирующей таблицы с разным числом экспортируемых данных, программа корректно выполнила требуемые операции.
Вывод
· В результате выполнения курсовой работы была разработана программа «Отдел снабжения» реализованная на языке Delphi 5, что позволило максимально визуализировать программу.
Программа выполняет все требуемые от нее функции: - открывает файлы задаваемые пользователем и выбирает из них необходимую информацию;
- считывает информацию и производит анализ;
- может производить поиск по номенклатуре;
- выводит итоговую информацию в виде таблицы;
- учитывает единицы измерения.
В итоге программа оказалась не требовательной к ресурсам компьютера и может легко использоваться на любых ПК.
Данная программа позволяет выполнять самые необходимые действия. Основные операции: поиск, добавление, удаление, изменение, анализ.
Данная программа может использоваться пользователем, который не имеет большого опыта работы с ПК.
Список литературы
ГОСТ 19.701 - 90 ЕСПД Схемы алгоритмов, программ, данных и систем. Условия обозначения и правила выполнения. - М.: Изд. стандартов, 1990.
ГОСТ 19.002 - 80 ЕСПД Схемы алгоритмов и программ. Правила выполнения.
ГОСТ 19.003 - 80 ЕСПД Схемы алгоритмов и программ. Обозначения условные и графические.
Архангельский А.Я. Программирование в Delphi 5. - М.: ЗАО «Издательство БИНОМ», 2000. - 1070 с.: ил. (ч/з ПАУ) excel сравнение товар программа