Трансформация столбца в таблицу

Рет қаралды 57,514

6 жыл бұрын

Как превратить длинный столбец с данными в нормальную двумерную таблицу.
Скачать пример www.planetaexcel.ru/techniques...
Заходите в гости ↓↓↓
Мои книги planetaexcel.ru/books/
Мои онлайн-курсы www.planetaexcel.ru/learning/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel

Пікірлер: 68

@mentonization Жыл бұрын

Кажется первый раз в жизни пишу благодарность, за действительно полезный урок, спасибо большое)

@user-bm6re4tb5g 6 жыл бұрын

Давайте завалим Николая требованиями почаще выпускать видеоуроки!!!

@Rice8520 5 жыл бұрын

Одноразмерные блоки - это крайне редкий и илеальный случай. Как правило, приходится иметь дело с совершенно разноформатными данными, от специфики экспорта, до умения конкретного человека работать с таблицами. Поэтому перед сборкой таблицы приходится сначала причёсывать данные, чтобы в них можно было более менее легко узнать какую-то структуру и после отсечения фильтром всего лишнего приступать уже непосредственно к созданию базы. Иногда даже приходится делать преобразования в ворде, потому что некоторые данные вставляются одной строкой.

@user-jh9mc9gl7f Жыл бұрын

Нарешті знайшла те що мене цікавило. Дуже Вам дякую.

@Eugetic 4 ай бұрын

Николай, очень изящно и просто! Спасибо!

@user-bk4gp9wy5p 2 жыл бұрын

Мало знать, надо ещё уметь донести. Браво, Николай!. Спасибо за интересные видео-уроки.

@auto8008 6 жыл бұрын

Как всегда просто, красиво, профессионально!!! Спасибо

@kh.parviz5555 11 ай бұрын

Николай, огромное спасибо за урок, которые реально помог. С PowerQuery не понял зачем удалять пустоты в4 столбцах, а в двух оставить. Сделал с plex, быстро, просто, спасибо Вам!

@Alex67488 6 жыл бұрын

Супер! То, что и требовалось!)

@Egorka80 4 жыл бұрын

Два дня убил, чтобы найти подходящий способ, получалось громоздко и сложно. Здесь - всё просто и понятно. Спасибо, Николай!

@senge_v4871 6 жыл бұрын

Спасибо. Пример с PQ просто шикарен...

@JackFamous 6 жыл бұрын

спасибо за урок!))) радует, что учеников всё больше и под видео с вашими уроками всё больше лайков. Также круто, что вы начали внедрение PQ, посредством своих уроков (в том числе дополнение старых примерами с PQ) - так учиться гораздо проще! Огромное спасибо!!!

@yurd563 6 жыл бұрын

Спасибо за видеоурок!

@Albertus99 3 жыл бұрын

Большое спасибо Николай!

@natm18 3 жыл бұрын

Моя жизнь никогда не будет прежней) спасибо!

@progressor73 6 жыл бұрын

Круто! Спасибулище огроменное!

@alnigm2245 6 жыл бұрын

Здравствуйте Николай. Вопрос: когда перетягиваеш формулу для того чтобы одно из значении ячейки оставался постоянным ставим знак доллара, это упрошает работу. У меня задача в итоговой строке таблицы ввести формулу суммпроиз. Нужно сумма произведения значении столбца "а" на столбец "б". Однако формула записывается в режиме таблицы и я не могу зафиксировать знаком доллар значения в строке "а". В результате для суммпроиз столбца "а" на столбец "в" приходится заново вводить формулы. А их очень большое кол-во. Как зафиксировать значения строки таблицы? Пример "=$А$1" здесь понятно куда ставить доллар. А в данном случае "=Таблица1[[#Эта строка];[1]] непонятно куда влепить доллар.

@yurii_svetlov 2 жыл бұрын

Тысяча Благодарностей!

@user-cx3ze2ke2g 6 жыл бұрын

Спасибо. Интересное решение.

@Sig4childrens 3 жыл бұрын

В 1-й раз PLEX оказался проще, чем рукопашка. Зачет!

@metatron5783 3 жыл бұрын

Добрый день! А как быть если количество строк в блоке не равно? Очень нужно!!!

@nurzhaniembergenovich7750 6 жыл бұрын

Добрый день. Видео КЛАСС!!! Подскажите решение задачи когда группировка (или порции как сказано в видео) не равны или в количестве строк. Спасибо!

@niksonos 3 жыл бұрын

вот решение :) www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=133454&TITLE_SEO=133454-transponirovat-strochnyy-massiv-iz-blokov-raznogo-razmera-v-stolbtsy-b&tags=vba%2Cimage&_r=9345

@flexxodus 2 жыл бұрын

Офигенно!!!

@lekaaz 6 жыл бұрын

А как быть, если в блоках не стандартное количество ячеек т.е не по 7 ячеек в каждом блоге?

@metatron5783 3 жыл бұрын

здравствуйте! вы получили ответ на свой вопрос? меня тоже это интересует, очень!

@user-wb4mf7it4l 2 жыл бұрын

Мне тоже интересно, как это сделать… писать обработку наверное

@user-qn5by5iv7u 2 жыл бұрын

Это уже таблица с иерархией - тут всё сложнее...

@2008ulman 6 жыл бұрын

Просьба больше уроков с pq

@user-bh8td7vo4k Жыл бұрын

Николай спасибо за наглядный пример. вопрос если я загружаю исходный файл с текстового формата в PQ удаляю 23 верхние строки и удаляю пустые строки.Загружаю на лист. PLEX с макросом трансформации и изменить размеры выдает ошибку 428. Где моя ошибка? Я понимаю что лучше мне в таком алгоритме сформировать файл в PQ. Спасибо за ответ

@slavochkafomin 2 жыл бұрын

Николай, спасибо! Двссыл можно интерпретировать как «данные в ссылку»

@user-ju8ev5bf1k 2 жыл бұрын

Николай! Добрый вечер! Подскажите - а PowerQuery есть для MacOS?

@StanOwden Ай бұрын

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

@EugeneKovalevsky 5 жыл бұрын

Добрый день, а есть возможность обратной трансформации таблицы?! Будем благодарны за совет, как можно это сделать.

@romanbaer1 2 жыл бұрын

Николай, спасибо за видео. Делаю все в точности как в видео но результат не получается. При попытке создания таблицы получается только одна строка и та заполнена ошибками. ( в ошибке пишет :" Элементов в перечислении было слишком много для выполнения операции. ") скажите где я ошибся или как найти ошибку. спасибо.

@ytolk 6 жыл бұрын

Элегантно.

@user-gm6bm2vb1d 2 жыл бұрын

А если в блоках разное число строк , например где-то 7 а где-то 8. Не стандартные блоки

@MrTrylobeat 2 жыл бұрын

Очень интересная информация! Вот бы ещё анблокнуть Ютьюб на рабочем месте. Потому что без просмотра видео повторить урок не так легко🙏

@VLZaidiner 2 жыл бұрын

А если в блоке одна из ячеек - гиперссылка, как ее сохранить? В вашем примере, при преобразовании в 2х мерную таблицу, гиперссылка пропадает.

@user-ui2wu6mm9l 4 ай бұрын

Давно знаком с методом использования в таких случаях столбца сведения, но никак не могу поднять до конца, как он работает. Просветите, пожалуйста! Почему, если удалить столбец с индексом, сведение сделать не получится, выдаст ошибку? Ведь казалось бы столбец с индексом - вспомогательный, и собственно для сведения он не нужен.

@kanankarimov7654 6 жыл бұрын

почему PLEX не остается на верхнем вкладке после закрытия файла Excel 2016. Помогите пожалуйста

@andriyzas1995 6 жыл бұрын

Есть способ проще и очевиднее. Сразу формируем первый горизонтальный ряд ссылками на клетки столбца. b1 = a2, c1=a3, d1 =a4 , e1 = a5 и т.д. Первый ряд готов. Копируем ряд с формулами вниз. Пересчитываем таблицу F9 (если вручную) Копируем формулы в значения. Сортируем по колонке "А". Удаляем все строки в которых в "А" не содержит дату. Или - тоже самое отжимаем через фильтр. Для продвинутых пользователей можно использовать логику в формулах, типа "если ячейка в первой строке и ячейка в 4-й строке содержат числовые значения, то.." Еще наверное можно через транспонирование массива.

@user-fi2jc1xk4i 6 жыл бұрын

Можно, транспонирование - это первое, что пришло мне в голову при виде подобных данных. Выделяем семь ячеек строки, жмём =ТРАНСП, выделяем семь нужных ячеек в столбце, жмём Ctrl+Shift+Enter Копируем блок с первой заполненной строкой и семью пустыми строками, вставляем на восьмую строку и т.д. (потом можно увеличенными блоками). Копировать всё, вставить как значение. Удалить дубликаты.

@andriyzas1995 6 жыл бұрын

Это понятно. Хорошо если данных немного, тогда можно и копировать вручную. А если тысячи, десятки тысяч? Можно , конечно через "два а периоде" - сначала копируете первую строку в восьмую, потом первую и восьмую вместе, потом вместе 1-ю , 8-ю, 16-ю и 32-ю и т.д. , но это требует и времени и внимательности. Я имел виду через код VBA. Погуглил , есть множество решений, если интересно, то ищите "vba transpose column to row." Кстати мой способ работает даже если структрура нарушена. Например между первым блоком пустая строка, вторым три пустых, четвертым вообще нет пустот . Либо пустоты заполнены "мусором" . Номер строки не имеет значения. Главное чтобы была структура внутри блока из 7 клеток.

@user-fi2jc1xk4i 6 жыл бұрын

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

@andriyzas1995 6 жыл бұрын

Рад, что пригодилось. Если случиться потребность перегнать любую табличную информацию с экрана в Excel (прайс-лист на сайте например, или что угодно), то вот простой способ.

@user-pp6bg1ud4o 6 жыл бұрын

Andriy Zas Ваш первый способ хорош, но если в исходном столбце есть даты. А если сплошные цифры и текст, то отфильтровать не получится. В уроке способ универсальный. А в данном частном случае - хороший вариант.

@user-ny9hm4vg3d 3 жыл бұрын

Николай добрый вечер. Можно Вас попросить о помощи?

@user-se6su2ky5r 3 жыл бұрын

👏👏👏👏👏

@user-uw2rp9uh5q 8 ай бұрын

Добрый день! Кто-то сталкивался с таким случаем? При выгрузке товаров с х-ками, они располагаются не горизонтально(в столбцах), а вертикально (в строках) и артикул повторяется столько раз сколько характеристик у товара, затем следом идет другой товар и т.д. Может кто-то может подсказать выстроить эти х-ки горизонтально и убрать повторяющиеся артикулы? Заранее спасибо!

@klgdre Жыл бұрын

В Qwery функция столбец сведения не работает, ошибка "Элементов в перечислении было слишком много для выполнения операции". В чем может быть дело?

@Samurai_so2-Samir 8 ай бұрын

Подскажите пжл а если количество разделяемых строк в каждом блоке разное как разделить на столбцы?

@planetaexcel 7 ай бұрын

www.planetaexcel.ru/techniques/2/15810/

@Shurshalkin 2 жыл бұрын

все-таки power query это сила!!!

@0ReH34 Жыл бұрын

Добрый день. Что делать если количество строк не одинаково? На вашем сайте есть решение через пользовательский столбец, оно не работает. Выдает ошибки. И заполнение вниз заполняет темиже значениями, а не по порядку.

@planetaexcel Жыл бұрын

А как понять тогда, сколько ячеек будет в каждом столбце? Гляньте www.planetaexcel.ru/techniques/2/15810/ - возможно будет в тему.

@ilyasikm 4 жыл бұрын

какая версия экселя в видео?

@planetaexcel 4 жыл бұрын

Office 365

@ilyasikm 4 жыл бұрын

Nikolay Pavlov спасибо

@user-qn5by5iv7u 2 жыл бұрын

2022

@user-ui2wu6mm9l 4 ай бұрын

2024

@pafosclashroyale7388 4 жыл бұрын

Добрый день, у меня 2 столбца, из них надо сделать 1 строчку, ПОМОГИТЕ, НИКАК НЕ МОГУ СДЕЛАТЬ......

@IllIll.. 3 жыл бұрын

PaFoS Clash Royale просто транспонируй это

@as4tik 2 жыл бұрын

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

@planetaexcel 2 жыл бұрын

Можно попробовать копать в сторону "Трансформировать - Столбец сведения" (Transform - Pivot Column)

@as4tik 2 жыл бұрын

@@planetaexcel спасибо, и так тоже попробую. Пока нашел решение создав объединенный столбец перед pivot шага. Его сортировкой удается изменить порядок так. Хотя думал есть решение по-проще. Изучу еще Transform - Pivot column. Спасибо, за подсказку. И отдельная благодарность в целом за ролик. И не только за этот. Ваши ролики вдохновляют но совершенно иные подходы, до которых сам не скоро догадался бы. Всегда интересовал вопрос, "как вы к этому приходите?" Из MS мануалов или вот прямо догадываетесь? Ну вы такие интересные и действенные способы освещаете, что прямо вот не могу себе объяснить, - как? Как вы к этому приходите сами? Что мне сделать, что бы догадываться самому, а не спрашивать?