Трансформация столбца в таблицу
Вставка
- Опубліковано 7 лис 2017
- Как превратить длинный столбец с данными в нормальную двумерную таблицу.
Скачать пример www.planetaexcel.ru/techniques...
Заходите в гости ↓↓↓
Мои книги planetaexcel.ru/books/
Мои онлайн-курсы www.planetaexcel.ru/learning/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Давайте завалим Николая требованиями почаще выпускать видеоуроки!!!
Кажется первый раз в жизни пишу благодарность, за действительно полезный урок, спасибо большое)
Одноразмерные блоки - это крайне редкий и илеальный случай. Как правило, приходится иметь дело с совершенно разноформатными данными, от специфики экспорта, до умения конкретного человека работать с таблицами. Поэтому перед сборкой таблицы приходится сначала причёсывать данные, чтобы в них можно было более менее легко узнать какую-то структуру и после отсечения фильтром всего лишнего приступать уже непосредственно к созданию базы. Иногда даже приходится делать преобразования в ворде, потому что некоторые данные вставляются одной строкой.
Как всегда просто, красиво, профессионально!!! Спасибо
Мало знать, надо ещё уметь донести. Браво, Николай!. Спасибо за интересные видео-уроки.
спасибо за урок!))) радует, что учеников всё больше и под видео с вашими уроками всё больше лайков. Также круто, что вы начали внедрение PQ, посредством своих уроков (в том числе дополнение старых примерами с PQ) - так учиться гораздо проще! Огромное спасибо!!!
Николай, очень изящно и просто! Спасибо!
Два дня убил, чтобы найти подходящий способ, получалось громоздко и сложно. Здесь - всё просто и понятно. Спасибо, Николай!
Супер! То, что и требовалось!)
Спасибо. Пример с PQ просто шикарен...
Николай, огромное спасибо за урок, которые реально помог. С PowerQuery не понял зачем удалять пустоты в4 столбцах, а в двух оставить. Сделал с plex, быстро, просто, спасибо Вам!
Спасибо за видеоурок!
Большое спасибо Николай!
Нарешті знайшла те що мене цікавило.
Дуже Вам дякую.
Круто! Спасибулище огроменное!
Спасибо. Интересное решение.
Тысяча Благодарностей!
Моя жизнь никогда не будет прежней) спасибо!
Здравствуйте Николай. Вопрос: когда перетягиваеш формулу для того чтобы одно из значении ячейки оставался постоянным ставим знак доллара, это упрошает работу. У меня задача в итоговой строке таблицы ввести формулу суммпроиз. Нужно сумма произведения значении столбца "а" на столбец "б". Однако формула записывается в режиме таблицы и я не могу зафиксировать знаком доллар значения в строке "а". В результате для суммпроиз столбца "а" на столбец "в" приходится заново вводить формулы. А их очень большое кол-во. Как зафиксировать значения строки таблицы? Пример "=$А$1" здесь понятно куда ставить доллар. А в данном случае "=Таблица1[[#Эта строка];[1]] непонятно куда влепить доллар.
Офигенно!!!
А как быть, если в блоках не стандартное количество ячеек т.е не по 7 ячеек в каждом блоге?
здравствуйте! вы получили ответ на свой вопрос? меня тоже это интересует, очень!
Мне тоже интересно, как это сделать… писать обработку наверное
Это уже таблица с иерархией - тут всё сложнее...
В 1-й раз PLEX оказался проще, чем рукопашка. Зачет!
Просьба больше уроков с pq
Добрый день. Видео КЛАСС!!! Подскажите решение задачи когда группировка (или порции как сказано в видео) не равны или в количестве строк. Спасибо!
вот решение :)
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
При использовни второго способа результат после пивота получился со сдвигом каждого сегмента данных на одну колонку вправо. В итоге получился несконченный каскад данных, а не упорядоченная таблица. Сейчас попробую способ с формулами.
Элегантно.
Добрый день, а есть возможность обратной трансформации таблицы?! Будем благодарны за совет, как можно это сделать.
Николай! Добрый вечер! Подскажите - а PowerQuery есть для MacOS?
Добрый день! А как быть если количество строк в блоке не равно? Очень нужно!!!
👏👏👏👏👏
Николай, спасибо! Двссыл можно интерпретировать как «данные в ссылку»
Николай, спасибо за видео. Делаю все в точности как в видео но результат не получается. При попытке создания таблицы получается только одна строка и та заполнена ошибками. ( в ошибке пишет :" Элементов в перечислении было слишком много для выполнения операции.
") скажите где я ошибся или как найти ошибку. спасибо.
Николай спасибо за наглядный пример. вопрос если я загружаю исходный файл с текстового формата в PQ удаляю 23 верхние строки и удаляю пустые строки.Загружаю на лист. PLEX с макросом трансформации и изменить размеры выдает ошибку 428. Где моя ошибка? Я понимаю что лучше мне в таком алгоритме сформировать файл в PQ. Спасибо за ответ
Есть способ проще и очевиднее.
Сразу формируем первый горизонтальный ряд ссылками на клетки столбца.
b1 = a2, c1=a3, d1 =a4 , e1 = a5 и т.д.
Первый ряд готов.
Копируем ряд с формулами вниз. Пересчитываем таблицу F9 (если вручную)
Копируем формулы в значения. Сортируем по колонке "А". Удаляем все строки в которых в "А" не содержит дату.
Или - тоже самое отжимаем через фильтр.
Для продвинутых пользователей можно использовать логику в формулах, типа "если ячейка в первой строке и ячейка в 4-й строке содержат числовые значения, то.."
Еще наверное можно через транспонирование массива.
Можно, транспонирование - это первое, что пришло мне в голову при виде подобных данных. Выделяем семь ячеек строки, жмём =ТРАНСП, выделяем семь нужных ячеек в столбце, жмём Ctrl+Shift+Enter Копируем блок с первой заполненной строкой и семью пустыми строками, вставляем на восьмую строку и т.д. (потом можно увеличенными блоками). Копировать всё, вставить как значение. Удалить дубликаты.
Это понятно. Хорошо если данных немного, тогда можно и копировать вручную. А если тысячи, десятки тысяч? Можно , конечно через "два а периоде" - сначала копируете первую строку в восьмую, потом первую и восьмую вместе, потом вместе 1-ю , 8-ю, 16-ю и 32-ю и т.д. , но это требует и времени и внимательности.
Я имел виду через код VBA. Погуглил , есть множество решений, если интересно, то ищите "vba transpose column to row."
Кстати мой способ работает даже если структрура нарушена. Например между первым блоком пустая строка, вторым три пустых, четвертым вообще нет пустот . Либо пустоты заполнены "мусором" . Номер строки не имеет значения. Главное чтобы была структура внутри блока из 7 клеток.
Аааа, VBA... Проверила Ваш первый способ, он прекрасен. Действительно быстро и просто. Я не знала, что фильтр выделяет дату в отдельную группу и достаточно снять один флажок (или больше по кол-ву лет), для фильтрации по столбцу с исходными данными.
Рад, что пригодилось.
Если случиться потребность перегнать любую табличную информацию с экрана в Excel (прайс-лист на сайте например, или что угодно), то вот простой способ.
Andriy Zas Ваш первый способ хорош, но если в исходном столбце есть даты. А если сплошные цифры и текст, то отфильтровать не получится. В уроке способ универсальный. А в данном частном случае - хороший вариант.
Очень интересная информация! Вот бы ещё анблокнуть Ютьюб на рабочем месте. Потому что без просмотра видео повторить урок не так легко🙏
А если в блоке одна из ячеек - гиперссылка, как ее сохранить?
В вашем примере, при преобразовании в 2х мерную таблицу, гиперссылка пропадает.
А если в блоках разное число строк , например где-то 7 а где-то 8. Не стандартные блоки
почему PLEX не остается на верхнем вкладке после закрытия файла Excel 2016. Помогите пожалуйста
Давно знаком с методом использования в таких случаях столбца сведения, но никак не могу поднять до конца, как он работает. Просветите, пожалуйста! Почему, если удалить столбец с индексом, сведение сделать не получится, выдаст ошибку? Ведь казалось бы столбец с индексом - вспомогательный, и собственно для сведения он не нужен.
Добрый день! Кто-то сталкивался с таким случаем? При выгрузке товаров с х-ками, они располагаются не горизонтально(в столбцах), а вертикально (в строках) и артикул повторяется столько раз сколько характеристик у товара, затем следом идет другой товар и т.д. Может кто-то может подсказать выстроить эти х-ки горизонтально и убрать повторяющиеся артикулы? Заранее спасибо!
В Qwery функция столбец сведения не работает, ошибка "Элементов в перечислении было слишком много для выполнения операции". В чем может быть дело?
Николай добрый вечер. Можно Вас попросить о помощи?
Подскажите пжл а если количество разделяемых строк в каждом блоке разное как разделить на столбцы?
www.planetaexcel.ru/techniques/2/15810/
Добрый день.
Что делать если количество строк не одинаково?
На вашем сайте есть решение через пользовательский столбец, оно не работает. Выдает ошибки.
И заполнение вниз заполняет темиже значениями, а не по порядку.
А как понять тогда, сколько ячеек будет в каждом столбце? Гляньте www.planetaexcel.ru/techniques/2/15810/ - возможно будет в тему.
какая версия экселя в видео?
Office 365
Nikolay Pavlov спасибо
2022
2024
Добрый день, у меня 2 столбца, из них надо сделать 1 строчку, ПОМОГИТЕ, НИКАК НЕ МОГУ СДЕЛАТЬ......
PaFoS Clash Royale просто транспонируй это
Друзья, а как бы вы выкрутились из ситуации, если бы обнаружили, что блоки хоть и одинакового размера, но порядок в каждом блоке оказываются разные. А значит функцию "заполнение вверх" применить не выйдет. Так как заполнение вверх дает верный результат для части блоков, а для других надо заполнять вниз.
Можно попробовать копать в сторону "Трансформировать - Столбец сведения" (Transform - Pivot Column)
@@planetaexcel спасибо, и так тоже попробую. Пока нашел решение создав объединенный столбец перед pivot шага. Его сортировкой удается изменить порядок так. Хотя думал есть решение по-проще.
Изучу еще Transform - Pivot column. Спасибо, за подсказку.
И отдельная благодарность в целом за ролик. И не только за этот. Ваши ролики вдохновляют но совершенно иные подходы, до которых сам не скоро догадался бы. Всегда интересовал вопрос, "как вы к этому приходите?" Из MS мануалов или вот прямо догадываетесь? Ну вы такие интересные и действенные способы освещаете, что прямо вот не могу себе объяснить, - как? Как вы к этому приходите сами? Что мне сделать, что бы догадываться самому, а не спрашивать?