[решено] вопрос по перекомпоновки данных

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

- - [решено] вопрос по перекомпоновки данных (http://forum.oszone.net/showthread.php?t=309035)

вопрос по перекомпоновки данных

Здравствуйте, я на форум недавно писала. Неудобно просить, но может кто поможет ещё по одному вопросу. Это честное исследование, т.е. реальные люди.
Например ,вот файл
на листе 5 мы видим шкалу цифер, и 4 категории. (cлишком дорого,слишком дешево, дешево, дорого)
А теперь, вот на листе 4, там 48 маленьких табличек. У них есть разные названия A, B,C.. Они унифицированы. Первая табличка соответствует слишком дорого, вторая слишком дешево, третье дешево, четвертая дорого
они идут по порядку Слева направо. у каждой таблички есть столбец Cumulative. Нужно значение в этом столбце прописать на листе 5 около соответствующей цифры. Например, ячейка на листе 4 F3 = 2, двойка стоит в строке , где цифра 22(ячейка b3) значит цифру 2 ставим на листе 5 напротив 22(ячейка b24). Поскольку это первая табличка, на листе 5 все это ставим в столбец слишком дорого. далее также с остальными таблицами. вторая табличка на листе 4 будет уже проставлять значения(Cumulative) в столбец слишком дешево и так далее. После того как 4 категории будут расставлены в табличке А, принимиаемя за 4 категории таблички B

итого будет 12 табличек на листе 5.

Единственное, как видно каждая табличка имеет разный диапазон цифер. На листе 5, как бы все значения выставлены по возрастанию поэтому они не ровные.Но каждая табличка заканчивается строкой missing
Таблицы(могут иметь разные названия)

Elizavetta, могу предложить следующий вариант:
1. На листе 4 у Вас 48 таблиц. В левом верхнем углу каждой из них есть название - А,В,С... F-1. Нужно поправить названия - буква А у вас в русской раскладке, в одном случае отсутствует наименование F-1.
Кроме того - в первых столбцах табличек листа 4 числа сохранены как текст. Лучше преобразовать их в числа. Для этого выделяем все 48 табличек и выполняем следующий код.

Код

Код:

Public Sub cell_txt2val()

Dim obj_cel As Object

For Each obj_cel In Application.Selection

    obj_cel.Value = obj_cel.Value

Next obj_cel

End Sub

Когда наименования переведены в английскую раскладку и указаны везде, преобразуем каждую табличку из 48 в отформатированную умную таблицу, причем каждой будет дано свое имя по принципу: слово "Таблица_", затем символ из левого верхнего угла (причем названия вида "А-1" меняем на "А1", т.к. имя не может содержать тире), затем "_" и номер таблички в ряду ( от 1 до 4).
Для этого запускаем при активном листе 4 скрипт:

Скрипт

Код:



Public Sub rot_tabl()

Dim strTabl As String

Dim i As Integer

Do Until Cells.Find(What:="Missing", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

        , SearchFormat:=False) Is Nothing

Cells.Find(What:="Missing", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

        , SearchFormat:=False).Activate

ActiveCell.Value = "Mis1sing"

Selection.CurrentRegion.Select

strTabl = Replace(ActiveCell.Value, "-", "")

Select Case Selection.Column

Case 2

i = 1

Case 8

i = 2

Case 14

i = 3

Case 20

i = 4

End Select







    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Таблица_" & strTabl & "_" & i



Loop

    

    Cells.Replace What:="Mis1sing", Replacement:="Missing", LookAt:=xlPart, SearchOrder _

        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False



End Sub

На листе 5 форматируем вашу таблицу в "умную" таблицу.

Скрин

В ячейке В1 указываем букву и цифру, которая нас интересует ( для четырех таблиц А, А1, В, В1 и т.д.). Для этого в столбце "I" перечислены все названия ваших таблиц, а в ячейке "В1" выполняется проверка вводимых данных (вкладка данные - проверка данных - список указать "I2:I13")
В сами ячеки вбиты следующие формулы:
Столбец В - "=ЕСЛИОШИБКА(ВПР(A3;ДВССЫЛ("Таблица_"&$B$1&"_1");5;ЛОЖЬ);"")"
Столбец C - "=ЕСЛИОШИБКА(ВПР(A3;ДВССЫЛ("Таблица_"&$B$1&"_2");5;ЛОЖЬ);"")"
Столбец D - "=ЕСЛИОШИБКА(ВПР(A3;ДВССЫЛ("Таблица_"&$B$1&"_3");5;ЛОЖЬ);"")"
Столбец E - "=ЕСЛИОШИБКА(ВПР(A3;ДВССЫЛ("Таблица_"&$B$1&"_4");5;ЛОЖЬ);"")"
Выбирая в В1 интересующую нас строку, получаем раскладку для четырех таблиц.

Файл прилагаю, будут вопросы - задавайте.

a_axe, Вы очень выручили. Абсолютно все работает. А можно только попросить, чтобы в таблице на листе 5 удалять пустые строки, например видно что строки с 51-56 , они полностью пустые их надо удалить
а вот строка 49 не полностью пустая, там есть данные в колонке 4, поэтому эту строчку не трогаю.

2. момент ,когда я делаю как вы говорите,но уже на своих файлах у меня почему кумулятивные проценты вот такого формата
рис.2 т.е. тут кумулятивные процент от 0- 33% почему-то, а у вас все ок от 1- 100. Пример файла на всякий случай прислала. может я что-то неверно запустила. И самое последнее можно к этим числам добавить знак % т.е. не просто 56, как в ячейки C9, а 56%

Цитата:

Цитата Elizavetta

тут кумулятивные процент от 0- 33% почему-то, а у вас все ок от 1- 100. »

Elizavetta, в файле "миллионники1" строка missing для каждой табличке 98, в файле "Питер" везде было 0. По идее - это необработанные результаты ("missing" вероятно = "пропущенные"), поэтому и диапазон процентов мал. Проверьте, в исходных необработанных файлах процент наверняка такой же, если нет - отпишитесь.
Что касается остальных моментов - решения следующие:
1. Создаем дополнительный заголовок "Видимость" в столбце "F".
2. Выделяем получившуюся таблицу включая заголовки и преобразуем ее в "умную" (сочетание клавиш ctrl+L). Получаем изображение как на скрине (цвет таблицы на ваше усмотрение).

Скрин1

3. Выделяем ячейки таблицы, в которых нужно проставить знак процента (без заголовков) - столбцы "В-Е". Жмем сочетание ctrl+1, в появившемся диалоге "формат ячеек" выбираем первую вкладку, формат "Все форматы" (на скрине показано зеленым цветом). Вбиваем в форматы выражение #0,0#"%", жмем окей. Смысл приблизительно следующий: в кавычках указан текст, который будет прибавляться к отображению значений (в данном случае - знак %), перед ними - количество знаков после запятой.
4. В ячейки столбца "Видимость" вбиваем формулу "=СЧИТАТЬПУСТОТЫ(Сводные_результаты[@[слишком дорого]:[дорого]])". Результатом будет количество пустых ячеек левее столбца "Видимость".
5. В заголовке "Видимость" выбираем фильтр (скрин 1 показано синим) и убираем галку с значения 4 (скрин 2), т.к. строки с четырьмя пустыми ячейками отображаться не должны.

Скрин2

a_axe, как всегда ясно объясняете. Единственное, было бы очень здорово, если бы все таблицы от A-F1 были на разных листах а не на листа 5. т.е. итого 12 листов. Такое возможно сделать?

Цитата:

Цитата Elizavetta

все таблицы от A-F1 были на разных листах а не на листа 5 »

Легко - можно скопировать лист 5 двенадцать раз ("схватили" левой клавишей мыши ярлычок листа 5, нажали ctrl и потащили вправо или влево, получилась копия листа вместе с итоговой таблицей) и на каждом ввести в ячейку В1 нужное вам название (А, А1 и т.д.). Получится 12 листов, на каждом копия таблицы, а вот буковки будут разные, и как следствие - содержимое. Чтобы было удобно - переименуйте листы по имени таблицы (двойной щелчок по ярлычку л. клавишей мыши) - на А, А1 и т.д.

Либо можно на листе 5 скопировать вашу таблицу 12 раз (в фильтре "Видимость" поставить галочку на 4, чтобы отключить все фильтры, а дальше стандартно: выделить таблицу + ячейку В1, ctrl+c, выделить место вставки, ctrl+v, как с любым другим диапазоном) единственное - располагать их нужно друг над другом (в "высоту"), а не рядом друг с другом (в "ширину"), иначе фильтрация пустых строк одной таблицы будет скрывать строку и для соседних таблиц. Однако в этом случае формулы копий таблицы все равно будут ссылаться на ячейку В1 (т.к. ссылка абсолютная), и это нужно поправлять руками.

Вчера весь день пыталась сделать умную таблицу как вы сказали. создала "видимость"выделила все столбцы и ничего. А когда при подсчете пустых ячеек, просто написано 388 и такого фильтра как у вас нет, там где 1-4.
Что я опять не так сделала?

Я прикрепила файл

Elizavetta, смысл Вы уловили правильно, не работает из-за одной описки: потеряли знак собаки (@)

Цитата:

=СЧИТАТЬПУСТОТЫ(Таблица5[@[слишком дорого]:[дорого]])

Знак указывает, что нужно брать не весь диапазон, а только из строчки, в которой вбита формула. Если его пропустить, то будет подсчитываться количество пустых ячеек во всех строках таблицы, а нам нужно, чтобы только в текущей. Если знак поставить, а потом забить формулу во все ячейки столбца F, то все работает. Скачать файл Питер1.
Кроме того, столбец F у вас в таблицу не входит, на нем просто указан автофильтр. Это не слишком принципиально, однако можно включить этот столбец в вашу таблицу5 - для этого убираем автофильтр (вкладка данные - фильтр), наводим мышку на правый угол ячейки нижнего правого угла таблицы, в нем есть "уголок" (маркер границы). За него можно растянуть таблицу например вправо. Почитать об этом можно тут. Обратите внимание, что если вы указали новую буковку для таблицы (A,A1 и т.д.), то автофильтр нужно обновить.

a_axe, где почитать про «@», как оно называется (поиском по такому символу сложно будет что-то найти)?

Iska, обычно определение @ дают совершенно не каноническое, приблизительно такое: "@ - означает текущую строку".
Неожиданно для меня на planetaexcel.ru про @ упоминания нет, хотя сами формулы затронуты.
Про структуру формул с @ можно почитать тут , на примерах все достаточно понятно, на этом же сайте есть полезная картинка структуры умной таблицы:

Картинка

Разница приблизительно следующая: если нужно просуммировать значения столбца "прибыль" из картинки выше, формула будет "=сумм(Имя_таблицы[Прибыль])", соответственно значение - 33. Если добавить символ собаки ("=сумм(Имя_таблицы[@Прибыль])"), то суммироваться будет только одна ячейка из столбца "Прибыль", а именно пересечение столбца "Прибыль" и строчки, в которой набита формула (для F3 - будет вычислено 5, для F4 -12). Если вбить формулу с @ ниже (или выше), чем заканчивается умная таблица, эксель выдаст ошибку.При работе с умной таблицей символ проставляется автоматически.

Прошу прощение за бестолковость объяснений, на мой взгляд самое понятное - поковыряться с этим. Памятую, что у Вас 2003 версия офиса, любопытно - позволяет ли он создавать умные таблицы?