Как из сводной таблицы сделать плоскую в Power Query
Когда мы получаем данные из выгрузки или от коллег, часто возникает проблема со структурой таблицы. Встаёт вопрос: «Как привести данные к нужной структуре, чтоб построить удобный и простой отчёт?».
Рассмотрим как должна выглядеть правильная структура таблицы. Правила будут следующими:
- У каждого столбца должен быть заголовок.
- В каждом столбце данные должны быть однородные, т.е. одного типа. Например, если столбец несет под собой значения даты, то в каждой строке в столбце «Дата» должен быть единый тип.
1. В заголовках имеем диапазон по дате или другим категориям
- через CTRL выделить все столбцы с диапазоном, в данном случае кварталы;
- перейти во вкладку «Преобразование»;
- найти кнопку «Отменить свертывания столбцов».
Бывает, что столбец с названием показателя вынесен отдельно:
Выделим нужный столбец и нажмем на кнопку «Столбец сведения», после чего откроется меню настройки.
Во вкладке «Столбец значений» выбираем значения, которые попадут в новые столбцы. Во вкладке «Функция агрегированного значения» выбираем пункт «Не агрегировать».
По окончании проделанных шагов получаем таблицу на рисунке ниже:
3. Сложная комбинация пунктов 1 и 2.
При комбинации случаев 1 и 2 первым делом необходимо избавиться от пустых значений null. Для этого выберем первый столбец и нажмем «Заполнить значения вниз».
Пустые значения первого столбца пропали, а на их месте теперь название филиала, которое было выше.
Далее перевернем таблицу, для этого нажмем на кнопку «Транспонировать», после выбираем «Заполнить значения вниз», как указано на рисунке ниже:
Следующим шагом избавимся от нескольких заголовков. Для этого необходимо объединить столбцы и нажать на кнопку «Объединить столбцы»:
Столбцы склеиваются в один:
Транспонируем таблицу обратно и используем первую строку в качестве заголовка:
Далее действуем как в предыдущих примерах. Выделяем нужный диапазон и нажимаем «Отменить свертывание столбцов»:
Разделим ранее склеенный столбец, чтобы отделить год.
Выделим столбец, где указаны кол-во и сумма, далее нажмем столбец сведения:
В результате получим простую таблицу, с которой удобно работать и которую легко анализировать:
Наши курсы по Power BI:
Курс Аналитик BI
Курс DAX Mastering
Курс Финансовый анализ в Power BI
Нормализация данных отчёта (вывод в виде плоской таблицы)
Уважаемые друзья, знаю что данная тема поднималась множество раз, однако предлагаемые пути решения у меня не срабатывают..
К сути проблемы — формирую отчёт "обороты между субконто", выгружается довольно много данных, пересохраняю это всё в EXCEL для обработки в сводной таблице, однако данные в таком виде для этого не подходят — выводятся многоэтажные шапки и данные выстроены в вертикальном порядке.
Один из вариантов это пользоваться макросом для нормализации таблицы. Но встречал на просторах интернета и предложения следующего характера — можно попробовать убрать группировку из параметров запроса, перенеся всё это в дополнительные поля отчёта, однако в этом случае у меня выходит отчёт только с номерами счетов и суммами, без наименований контрагентов и материалов.
Как получить нормализованную таблицу с наименованием материалов, контрагентов, счетов и сумм? Как правильно выстроить фильтры отчёта, чтобы получить плоскую таблицу, т.е. когда все данные по одному контрагенту и материалу были вписаны в одну строку? Скрины в приложении.
Заранее признателен за помощь!
Вывод данных из БД FireBird в виде Excel-отчета
В своей программе добавил возможность создание Excel-отчета, и столкнулся с такой проблемой: когда.
Суммирование данных и вывод данных в виде отчёта
Здравствуйте . Нужно сделать вот что,в базе есть таблица "Статистика повреждений". В ней.
Вывод данных в виде таблицы в C++
Существует ли возможность вывода (записи) данных в виде таблиц в C++?
Вывод данных из БД в виде таблицы
Доброго времени суток! В общем имеется БД с фото. Нужно из нее вывести фото вряд по три штуки ну и.
Скорее всего только внешним отчетом.
Ищите на инфостарте какой-нибудь вариант.
Удалять совсем поля группировки в настройках пробовали?
Вывод данных в виде таблицы
Подскажите пожалуйста как вывести элементы из таблицы sql (список юзеров) в виде таблицы. я.
Вывод данных в виде таблицы
Есть таблица. Мне нужно написать эту таблицу, на языке паскаль так чтобы, автоматически происходило.
Вывод данных из БД в виде таблицы
Добрый день. У меня на странице нужно отобразить данные в виде таблиц. Но, не получается. <?php.
Вывод данных в виде таблицы
Здравствуйте, помогите сделать шапку для фио студентов фио | номер зачётки | наименование.
Подготовить таблицу — строки
Команда способна преобразовать в удобный для анализа вид практически любую «многоуровневую» таблицу, сформированную в 1С. Пригодится тем, кто часто сталкивается с необходимостью анализа данных, выгруженных из 1С таблиц. Например, вот такой вот обычный отчет из 1С:
Построить из такого отчета сводную таблицу не то, чтобы нереально — но вручную не на один час нудной работы. И то все зависит от кол-ва строк и столбцов. При этом формулы здесь явно не помогут и остается два пути: руками или при наличии навыков писать свой код, который хоть часть данных приведет в порядок. Ну и конечно же, это можно сделать при помощи команды MulTEx Подготовить таблицу — строки.
Самая главная проблема в отчете выше — это так называемая группировка строк при формировании отчета в 1С. На примере выгрузки выше к группировке строк относятся: Контрагент, Номенклатура и Регистратор(Документ). Сама сложность такой группировки заключается в том, что каждая строка группировки записывается в таблицу со своим отступом от левого края ячейки, создавая некую ступенчатую иерархию данных, из которой можно понять какие товары в какую группу и подгруппу входят. Например, на картинке выше к Контрагенту относятся Автокомпонент-ТЗК и Азов-ТЗК . Это верхний уровень группировки. Следующий уровень — Номенклатура: Р86023 Pehapol разбавитель и PLP02004 разбавитель . И последний, третий уровень — Регистратор(Документ), к нему относятся все светло-желтые строки — «Реализация товаров и услуг. » .
Вторая проблема — данные по каждому товару разбиты на месяцы и в каждом месяце три отдельных категории значений: Количество (в ед. хранения), Цена продажи и Цена закупки. И при этом месяцы расположены не в строках, а в столбцах. Что так же затрудняет работу с таблицей формулами, не говоря о сводных таблицах.
Если отчет выше преобразовать в «плоский» вид, то получится такая таблица:
Как видно — из такой таблицы уже свободно можно делать сводную таблицу, экспорт данных в другие программы и т.п.
Примечание: Заголовки в обеих таблицах выделены цветами специально, чтобы показать соответствие данных в обеих таблицах и как они распределяются после преобразования. В реальных отчетах такой расцветки нет.
И именно подобные преобразования и способна сделать команда MulTEx Подготовить таблицу — строки. Достаточно настроить один раз команду для обработки отчета и далее лишь использовать эти настройки при необходимости, т.к. для каждого вида отчета можно хранить свои настройки.
Чтобы более точно понять принцип работы всех настроек и команды в целом лучше применить некоторые определения для данных:
- Свойства строки — данные, которые характеризуют строку (Наименование, Артикул, Код, Цвет, Номенклатура, Документ и т.п.)
- Значения строки — как правило числовые значения(Количество, Цена, Сумма и т.п.), относящиеся к конкретному товару, документу и т.п. Т.е. непосредственно количественные данные нижнего уровня таблицы (строки с максимальным количеством отступов)
Схематично применение терминов к таблице выше выглядит так:
Настройки
После вызова команды появится окно настроек: 
В левой части расположенные основные настройки, отвечающие за непосредственно преобразование. В правой части хранятся шаблоны настроек. При первом запуске команды список шаблонов пуст — он наполняется вручную один раз для каждого отчета.
Соответствия заголовков данным в строках — специальное поле, в котором указывается какому элементу группировки какое количество отступов соответствует. Может содержать неограниченное количество соответствий. В первом столбце поля указывается наименование столбца, которое будет записано в результирующую таблицу, во втором — количество отступов для данного наименования. Определять отступы самостоятельно нет необходимости. При изменении поля с числом отступов просто укажите на листе ячейку, отступы в которой надо определить и они будут записаны в это поле.
В примере для большего понимания эти наименования соответствуют таковым в самой таблице, но на деле их можно взять либо из ячейки, либо записать вручную — главное здесь отступы. Строки с наибольшим количеством отступов(на картинке выше это Регистратор (Документ)) считаются как данные нижнего уровня и данные этих строк будут определены как Значения строки и будут записаны в результирующую таблицу. Остальные строки(с меньшим количеством отступов) будут записаны в каждую строку результирующей таблицы как Свойства строки(см.схему выше).
Важно: для более корректного преобразования данных лучше указывать соответствия отступов в порядке возрастания(от минимального к максимальному: 0, 1, 2 или 0, 3, 6 и т.д.).
Примечание: отступы определяются исключительно в одном столбце — в том, в котором расположена Первая ячейка в строках данных
Чтобы добавить соответствие необходимо:
- либо дважды щелкнуть мышью по любой пустой строке поля
- либо нажать сочетание клавиш Ctrl+Plus(кнопка + на клавиатуре)
- либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Добавить
Чтобы изменить существующее соответствие необходимо:
- либо дважды щелкнуть мышью по строке поля с нужным соответствием(при этом в зависимости от того над каким пунктом был щелчок активируется либо Наименование, либо Кол-во отступов)
- либо нажать сочетание клавиш Ctrl+Right(стрелка вправо на клавиатуре)
- либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Изменить
Чтобы удалить существующее соответствие необходимо:
- либо нажать сочетание клавиш Ctrl+Delete
- либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Удалить
Если в таблице нет отступов, то в любом случае необходимо указать хотя бы один уровень и записать для него количество отступов, равное нулю. См.пример таких таблиц и настроек: пример 1>>, пример 2 >>
Первая ячейка в строках данных( B11 ) — указывается одна ячейка в таблице, с которой начинаются реальные данные (не заголовки). В столбце указанной ячейки, начиная с этой ячейки программа будет определять отступы и в зависимости от количества отступов распределять остальные данные.
Заголовки переменных данных
По сути этот блок отвечает за числовые данные, которые необходимо отразить в результирующей таблице. При этом если флажок Есть повторяющиеся заголовки значений не установлен, то Значения строки берутся из столбцов, указанных в поле Заголовки расположены в ячейках. Если флажок установлен, то Значения строки берутся из столбцов, указанных в поле Заголовки в ячейках(раздела Есть повторяющиеся заголовки значений) или Подзаголовки в ячейках(раздела Есть повторяющиеся заголовки значений).
Заголовки расположены в ячейках( C8:E8 ) — указывается диапазон ячеек одной строки заголовка. Если нет повторяющихся заголовков значений, то данные указанных столбцов будут определяться как Значения строки. Если есть повторяющиеся заголовки значений, то значения ячеек указанных столбцов буду определяться как Свойства строки .
Есть повторяющиеся заголовки значений — если в отчете имеются данные, повторяющиеся с каким-то шагом и эти данные необходимо включить в итоговый отчет, то необходимо установить этот флаг.
— Заголовки повторяются с шагом — указывается целое число больше нуля, определяющее с какой периодичностью повторяются данные. В рассматриваемой таблице данные расположены таким образом, что Количество (в ед.хранения), Цена продажи и Цена закупки повторяются для каждого месяца. Т.е. на один месяц три столбца данных. Значит шаг необходимо выставить равный 3.
— Заголовки в ячейках( F8:AO8 ) — указывается диапазон ячеек (или одна ячейка), одной строки заголовка, данные из столбцов которых необходимо записать построчно для каждого значения строки. Если нет подзаголовков значений, то данные указанных столбцов будут определяться как Значения строки . Если есть подзаголовки значений, то значения ячеек указанных столбцов буду определяться как Свойства строки .
— Подзаголовки в ячейках( F10:H10 ) — если указаны, то Значения строки будут взяты из столбцов, указанных в этом поле. Значения из столбцов поля Заголовки в ячейках будут определяться как Свойства строки и записываться в отдельный столбец(соответствующий названию столбца). Также, если подзаголовки указаны и шаг повторения больше 1, то из Заголовков в результирующую таблицу будет записываться одно значение на весь повторяющийся блок подзаголовков(в примере используется шаг — 3):
Определять последний столбец по заголовкам — если установлен, то для заголовков переменных данных достаточно будет указать только первую ячейку заголовка — остальные данные будут определены на основании строки заголовка. При этом если данные в различных заголовках и подзаголовках заканчиваются в различных столбцах — в качестве последнего столбца данных будет взят самый дальний столбец, т.е. наибольший из всех. Если данный пункт отключен, то данные из таблицы будут браться исключительно из указанных в полях столбцов.
Например, если в таблице выше подзаголовки записаны в столбцах F10:AL10 , то в поле Подзаголовки в ячейках достаточно будет указать ячейку F10 — последняя ячейка( AL10 ) будет определена автоматически. Если флажок снят — то будут обработаны только те столбцы точек, которые указаны в поле Подзаголовки в ячейках.
Для чего это нужно: иногда в таблицах в конце присутствует блок итогов и не всегда эти итоги нужны в таблице для анализа. Если указать определение автоматически — то итоги так же попадут в результирующий лист и будут только мешать.
Записывать каждый подзаголовок в новую строку — если установлен, то подзаголовки будут переносится не блоком в одну строку, а каждая ячейках подзаголовка будет записана в новую строку. При этом все свойства строки будут продублированы для значения подзаголовка. См.пример применения >>
Переносить в новую таблицу только данные с непустыми ячейками — если установлен, то в результирующую таблицу будут перенесены только те строки ячеек полей, определенных как Значения строки , по которым в ячейке есть хоть какое-то значение.
Для чего это нужно: на примере таблицы выше: в ячейках от F13 до H19 нет данных. Если флажок установлен — то в результирующем отчете будет как минимум на 7 ненужных строк меньше. В большинстве случаев этот флажок лучше ставить, но бывают ситуации, когда даже пустые строки и ячейки важны для анализа(например, определить, по каким позициям операции отсутствуют).
Считать последнюю строку группы максимальным уровнем — применяется в случаях, когда структура исходной таблицы построена таким образом, что в каждой группе данных не всегда есть максимальный доступный уровень отступа:
На картинке видно, что для группы «Материалы и запчасти на Контр по ЛЗК» максимальный уровень отступов 16, для «Иные запчасти» — 14, а для «ОС до 40 тыс.руб» — 12. Если галочка Считать последнюю строку группы максимальным уровнем будет снята, то данные будут перенесены некорректно, т.к. даже если указать максимальный уровень 16, то данные с меньшим количеством отступов просто не попадут в итоговую таблицу в качестве Значения строки . Если же галочка Считать последнюю строку группы максимальным уровнем будет установлена — то даже если уровень будет меньше 16 программа определит, что это последний уровень в группе и перенесет именно эти строки в итоговый отчет как Значения строки .
Шаблоны настроек
Создание нового шаблона
Создавать шаблоны настроек просто — достаточно заполнить левую часть формы для обработки какого-либо отчета, после чего перейти в правую часть, выбрать создать новый шаблон, вписать имя шаблона в соответствующее поле и нажать Сохранить текущие настройки в шаблон. Лучше сразу давать понятные имена шаблонам(выгрузка_1С_продажи, 1С8_остатки и т.п.). Имя шаблона не должно содержать пробелов, знаков препинания(кроме нижнего подчеркивания) и не должно начинаться с цифры. Если имя шаблона будет задано неверно — надстройка выдаст предупреждение и необходимо будет изменить имя в соответствии с требованиями.
По сути на этом создание шаблона заканчивается. Он успешно запоминается надстройкой и теперь будет доступен всегда.
Для применения шаблона необходимо выбрать нужный шаблон в списке шаблонов (правая часть формы) и нажать Применить настройки выбранного шаблона. Поля настроек (левая часть формы) будут заполнены в соответствии с шаблоном. Так же заполнить поля из шаблона можно двойным кликом левой кнопкой мыши на названии шаблона.
Для изменения шаблона необходимо выбрать нужный шаблон в списке шаблонов (правая часть формы), при необходимости заполнить поля настроек (двойной клик мыши или Применить настройки выбранного шаблона). После этого необходимо внести требуемые настройки в левой части формы. Выбрать изменить выбранный шаблон и нажать Сохранить текущие настройки в шаблон. Настройки выбранного шаблона будут изменены.
Для удаления шаблона необходимо выбрать шаблон из списка и нажать кнопку Удалить выбранный. Следует помнить, что данная операция необратима и шаблон будет удален без возможности восстановления.
Удаление всех шаблонов
Для удаления всех шаблонов из списка необходимо нажать кнопку Удалить все шаблоны. Следует помнить, что данная операция необратима и все шаблоны будут удалены без возможности восстановления.
Другие примеры таблиц и настроек
Пример обработки оборотно-сальдовой ведомости: 
Для преобразования такой таблицы в плоскую необходимо будет применить такие настройки формы: 
В результате получим такую таблицу: 
Пример отчета без повторяющихся заголовков: 
Настройки формы для преобразования такой таблицы в плоскую: 
В результате получим такую таблицу:
Пример настроек с использование пункта Записывать каждый подзаголовок в новую строку. Наиболее актуально это для таблиц примерно следующего вида:
Для преобразования в удобную таблицу необходимо применить следующие настройки: 
результатом будет такая таблица:
Попробовав преобразовать пару различных таблиц можно достаточно быстро понять принцип.
Преобразование отчета из какой-то левой БД в плоскую таблицу в Excel
Приложить файл примера не знаю как. Если кто подскажет, добавлю файл.
Ой, как я «люблю» отчеты из 1С анализировать! (Нет, нет и еще раз нет!) Там такие неудобные шапки, сводки, да еще объединенные ячейки везде. Выполнить анализ в Excel примерно такого отчета без предварительных «танцев с бубном» раньше было сложно:
Сейчас же у нас есть волшебный Power Query, позволяющий в несколько шагов очистить данные от лишних заголовков и объединений, и «развернуть» сводку данных в плоскую таблицу.
Итак, шаги по порядку.
1 шаг. Сформировать запрос к таблице
Лучше делать это из чистой книги.
1. На вкладке Данные выбираем Получить данные — Из файла — Из книги, находим файл, нажимаем Импорт:
2. Выбираем лист с таблицей, нажимаем Преобразовать (данные):
3. Пугаемся того, что открылось. Знакомимся с окном Power Query и открытой импортированной таблицей:
Для тех, что ранее не работал с окном Power Query:
В правой части окна видно имя запроса (его можно изменить, если необходимо) и примененные изменения (шаги). В списке шагов будет видна последовательность всех примененных действий. Если нужно, шаги можно удалять кнопкой «Х» рядом с соответствующим шагом.
Сверху в окне Power Query находятся командные вкладки для преобразования данных.
2 шаг. Преобразование данных
1. Удаление лишних строк
Так как данные листа импортированы полностью, а таблица начинается лишь с 4-й строки на листе, нужно удалить первые 3 строки: вкладка Главная — Удалить строки — Удаление верхних строк. В появившемся окне ввести «3», нажать ОК:
2. Повышение заголовков
Еще нужно удалить лишние столбцы, но пока столбцы без названий, определять их неудобно. Чтобы первую строку таблицы установить заголовками, на Главной вкладке нажимаем Использовать первую строку в качестве заголовков:
3. Переименуем столбец Column3 в «Наименование товара», т.к. здесь не было понятного заголовка: двойной клик по наименованию Column3:
4. Удаляем лишние столбцы
Все столбцы, что идут с названием ColumnХХ — пустые. Они получились в результате разъединения объединенных ячеек. Чтобы их удалить, на Главной вкладке нажимаем Выбор столбцов — Выбор столбцов, а затем снимаем флажки со всех столбцов с заголовками ColumnХХ, а также со столбца Итого:
Результат, который получается на данный момент:
5. Заполняем пустые строки в столбце Категория
Выделив этот столбец, на вкладке Преобразование выбираем Заполнить — Заполнить вниз. По всему столбцу будут заполнены категории товаров:
6. Удалим пустые строки
Оставшиеся пустые строки (null) содержат либо ненужные заголовки, либо суммы по категориям товаров. Эти сведения для плоской таблицы не нужны. Убрать их можно фильтрацией по столбцу Наименование товара: нажав кнопку фильтра столбца, убрать флаг NULL:
Результат после 6-го действия:
7. «Развернем» данные
Нужно выделить первые 2 столбца (Категория и Наименование товара), затем на вкладке Преобразование в команде Отменить свертывание столбцов выбрать Отменить свертывание других столбцов:
8. Данные из столбцов будет расположены в стоки, появятся столбцы Атрибут с датами (месяцами продаж) и Значение с суммами. Имеет смысл их сразу переименовать в Период и Сумма соответственно:
9. Для корректного анализа данных Периоду нужно присвоить формат данных Дата, а Сумме — формат Валюта: кнопка «АВС123» в заголовках столбцов:
Результат после 9 действия (4 столбца и 126 строк):
10. Последнее действие — выгрузить данные на лист Excel: команда Закрыть и Загрузить на вкладке Главная:
На листе Excel появится таблица и сведения о запросе:
Область с именем запроса не нужна для отображения, ее можно закрыть.
А дальше уже работаем с получившейся таблицей, как необходимо.
Например, можно сделать такой отчет:
Видео со всеми шагами:
665 постов 14.8K подписчика
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
А в 1с сформировать отчет в нужном виде не судьба? почти все типовые отчеты имеют настройки где за меньшее количество действий можно настроить свой вариант, например отключить группировки, вывести дополнительные поля из доступных и тд..
Очень круто! Но зачем?
Любой 1С отчет выполненный в СКД (а на скрине похоже как раз такой) можно и без танцев с бубном вывести в виде плоской таблицы за полминуты примерно, прямо в самой 1С.
Я так понял ваша цель это использовать данные из 1С в качестве табличного источника данных для Excel. Если так, позволю себе дополнить ваш гайд другим, где тот же результат можно получить на стороне 1С.
Для того чтобы любой отчет получить в виде плоской таблицы нужно:
1. В меню «Еще» выбрать пункт «Настройки».
2. Откроется диалоговое окно настроек схемы компоновки в котором нужно перейти на вкладку «Структура». Там будет структура отчета, она может быть весьма сложной, но это не имеет значения, потому что для вывода в плоскую таблицу нам нужно будет ее отключить.
3. Включаем вид настроек «Расширенный» (слева внизу переключатель). Удаляем текущую структуру.
4. Идем в меню «Еще», нажимаем кнопку «Добавить таблицу».
5. В таблице настраиваем состав строк и колонок так как нам надо. В вашем случае в строках будет Категория, Наименование товара и Период, а колонки добавлять не надо, т.к. сумма это ресурс и она сама встанет куда надо автоматически.
6. Нажимаем «Закрыть и сформировать» и вуаля — перед нами данные в виде плоской таблицы.
7. Сохраняем полученный отчет в файл и работаем с ним в Excel.
Данную структуру, кстати, можно сохранить в виде варианта отчета и больше не париться а просто использовать его тогда когда надо уже настроенный. Исходный вариант также сохранится и не исчезнет.
На рисунке я привел пример настройки.
Самое сложное у пользователя обычно бывает с колонками, они пытаются добавить числовые показатели в колонки и получают ошибку формирования отчета. Это потому что суммы отчета как правило это ресурсы и они будут добавлены в таблицу автоматически, т.е. для плоской таблицы в основном достаточно только настроить состав строк, т.е. разрезов вашей таблицы.
если отчет на СКД, то можно его в прямо в 1с, настройками, в плоскую таблицу вывести.
Ёмоё! Я таких извращенцев дано не встречал! Настройки отчета не? Запилить свой отчет на СКД как вам нужно не? Вы бы еще потом из Exel его в блокнот эспортировали.
Чет если честно — то проще разгруппировать строки, удалить лишние столбцы и все.
Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel
Друзья, всем привет. В комментариях, да и на занятиях, довольно часто поднимается тема преобразования чисел, сохранённых как текст, в рабочий числовой формат, поэтому решил про это написать. Как обычно, здесь не будет никаких откровений и чего-то нового. Так или иначе, всё то, о чём напишу, уже давно есть в интернете. Просто решил собрать всё в одном месте. Может, кому-то будет полезно (я на это очень надеюсь).
Небольшое вступление. Чего рассматривать не будем, и почему такое вообще происходит.
Сразу оговорюсь, что не буду здесь рассматривать случаи, когда в ячейках кромешный ад и вакханалия вроде «10 руб», «33 попугая» и так далее. Хотя один способ может и с такими ячейками помочь. Будем разбирать православные ячейки, в которых только число, но, по тем или иным причинам, оно сохранено как текст.
Причины могут быть разными, но чаще всего такая ситуация случается, когда мы импортируем данные в Excel из какой-нибудь другой корпоративной программы или копируем из интернета/другого источника. Выгрузил данные в Excel, пытаешься что-то просуммировать, а на выходе получаешь шиш с маслом:
Excel любезно даёт понять, что число сохранено как текст, добавляя в такие ячейки зелёный треугольник в левый верхний угол (не касается дат, сохранённых как текст, такие ячейки никак не выделяются). Интересный момент: если суммировать ячейки вручную, то результат будет нормальным.
Вся загвоздка в том, что если пойти путём обычной смены формата на числовой (Главная — Число — в выпадающем списке выбрать Числовой), результата это не даст. Придётся взять бубен и немного потанцевать с ним.
И так, что же можно сделать.
Важно! Прежде чем пробовать какой-то из способов, убедитесь, что у вас в принципе правильные разделители указаны. Если система использует запятую как разделитель групп разрядов, а у вас точки везде, нужно просто через поиск и замену поменять точку на запятую (и наоборот, если в качестве разделителя система использует точку).
Способ 1. Использовать инструмент «Текст по столбцам» (text to columns) . Выделяем все «проклятые» ячейки, идём на вкладку Данные — Работа с данными — Текст по столбцам, и на самом первом шаге нажимаем Готово.
Способ 2. Замена разделителя на аналогичный. Честно скажу, когда рассказываю про этот способ, почти все говорят «Да какого. Да почему!? Да что с этой программой не так?!». Да чего уж, признаюсь, что без улыбки на лице про него сам не могу рассказывать. Если в ячейках дробные числа, то преобразовать можно с помощью банальной замены запятой на. Барабанная дробь. ЗАПЯТУЮ! Выделяем данные, запускаем поиск и замену (CTRL+ H), ищем запятую, меняем на запятую. Этот способ также подходит для дат, которые сохранены как текст, только вместо запятой заменять нужно разделитель, который указан в ячейках (точка, слэш, дефис).
Способ 3. С помощью специальной вставки. Довольно интересный способ, так как знакомит нас со специальной вставкой — очень полезным инструментом. Смысл в том, что для преобразования текстовых чисел в нормальные числа с ними нужно произвести какое-то математическое действие. Но нужна такая операция, которая не меняет самого числа, а это, например, умножение на единицу (есть более экзотические, прибавить или вычесть ноль, но их рассматривать не будем). Алгоритм следующий:
В произвольную ячейку пишем единицу (1)
Копируем эту ячейку (Ctrl + C)
Выделяем диапазон с «кривыми» числами
По любой выделенной ячейке щёлкаем правой кнопкой мыши (либо Ctrl + Alt + V)
В контекстном меню выбираем Специальная вставка (Paste special)
В следующем окне выбираем «Умножить». Если в таблице есть какое-то оформление, то ещё желательно выбрать «Значения», чтобы это самое оформление не слетело
Способ 4. Функция ЗНАЧЕН (VALUE). Если планируете делать некий шаблон, в который будете копировать текстовые числа, а на выходе получать нормальные, то можно воспользоваться функцией, которая как раз и занимается преобразованием. Если работаете с датами, то нужна функция ДАТАЗНАЧ (DATEVALUE).
Способ 5. Бинарное отрицание. Хотите быть не как все? Хотите, чтобы коллеги подходили к вам с вопросом «А что это такое тут у тебя формуле?». Тогда этот способ для вас! Бинарное отрицание, если рассматривать его в контексте нашего вопроса, умножает число на -1, а потом ещё раз на -1. То есть мы производим математическую операцию, которая не меняет самого числа. Нужно просто перед ссылкой на ячейку поставить два знака минус (-). Вполне можно использовать при создании шаблона вместо функции.
Способ 6. Excel спешит на помощь. Вообще, если нажать на смарт-тэг (знак «дорожные работы») с ошибкой, то программа сама предложит преобразовать текст в число:
То есть можно выделить диапазон с такими вот ячейками, потом нажать на смарт-тэг, выбрать «Преобразовать в число», и всё сработает как надо. Но должен предупредить, что с большим количеством ячеек способ может работать довольно долго. Особенно если в книге много всего другого (формулы, листы, связи и т.д.).
Немного другая история. Иногда, когда выгружаем данные откуда-то в Excel, в числовых ячейках попадается пробел, как разделитель групп разрядов. И в большинстве случаев все способы, про которые я писал выше, справляются с преобразованием. Но порой что-то идёт не так. И ничего не срабатывает. Отчаявшись, ты пытаешься заменить пробел на пусто, но Excel, подлец, пишет, что пробелов в ячейках не найдено. Но как, вот же он, бездушная ты слепая программа:
К своему стыду (ладно, вру, мне не стыдно), я не погружался прям глубоко в то, почему так происходит. Для себя я придумал версию, что пробелы бывают разные. Так вот, чтобы разобраться с таким недоразумением, нужно скопировать пробел из самой ячейки, выделить ячейки, которые хотим преобразовать, потом открыть окно поиска и замены, вставить скопированный пробел в поле «Найти», а заменить на пусто.
Всесильный Power Query.
Долго думал, стоит ли включать сюда этот способ, и всё-таки решил включить. Часто в комментариях пишут, что а вот это можно с помощью PQ сделать, а вот то вообще на раз-два. И ведь это правда. PQ — это безумно крутая штука. Почему боюсь про него писать? По одной простой причине: более менее интерфейс PQ устаканился, начиная с 2019 версии. Для 2010 и 2013 вообще надо отдельно скачивать и устанавливать (да, это просто и занимает всего пару минут, но всё же). В 2016 версии сразу из коробки идёт, но выглядит чуть иначе. И вот я сейчас покажу, как это делается, а кто-то потом напишет, что у него этого нет, а это по-другому выглядит, и вообще автор — кАзёл. И всё же 🙂 Собрал все случаи, про которые писал. Дальше:
Желательно преобразовать таблицу в «умную» (не сделаете сами, всё равно потом Excel это сделает за вас)
Далее вкладка Данные — Получить и преобразовать данные — Из таблицы/диапазона
Если настройки PQ не меняли, то автоматически будет применён шаг «Изменённый тип», который всё и сделает
В PQ на вкладке Главная нажимаем Закрыть и загрузить
Получаем на новом листе «умную» таблицу с правильными форматами.
На этом, пожалуй, простыню свою завершу. Возможно, я рассказал не про все способы борьбы с текстовыми числами/датами. В комментариях делитесь своими, с удовольствием про них почитаю. Если спросите, зачем так много способов, то ответ будет так себе. Для разнообразия, конечно 🙂 Когда какой использовать — дело вкуса, привычки и исходных данных. В большинстве случаев все способы взаимозаменяемы.
Как всегда, всем огромное спасибо, кто потратил своё драгоценное время и внимание на прочтение данного материала. Надеюсь, было полезно и что-то из статьи поможет вам в работе.
Excel вечен
Диаграмма «спагетти» в Excel
Ребятушки, подскажите, можно ли сделать в эксель диаграмму спагетти, типа такой, которая на рисунке?
Нужно отобразить передвижение из одного кабинета в другой
5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1
Друзья, всем привет.
Сегодня хотел бы поговорить с вами о том, чего в Excel делать не нужно, чтобы в будущем избавить себя от головной боли и перманентных хлопков в нижней части тела. Планировал сначала про 10 вещей написать, но при подготовке материала понял, что в рамках одной статьи слишком уж много получится. Поэтому решил разбить на 2 части.
Важное примечание!
Сразу оговорюсь, что речь здесь скорее пойдёт не про то, как исправлять уже кривые данные, а про то, как правильно первоначально их оформлять. Так что статья больше ориентирована не на тех, кто работает с готовыми данными, а собирает все свои таблицы «с нуля».
1 — Объединение ячеек.
Один из самых страшных грехов при создании таблиц. Объединение ячеек влечёт за собой трудности с сортировкой, фильтрацией, созданием сводных, форматированием, протягиванием формул и т.д. Вот пример:
Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.
Как сделать лучше.
Если уж так хочется, чтобы название месяца было по центру, то поступаем следующим образом.
Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) — правая кнопка мыши — Формат ячеек (или CTRL + 1 с клавиатуры):
Далее вкладка Выравнивание — по горизонтали — по центру выделения:
К остальным заголовкам можно уже применить «Формат по образцу» (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.
2 — «Правильная» таблица
Этот пункт, безусловно, спорный, но я его всё же включил. И, в первую очередь, совет касается тех таблиц, но основе которых вы в дальнейшем планируете строить сводные таблицы. Сделали мы вот такую таблицу по месяцам за несколько лет:
Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.
Как сделать лучше.
Всё просто — не делайте трёхмерные таблицы. Плоская таблица — вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:
Теперь и сводную строить сплошное удовольствие, и сортировать/фильтровать данные куда проще. Про то, как трёхмерные таблицы переделывать в плоские не вручную, здесь речь не пойдёт.
3 — «Правильные» даты
Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить «29 марта 2023 г.». Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что «тупой иксель» не фильтрует, не считает:
Ну не умеет он так даты воспринимать. Все мы неидеальны.
Как сделать лучше.
Вводить даты, используя православные разделители, которые Excel понимает. Их три:
«-» — дефис/тире/минус (кому как удобнее)
А потом уже через формат ячеек установить то, что тебе нужно.
Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.
4 — Цветовое оформление таблицы.
Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:
А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я — молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.
Как сделать лучше.
Форматировать данные как таблицу.
Встаём в любую ячейку таблицы, Главная — Стили — Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку «Таблица с заголовками» — Ок. Всего 4 (. ) нажатия на кнопку мыши и готово. В этих наших интернетах это называется «умная» таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. «Умная» таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.
5 — Ручное закрашивание данных
Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:
Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но.
Как сделать лучше.
Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим «1». Выделяем весь диапазон, где хотим что-то закрашивать. Главная — Стили — Условное форматирование — Правила выделения ячеек — Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке «Заливка» выбираем нужные нам цвета. А вот самое интересное происходит на вкладке «Число». Выбираем «(все форматы)», в поле «Тип» прописываем следующий формат «;;;» (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет «1», но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с «умной» таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.
На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.