Страницы: 1
RSS
приведение таблицы к иному виду
 
Добрый день!
В приложенном файле исходная таблица Sheet1.
На Sheet2 то, что нужно получить в итоге.
Суть заключается в том, к ячейкам в столбце «Артикл» (код1, ..., код49) надо притянуть значения из столбцов «штуки» и «продажи» по каждому магазину, их всего 65.

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

Посоветуйте, пожалуйста, более красивый способ, если он есть!
Макросами не владею, но, если надо решать с помощью них, буду рада разобраться.

Заранее спасибо!
 
Структура таблицы реальная? или позже выяснится, что: "на самом деле все "немного" подругому, не могли бы вы поправить?"
Изменено: LVL - 21.08.2013 14:44:40
 
=ИНДЕКС(Sheet1!$B$3:$V$25;ПОИСКПОЗ($B2;Sheet1!$A$3:$A$51;);ПОИСКПОЗ($A2;Sheet1!$B$1:$EA$1;)+СТОЛБЕЦ(A$1)-1)
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
Ок, на самом деле структура такова
 
Владимир, спасибо большое! Это сократило мои действия вручную!

Но моя задача также ещё заключается в том, чтобы создать структуру, а не просто притянуть значения-цифры.
Имеется ввиду, что надо создать столбец "магазин", т.е. транспонировать строку из исходной таблицы в столбец и каждое её значение (магазин1, ..., магазин65) попорядку растянуть на столько строк, сколько значений у артикулов (величина постоянная, в примере артикулов 49). а потом к этим магазинам притянуть уже сами значения артикулов, столбец которых каждый раз будет одним и тем же (код1, ..., код49). данная структура показана на Sheet2.
Как можно можно выполнить автоматически транспонирование и растягивание, я пока не понимаю. Хочется поступить следующим образом: после вставки транспонированной в столбец строки с магазинами, добавить 48 пустых ячеек после каждой заполненной. а потом уже будет просто их автоматичеки заполнить. но вот как добавить пустые ячейки, я пока не могу понять.
Или возможно стоит отталкиваться, наоборот, от того, что сначала автоматически 65 раз добавить блок из 49 артикулов, и уже к нему добавлять магазины.

Подскажите, пожалуйста, как надо дейтвовать.

Заранее спасибо!
 
candoliza!
Полагаю:
1. количество магазинов и количество артикулов может изменяться;
2. исходная структура (Sheet1) - экспорт из Генератора Отчетов ПО, эксплуатируемого в Вашей организации,
3. новая структура (Sheet2) - нужна для построения аналитических регламентных отчетов, как то:
3.1. анализ продаж по определенным артикулам в "разрезе магазинов" в определенном периоде,
3.2. анализ циклического (базового) прироста продаж по определенным артикулам в "разрезе магазинов" и в "разрезе периодов",
и т.д. и т.п.

В файле-примере на Sheet1 нужно:
1. столбы - достаточно оставить А~Q,
2. строки - достаточно указанных,
3. ячейки в вышеуказанной матрице ОБЯЗАТЕЛЬНО заполнить "нормальной" информацией.
 
Мотя, спасибо за Ваш комментарий.
 
Данные в массив, цикл по данным (цикл в цикле по столбцу и шапке)- заносим в словарь ключ МАГАЗИН|КОД, в item массив из двух значений, где суммируем штуки и стоимость (если их можно так тупо суммировать - цена за штуку всегда одинокова?).
В конце словарь в цикле выгружаем на лист (можно для скорости через массив).

Можно делать без массивов, но будет работать намного дольше.

P.S. Писать код по примерам без доработки невозможно (т.е. невозможно его применить/проверить) - нужны хоть какие-то названия для ключей и значения для суммирования.
Изменено: Hugo - 22.08.2013 11:09:34
 
Hugo, спасибо большое за Ваш ответ, но я ничего не поняла. Добавляю заполненный цифрами файл 12 для примера кода. Цифры нереальные, но пустые строки в реальном файле тоже есть.
Цена за штуку может меняться в зависимости от кода и магазина, то есть величина частного стоимость/штуки для всех магазинов для одинаковых кодов разная.
 
У меня тут нет 2007, поэтому весь файл во всей красе не вижу :(
Но что-то сделать смогу. Если ранее от других ответа не будет.
Но вот если цены разные - правильно ли складывать штуки и цены?
 
Так попробуйте (файл не приложу...):
Код
Option Explicit

Sub svodnik()
    Dim a(), dSht As Object, dSum As Object, i&, ii&, t$, el
    Set dSht = CreateObject("Scripting.Dictionary"): dSht.comparemode = 1
    Set dSum = CreateObject("Scripting.Dictionary"): dSum.comparemode = 1

    a = Sheets(1).[a1].CurrentRegion.Value
    For i = 2 To UBound(a, 2) Step 2
        For ii = 3 To UBound(a)
            t = a(1, i) & "|" & a(ii, 1)
            dSht.Item(t) = dSht.Item(t) + a(ii, i)
            dSum.Item(t) = dSum.Item(t) + a(ii, i + 1)
        Next ii
    Next i


    ReDim b(1 To dSht.Count + 1, 1 To 4)
    b(1, 1) = "МАГАЗИН"
    b(1, 2) = "АРТИКУЛ"
    b(1, 3) = "ШТУКИ"
    b(1, 4) = "СТОИМОСТЬ"

    i = 1
    For Each el In dSht.keys
        i = i + 1
        b(i, 1) = Split(el, "|")(0)
        b(i, 2) = Split(el, "|")(1)
        b(i, 3) = dSht.Item(el)
        b(i, 4) = dSum.Item(el)
    Next
    Workbooks.Add(1).Sheets(1).[a1].Resize(UBound(b), 4) = b

End Sub

Решил не мучиться с массивами в словаре - проще сделать два словаря  :)  
P.S. Код поместить в стандартный модуль любой книги, запускать (например по Alt+F 8)  при активной книге с данными (данные на первом листе этой книги, хотя это кстати можно поменять на активный лист - думаю так будет даже удобнее).
Достаточно в строке
a = Sheets(1).[a1].CurrentRegion.Value
удалить
Sheets(1). (с точкой).
Изменено: Hugo - 23.08.2013 12:36:44
 
Hugo!
Это потрясающе! Вы гений)) Спасибо Вам огромное-огромное!!!
Русские буквы почему-то заменились вопросами в моём коде...
Единственное, что меня огорчает, что я ничего не понимаю в этом коде, кроме присвоения переменных))
 
Взяли исходные данные в массив, перебираем его в цикле (почти все ячейки).
Из первого столбца и шапки составляем ключ, по этому ключу в одном словаре суммируем штуки, в другом цены. Кстати если в названиях уже есть "|", то нужно в коде заменить это разделитель на что-то другое, например "@@@".
В конце для скорости создаём результирующий массив (все размеры уже известны), циклом по словарю перекладываем в него данные (по ходу дела заполняем шапку, можно в любой момент).
Создаём новую книгу, куда выгружаем собранное. Можно выгружать куда угодно - но думаю в новую книгу удобнее, затем из неё можно копипастить куда захочется в конкретный момент.
 
Про русские буквы - локаль у Вас не русская? Вероятно это что-то системное...
Если не получается с кириллицей - то и ладно, можно без шапки сделать, или её скопировать с исходника, или эти строки написать используя коды символов и ChrW().
 
Русские буквы в коде оттого, что где-то раскладка на англ.
Измените раскладку в редакторе VBA на русский, на форуме тоже(обычным сочетанием, как всегда). Копируете с форума и вставляете. Все будет нормально.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Я перед постом проверил - копировал с форума с нерусской раскладки в нерусскую (не английскую, другую :) ) - кириллица есть.
 
У тебя, наверное, ХР. Плюс, возможно, когда-то делал правки в реестре(на форуме Kuklp как-то выкладывал файлик для этого).
А стандартно будут именно невероятные превращения кириллицы в фигню. Поэтому при наличии кириллических символов в кодах лучше сразу устанавливать для приложений между которыми происходит копирование/вставка раскладку на русский язык.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Возможно что так. Ну что ХР - это точно так :)
Я знаю, что если локаль не русская (есть такие рабочие машины), то там в кодах кириллицы нет. Более того, если UDF названа кириллицей - то она на листе не работает. Т.е. "внутри себя" всё работает ("каракулевые" переменные функционируют), а на лист данные не передаются.
Кажется так было :)
 
Цитата
Взяли исходные данные в массив...
В конце для скорости создаём результирующий массив...
Hugo, я сейчас разбираюсь с кодом и у меня появился вопрос.
Можно ли как-то изменить данный код, чтобы он делал тоже самое, если вместо одного столбца ("артикул" ;)  будут два столбца?
Я предположила, что надо идти примерно по такому пути:
Код
a = Sheets(1).[a1].CurrentRegion.Value
[U]ab = Sheets(1).[a:b1].CurrentRegion.Value[/U]
For i = 2 To UBound([U]a:b[/U], 2) Step 2
 For ii = 3 To UBound(a)
 t = a(1, i) & [U]"|" & ab(2, i) [/U]& "|" & a(ii, 1) 
 dSht.Item(t) = dSht.Item(t) + a(ii, i)
 dSum.Item(t) = dSum.Item(t) + a(ii, i + 1)
 Next ii
 Next i
 
Дык, Дима-то и выкладывал универсальный преобразователь...
http://www.excelworld.ru/forum/3-511-1
Дим, если можно, дай ссылку на статью в своем форуме. Помню - была.
Я сам - дурнее всякого примера! ...
 
Перевожу строку
a = Sheets(1).[a1].CurrentRegion.Value :
данные смежной с ячейкой A1 области помещаем в массив a.

Следовательно не важно, сколько там будет столбцов - они все будут в массиве. Тут уже скорее важно не понабрать лишнего :)
Следовательно, Ваш код неверен. Я даже не пойму, что Вы там хотели сделать.
 
Цитата
Дим, если можно, дай ссылку на статью в своем форуме. Помню - была.
http://www.excel-vba.ru/forum/index.php?topic=138.0
хотя там тоже самое.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
Следовательно, Ваш код неверен. Я даже не пойму, что Вы там хотели сделать.
Мне кажется, Вы меня не поняли. Во всяком случае у меня не срабатывает код.
сейчас структура такова: столбец А2:А51 ("Артикул" ;) , строка B1:EA1 ("Магазин" ;)  и массив данных, находящийся в повторяющихся столбцах "штуки" и "стоимость". На выходе мы имеем столбцы "магазин", "артикул", "штуки" и "стоимость".
Я же спрашиваю про следующую структуру. Изначальные данные: столбец А2:А51 ("Артикул" ;)  и столбец B2:B51 ("наименование" ;) , строка С1:EB1 ("Магазин" ;)  и массив данных, находящийся в повторяющихся столбцах "штуки" и "стоимость". На выходе хотелось бы получить "магазин", "артикул", "наименование", "штуки" и "стоимость".

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

Код
Option Explicit

Sub svodnik()
    Dim a(), dSht As Object, dSum As Object, i&, ii&, t$, el
    Set dSht = CreateObject("Scripting.Dictionary"): dSht.comparemode = 1
    Set dSum = CreateObject("Scripting.Dictionary"): dSum.comparemode = 1

    a = Sheets(1).[a1].CurrentRegion.Value
    For i = 3 To UBound(a, 2) Step 2
        For ii = 3 To UBound(a)
            t = a(1, i) & "|" & a(ii, 1) & "|" & a(ii, 2)
            dSht.Item(t) = dSht.Item(t) + a(ii, i)
            dSum.Item(t) = dSum.Item(t) + a(ii, i + 1)
        Next ii
    Next i


    ReDim b(1 To dSht.Count + 1, 1 To 5)
    b(1, 1) = "МАГАЗИН"
    b(1, 2) = "АРТИКУЛ"
    b(1, 3) = "наименование"
    b(1, 4) = "ШТУКИ"
    b(1, 5) = "СТОИМОСТЬ"

    i = 1
    For Each el In dSht.keys
        i = i + 1
        b(i, 1) = Split(el, "|")(0)
        b(i, 2) = Split(el, "|")(1)
        b(i, 3) = Split(el, "|")(2)
        b(i, 4) = dSht.Item(el)
        b(i, 5) = dSum.Item(el)
    Next
    Workbooks.Add(1).Sheets(1).[a1].Resize(UBound(b), 5) = b

End Sub
 
Спасибо большое!

Я немного не додумала...
 
Уважаемый Hugo!
Помогите мне опять, пожалуйста!
Я не могу додумать всё-таки, как поместить не два а четыре столбца в массив.

Что-то такое пытаюсь изобразить, но не получается:
Скрытый текст
 
Не, так не пойдёт.
Нужен файл с кодом, и описание задачи.
А вот на вопрос "как поместить не два а четыре столбца в массив" ответ простой - заполните данными "не два а четыре столбца".

P.S. UBound(a, 4) - это кажется для четырёхмерного массива. Даже представить такое страшно... :(
Изменено: Hugo - 19.09.2013 16:17:07
Страницы: 1
Наверх