Преподаватель который помогает студентам и школьникам в учёбе.

Автоматизация аналитического учета ООО «ПепсиКо Холдингс»

Содержание:

Введение

Компания PepsiCo — крупнейший в России производитель продуктов питания и напитков. В центральном и региональных офисах компании и на ее многочисленных производственных предприятиях работает более 20 000 сотрудников.

Сегодня компания PepsiCo занимает ведущие позиции в сегментах газированных, негазированных и энергетических напитков с торговыми марками Pepsi^®, 7 Up^®, Mirinda^®, Adrenaline Rush^®, «Русский дар», и др., на рынке чипсов и снеков (Lay’s^®, Cheetos^®, «ХрусTeam»), в сегменте соков и нектаров («Я», «Фруктовый Сад», J7^®, «Любимый» и др.), на рынке молочной продукции («Домик в деревне», «Веселый молочник», «Чудо», Bio Max^®, «Имунеле»), и детского питания («Агуша» и «Чудо детки»), а также в сегменте бутилированной воды («Аква Минерале», «Ессентуки», «Родники России»).

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

Одно из главных направлений работы – работа с национальными ключевыми клиентами, такими как «Пятерочка», «Перекресток», «Карусель», «Магнит», «Дикси» и т.п. (Далее NKA – National Key Account). Одно из структурных подразделеней NKA является CWHD – Central Warehouse Delivery, задачей которого является обслуживание двух ключевых клиентов – «Магнит» и «Дикси».

Основная причина выделения нового подразделения – специфика работы данных клиентов, а именно отгрузка готовой продукции исключительно в Распределительные Центры (далее РЦ), с невозможностью отслеживания продаж в конечные торговые точки (Sell In). При этом для эффективной работы с клиентами существует договоренность о предоставлении ими продаж из торговых точек конечным потребителям (Sell Out).

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

Целью данной работы является автоматизация аналитического учета на основе Sell Out данных.

Задачи:

Автоматизация обработки исходных данных (Sell Out)
Построение ИС на основе OLAP кубов, с конечным интерефесов в MS Excel
Автоматизация обработки ошибок
Снижение временных и трудозатрат на обработку данных и формирование отчетности

P.S. В связи с политикой компании ООО «ПепсиКо Холдинг» и с целью сохранения коммерческой тайны все данные не касающиеся целей работы (данные сотрудников, данные о планах и продажах, технические характеристики рабочих машин компании и т.п.) будут скрыты или вымышлены. Данная работа выполняется в учебных целях и не представляет собой коммерческого продукта.

Глава 1. Технико-экономическая характеристика предметной области и предприятия

1.1 Характеристика отдела

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

Таблица 1.1.

Характеристика отдела

№ п\п	Наименование характеристики (показателя)	Значение показателя на определённую дату либо за период
1.	Масштабы деятельности	На территории Российской Федерации
2.	Численность персонала	100* человек
3.	Количество организаций, являющихся клиентами	2 торговых сети

1.2. Организационная структура

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

$D:\Users\40314684\Data\Downloads\Диаграмма без названия.jpg$

Рисунок 1.1. Организационная структура

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

1.3 Выбор комплекса задач автоматизации и характеристика существующих бизнес процессов

Исходные данные представляют собой ненормализованные таблицы в Excel, разных форматов, причем разные по аттрибутам для разных клиентов.

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

Пример исходных данных представлен в таблице 1.2

Таблица 1.2.

Пример исходных данных

Поле	Пример	Примечание
Филиал	Филиал Уральский	Филиал в классификации клиента
Завод	3001	Код торговой точки
	3774-Пятерочка	Имя
Материал	14022	Код продукта
	ЛЮБИМЫЙ Нектар яблочный д/дет.осв.0.2л	Наименование продукта
Производитель/вендор PL (НА)	ЛЕБЕДЯНСКИЙ	Изготовитель
Ассорт. Кластер (НА)	АК ДСК Челябинск
Регион	Регион Челябинск	Регион в классификации клиента
Количество (без ед. изм.)	6	Объем в штуках
Оборот (с НДС) (без ед.изм.)	129,5	Объем в рублях

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

Решение проблемы с неорганизованностью данных может являтся модель БД, в которую на этабе проектирования возможно будет заложить решение.

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

Комплексом задач в рамках данной работы будет являтся исключительно OLAP Cube для конечного пользователя, с возможностью оперативной загрузки новых данных в систему с тремя измерениями: Дата до недели, Место продаж до торговой точки, и Продукцией до СКЮ. В качестве справочной информации необходоми занать по измерениям:

Дата – Год, квартал, месяц, наименование месяца и номер недели.

Место – Регион в классификации клиента, регион в классификации Песпсико, Область, Город, Адрес (без атомарного деления), кому пренадлежит торговая точка (RSM, TSM, торговый), РЦ с которого отгружается конкретная ТТ, индетификаторы торговой точки.

Продукция – Категория продукции, брэнд, формат упаковки, вес и прочее.

Так же необходимо рассчитать объем проданной продукции в кг\л (в зависимости от категории).

Решение данной задачи позволит компании принимать верные управленческие решения, отслеживать разницу между отгруженной клиенту продукцией и проданной им, расчитывать остатки, а так же бонусная часть заработной платы торговой команды основывается именно на выполнении планов и целей расчитваемых на основе продаж из точки (SellOut).

В качестве исходной информации:

Excel файлы от клиента
Ассортиментный справочник
Адресная программа

Справочную информацию мне предоставляют координаторы и торговая команда. Подытожив, IDEF0 схема задачи будет выгляжить так: $D:\Users\40314684\Data\Downloads\КУРСАЧ.png$

Рисунок 1.2. IDEF0 схема задачи

Где блок A1 и A3 выполняется при помощи аналитика, основным средством работы которого будет являтся средство загрузки данных и Excel. Операции по загрузке данных, и их конвертации в OLAP Cube являются автоматизированными.

Глава 2. Информационное обеспечение задачи

2.1 Информационная модель и её описание

Информационная модель представлена на рисунке 2.1.

$D:\Users\40314684\Data\Downloads\КУРСАЧ (1)-Page-2 (1).png$

Рисунок 2.1. Информационная модель

Данные получаемые от клиента необходимо нормализовать. Эта операция будет производиться с помощью VBA макроса в приложении 1.
Далее происходит загрузка файлов во временную таблицу с помощью SSIS пакетов, в процедуру сразу включена проверка файлов на корректность.
В случае корректности данных с помощью SSIS пакетов происходит обновление данных в кубах
В случае успеха обновления куба, данные можно увидеть в Excel файле с настроенным подключением

2.2. Используемые классификаторы и системы кодирования

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

Таблица 2.1.

Нормализованные данные

Название	Тип	Описание
Дата	Date	Дата продаж
Клиент	Nvarchar(MAX)	Наименование торговой сети
Код ТТ	Nvarchar(MAX)	Код торговой точки
Код СКЮ	Nvarchar(MAX)	Код продукта
Рубли	Decimal(12,5)	Продажи в рублях
Штуки	Decimal(12,5)	Продажи в штуках

Таблица 2.2.

Отсутсвующий мэппинг продуктов

Название	Тип	Описание
Код СКЮ	Nvarchar(MAX)	Список отсутвующих в справочнике продуктов

Таблица 2.3.

Отсутсвующий мэппинг торговых точек

Название	Тип	Описание
Код ТТ	Nvarchar(MAX)	Список отсутвующих в справочнике точек

В качестве классификаторов в работе искользуются Код СКЮ и Код ТТ, но так как эти данные поступают от разных клиентов, где каждый спозует свою классификацию данных, то в данной курсовой работе классификаторы будут закодированы в текстовом формате, а при дальнейшей работе с ними будет использоваться кодирование hashbytes и преобразование текстового значения в тип bigint.

2.3. Характеристика нормативно-справочной, входной и оперативной информации

Реквизиты входной информации указаны в таблице

Таблица 2.4.

Входная информация

№	Название	Описание
1.	Дата	Выбор даты вручную
2.	Продуктовая иерархия	Выбор вручную или с помощью иерархии
3.	Торговая иерархия	Выбор вручную или с помощью иерархии

По продуктовому и торговому направлениям справочная информация хранится в «Current», т.е. любое изменение в справочной информации отображается во всей ИС.

Пример работы с временной иерархией на рисунке 2.2.

Рисунок 2.2. Иерархия времени

Пример работы с продуктовой иерархией на рисунке 2.3.

Рисунок 2.3. Иерархия продуктов

Пример работы с торговой иерархией на рисунке 2.4.

Рисунок 2.4. Торговая иерархия

Справочная информация с временной иерархие автоматическая и поддерживается средствами MS SQL Server, мэппинги по продуктам и клиентам поддерживаются специальным отделом Data Master и доступны в Excel формате.

Данные справочники, включая продажи загружаются на SQL Server средсвами MS SQL Server Integrated Service, где в DataFlow пакетах настраивается «забор» данных из сетевых папок, на которые любой аналитик, обрабатывающий данные может выложить необходимые ресурсы.

Далее теме же SSIS пакетами происходит обработка данных, где в запросе на обновление указан следующий запрос:

delete

FROM [CWHD].[dbo].[SellOut]

where ISNULL( [Рубли],0)= 0 and ISNULL( [Штуки],0)= 0

TRUNCATE TABLE [CWHD].[dbo].fact_table

INSERT INTO [CWHD].[dbo].fact_table

( [date]

,[outlet_code]

,[sku_code]

,[amount]

,[qty]

,[vol]

,[vol\8oz])

SELECT [Дата] as [date]

,cast(hashbytes('****',upper(SellOut.[Клиент] + '_' + [Код ТТ])) as bigint) as outlet_code

,cast(hashbytes('****',upper([Код СКЮ])) as bigint) as sku_code

,round(cast([Рубли] as float),4) as amount

,round(cast([Штуки] as float),4) as qty

,round(cast(([Штуки]*cast(replace(dbo.[Cross_Prod].[Вес],',','.') as float)) as float),5) as vol

,case

when Cross_Prod.[Группа продукта PEP]='Beverages' then round(cast(([Штуки]*cast(replace(dbo.[Cross_Prod].[Вес],',','.') as float)) as float),5)/5.67812

else round(cast(([Штуки]*cast(replace(dbo.[Cross_Prod].[Вес],',','.') as float)) as float),5)

end as [vol/8oz]

FROM dbo.SellOut LEFT OUTER JOIN

dbo.Cross_Prod ON dbo.SellOut.[Код СКЮ] = dbo.Cross_Prod.[Product ID Dixy]

По условиям изначальной задачи необходимо получение данных не только в рублях и штуках, а так же и в КГ и Литрах + для категории напитков в восьмиунцовках (еденица, необходимая для точного производства). Для этого все исходные данные из промежуточной таблицы с кодировкой загружаются в таблицу fact_table с учетом арефметических операций.

2.4. Характеристика результатной информации

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

Куб содержит следующие данные:

Меры
1. #Active Account
2. #SKU
3. Net Revenue
4. Quantity
5. Vol 8oz
6. Vol
7. VPO, NR
8. VPO, QTY
9. VPO, Volume
Измерение времени
1. Year
2. Quarter
3. Month
4. Week
Измерение продуктов
1. Barcode
2. SKU Name
3. Package size
4. Package type
5. Fat
6. Flavor
7. Brand group
8. Brand
9. Category
10. Category PEP
Измерение клиентов
1. Outlet_ID
2. Address
3. City
4. Area
5. Region
6. RSM
7. TSM
8. Developer
9. DC
10. Branch

Глава 3. Программное обеспечение задачи

3.1. Общие положения (дерево функций и сценарий диалога)

Т.к. информационная система в конечном счете представляет собой источник данных для self-service обработки данных, диалог с пользователем осуществляется посредством сводной таблицы Excel. Пример на рисунке 3.1.

Рисунок 3.1. Сводная таблица

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

3.2. Характеристика базы данных

Для управления и хранения большого объема данных ИС используется СУБД MS SQL Server 2008 (SP3) – 10.0.5538.0 (X64). Для автоматизации процесса загрузки, обработки и обнавления информации используется MS Visual Studio: SS Analysis Service и SS Integration Service. В решаемой задачи используется ER-модель, представленная на рисунке 3.2. ER-модель описывает взаимосвязь таблиц в БД.

Рисунок 3.2. ER модель

Структура таблицы DIM_Time представляет собой автоматически сформировную таблицу времени, где в качестве первичного ключа используется дата в формате dd.mm.yyyy. В OLAP Кубе по ТЗ требуются не все поля, поэтому на этапе формирования структуры измерения в дальнейшем будут использованы лишь необходимые:

Год
Квартал
Месяц
Номер месяца
Неделя

Так же, с признаком Not Visible используется поле Дата.

Структура таблицы Prod_table представлена в таблице 3.1.

Таблица 3.1.

Структура “Prod_table”

№	Наименование поля	Идентификатор поля	Тип поля	Прочее
1		client	nvarchar(255)
2		sku_code	bigint	PK, hashbytes
3		Product	nvarchar(255)
4		barcode	nvarchar(255)
5		sku_name	nvarchar(255)
6		Category	nvarchar(255)
7		category_pep	nvarchar(255)
8		sub_category	nvarchar(255)
9		brand_groupe	nvarchar(255)
10		brand	nvarchar(255)
11		sub_brand	nvarchar(255)
12		flavor	nvarchar(255)
13		pack	nvarchar(255)
14		pack_size	nvarchar(255)
15		fat	nvarchar(255)
16		volume	float
17	sku_code	sku_code_true	nvarchar(255)

Структура таблицы store_table представлена в таблице 3.2.

Таблица 3.2.

Структура “Store_table”

№	Наименование поля	Идентификатор поля	Тип поля	Прочее
1		chain	nvarchar(255)
2		client_id	bigint	PK, hashbytes
3		sap_id	nvarchar(255)
4		region	nvarchar(255)
5		area	nvarchar(255)
6		city	nvarchar(255)
7		outlet_address	nvarchar(255)
8		rsm	nvarchar(255)
9		tsm	nvarchar(255)
10	Route	dev_route	nvarchar(255)
11		format	nvarchar(255)
12		branch	nvarchar(255)
13		dc	nvarchar(255)
14	client_id	client_id_true	nvarchar(255)

Структура таблицы fact_table пердставлена в таблице 3.3.

Таблица 3.3.

Структура “Fact_table”

№	Наименование поля	Идентификатор поля	Тип поля	Прочее
1		date	datetime	PK
2		outlet_code	bigint	PK, hashbytes
3		sku_code	bigint	PK, hashbytes
4	Net Revenue	amount	float
5	Quantity	qty	float
6	Volume	vol	float
7		[vol\8oz]	float

Так же в структуре существуют расчетные поля – Количество точек, количество СКЮ, VPO. Код их рассчета в SSAS, ассоциирующий показатели в группу Fact Table представлен ниже.

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Measures].[Av Price per QTY]

AS [Measures].[Net Revenue]/[Measures].[Quantity],

FORMAT_STRING = "# ##0,00;-# ##0,00",

NON_EMPTY_BEHAVIOR = { [Net Revenue] },

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].[#Active Account]

AS sum(

filter(

EXISTING [Measures].[AA],

sum([Measures].[Quantity]) > 0)),

VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Table';

CREATE MEMBER CURRENTCUBE.[Measures].[#SKU]

AS sum(

filter(

EXISTING [Measures].[SKU],

[Measures].[quantity] >0

)

VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Table';

CREATE MEMBER CURRENTCUBE.[Measures].[VPO, Volume]

AS measures.volume/measures.[#Active Account],

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[VPO, NR]

AS measures.amount/measures.[#Active Account],

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[VPO, QTY]

AS MEASURES.quantity/measures.[#Active Account],

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[VPO, Vol8oz]

AS [Measures].[Vol 8oz]/measures.[#Active Account],

VISIBLE = 1;

3.3. Структурная схема пакета (дерево вызова программных модулей)

Т.к. интерфейс конечной ИС – Excel Pivot Table, то схема вызова програмных модулей отсутвует или может быть представлена в виде только одного блока «Excel Pivot Table».

Процесс работы с данной таблицей представляет собой «перетаскивание» необходимых полей в кросс-таблицу. Пример работы представлен на рисунках 3.3 и 3.4.

Рисунок 3.3. Работа с кубом

Рисунок 3.4. Работа с кубом

3.4. Описание программных модулей

На рисунке 3.5 представлена блок-схема.

$D:\Users\40314684\Data\Downloads\КУРСАЧ (1) (2)-Page-3.png$

Рисунок 3.5. Блок-схема задачи

Глава 4. Контрольный пример реализации проекта и его описание

В ходе выполненных работ был разработан OLAP Cube, выполняющий все необходимые задачи.

Проведем два теста, первый с заведомо недостающим мэппингом, второй корректный.

Для начала загружаем исходные данные на сетевой ресурс:

$C:\Users\40314684\AppData\Local\Microsoft\Windows\INetCache\Content.Word\ttl3.png$

Рисунок 3.6. Данные на сетевом ресурсе

В ходе выполнения Job задачи на сервере (рисунок), мы получаем первые результаты

$C:\Users\40314684\AppData\Local\Microsoft\Windows\INetCache\Content.Word\ttl4.png$

Рисунок 3.7. Успешное выполнение Job задачи

Результат первого теста представлен на рисунке 3.8.

$C:\Users\40314684\AppData\Local\Microsoft\Windows\INetCache\Content.Word\ttl1.png$

Рисунок 3.8. Отбивка с ошибкой

Результат второго теста представлен на рисунке 3.9.

$C:\Users\40314684\AppData\Local\Microsoft\Windows\INetCache\Content.Word\еед2.png$

Рисунок 3.9. Отбивка об успешном выполнении

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

ЗАКЛЮЧЕНИЕ

В результате выполения курсовой работы была выполнена автоматизация аналитического учета на основе SellOut данных. Ранее данные хранились не структурировано и доступ к ним, а тем более расчеты выполнялись полностью вручную и это занимало огромное количество времени, к тому же людские ресурсы не позволяли в иерархии времени опускаться ниже одного месяца. Автоматизация этого процесса позволила сохранить большое количество рабочих часов, избавиться от человеческого фактора, выявлять ошибки заранее и получить конечному пользователю возможность быстро и «без хлопот» получать необходимые для себя данные по продажам клиентов отдела. К тому же данная система легко позволит при необходимости увеличить количество клиентов, регулировать количество продуктовых категорий и расширять возможности загрузки данных во временном интервале. Автоматический загрузчик настроен таким образом, что с легкостью может загрузить такое количество файлов единовременно, насколько это позволит дисковое пространство, так как на этом этапе не происходит финальной агрегации данных. И только в случае успешной загрузки файлов и полностью корректных данных начинается «слияние» с уже загруженными.

В ходе выполнения курсовой работы были произведены:

Автоматизация обработки исходных данных (Sell Out)

Построение ИС на основе OLAP кубов, с конечным интерефесов в MS Excel

Автоматизация обработки ошибок

Разработаная ИС позволила:

Снизить временные затраты

Масштабировать существующую систему по всем иерархиям

Повысить качество данных

Минимизировать человеческий фактор

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

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

Грекул В.И., Денищенко Г.Н., Коровкина Н.Л. Проектирование информационных систем – М.: ИУИТ, 2012 – 300с.
Гущин А.Н. Базы данных: учебник. - М.: Директ-Медиа, 2014. - 266с
Методология функционального моделирования IDEF0, Руководящий документ, Госстандарт России
http://www.pepsico.ru/company/pepsico-in-russia
https://www.draw.io/

ПРИЛОЖЕНИЯ

Приложение 1.

VBA макрос.

Sub save_new_books()

Dim sFiles, sNewFolder, sFileName, sNFiles As String

Dim i, ii As Byte

Dim j As Byte

Dim k As Integer

Dim sSheetName, sTrueName, max As String

Dim ch As Range

Dim example, nrow As String

Dim alarm As Boolean

Dim Data As String

Data = InputBox("Enter data DD.MM.YYYY", "Enter data")

Application.DisplayAlerts = False

'colomns (Data)

sNewFolder = Cells(1, 3)

i = 2

sFiles = Sheets("final").Cells(i, 1)

sFileName = Sheets("final").Cells(i, 2)

Do While sFiles <> ""

sNFiles = sNewFolder & sFileName

FileCopy sFiles, sNFiles

With Application.Workbooks.Open(sNFiles)

j = .Sheets.Count

For k = j To 1 Step -1

sSheetName = .Sheets(k).Name

Set ch = Application.Workbooks("SETUP.xlsm").Sheets("setting").Range("A:A").Find(What:=sSheetName, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

' MsgBox ("OK")

On Error GoTo Line1

If ch <> sSheetName Then

Line1:

.Sheets(k).Visible = True

.Sheets(k).Delete

Resume Line2

Line2:

Else: .Sheets(k).Name = "Data"

End If

'---------------------------

max = Application.Workbooks("SETUP.xlsm").Sheets("setting").Cells(Rows.Count, 3).End(xlUp).Row

alarm = True

For ii = 2 To max

example = Application.Workbooks("SETUP.xlsm").Sheets("setting").Cells(ii, 3)

Line4:

Set ch = .Sheets("Data").Range("1:20").Find(What:=example, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

On Error GoTo Line5

nrow = ch.Row

If nrow = 1 Then

alarm = False

GoTo Line3

Else

.Sheets("Data").Rows(1).EntireRow.Delete

'alarm = False

GoTo Line4

End If

Line5:

Resume line6

line6:

'---------------------------

Line3:

Call Mapping

Call Check_SKU

Call Check_AA

colomns (Data)

If alarm = True Then MsgBox ("ALARM! " & sFileName)

.Close savechanges:=True

End With

i = i + 1

sFiles = Sheets("final").Cells(i, 1)

sFileName = Sheets("final").Cells(i, 2)

Loop

Application.DisplayAlerts = True

End Sub

Function colomns(Data As String)

Dim arrNR(10) As Integer

Dim arrQT(10) As Integer

Dim arrDel(20) As Integer

Dim i, j As Integer

Dim b, z As String

Dim ch As Range

j = 1

b = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column

If Cells(1, i) = "Рубли" Then

arrNR(j) = Cells(1, i).Column

j = j + 1

End If

j = 1

For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column

If Cells(1, i) = "Штуки" Then

arrQT(j) = Cells(1, i).Column

j = j + 1

End If

j = 1

For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column

If Cells(1, i) = "Рубли" Or Cells(1, i) = "Штуки" Then

arrDel(j) = Cells(1, i).Column

j = j + 1

End If

If arrNR(2) = "0" And arrQT(2) = "0" Then

'www

i = i

Else

Dim rez As Double

'MsgBox ("More")

z = Cells(1, Columns.Count).End(xlToLeft).Column

Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column + 1) = "Рубли"

Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column + 1) = "Штуки"

For i = 2 To b

rez = 0

For j = 1 To UBound(arrNR)

If arrNR(j) = "0" Then GoTo Line1

rez = rez + Cells(i, arrNR(j))

Line1:

Cells(i, z + 1) = rez

'-------------------------------------------------

rez = 0

For j = 1 To UBound(arrQT)

If arrQT(j) = "0" Then GoTo Line2

rez = rez + Cells(i, arrQT(j))

Line2:

Cells(i, z + 2) = rez

'-------------------------------------------------

For i = 20 To 1 Step -1

If arrDel(i) = "0" Then GoTo Line4

Columns(arrDel(i)).Delete

Line4:

End If

'--------------------------------------------------СОРТИРОВКА-УДАЛЕНИЕ------------------------------------------

Dim numCol As Integer

z = Cells(Rows.Count, 1).End(xlUp).Row

Columns("A").Insert

Set ch = Range("1:1").Find(What:="Код ТТ", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

numCol = ch.Column

Columns(numCol).Cut [C1]

Set ch = Range("1:1").Find(What:="Код СКЮ", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

numCol = ch.Column

Columns(numCol).Cut [D1]

Set ch = Range("1:1").Find(What:="Рубли", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

numCol = ch.Column

Columns(numCol).Cut [E1]

Set ch = Range("1:1").Find(What:="Штуки", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

numCol = ch.Column

Columns(numCol).Cut [F1]

Cells(1, 1) = "Дата"

Cells(1, 2) = "Клиент"

Range("G:BB").Delete

Dim sFileAA, sFileNow As String

Dim clien, clientName As String

sFileNow = Application.ActiveWorkbook.Name

sFileAA = Application.Workbooks("SETUP.xlsm").Sheets("setting").Cells(3, 6)

On Error Resume Next

i = 2

client = "0"

Do While client = "0"

With Application.Workbooks.Open(sFileAA)

Set ch = .Sheets(1).Range("C:C").Find(What:=Workbooks(sFileNow).Sheets(1).Cells(i, 3), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

client = ch.Row

i = i + 1

If client <> "0" Then clientName = .Sheets(1).Cells(client, 2)

.Close savechanges:=False

If i = 5000 Then

MsgBox ("В 5к строк нет данных")

Exit Do

End If

End With

Loop

Range(Cells(2, 1), Cells(z, 1)) = Data

Range(Cells(2, 2), Cells(z, 2)) = clientName

End Function