Как разделить строки в сводной таблице excel - Word и Excel

Сводная: как перенести второе значение строк в столбцы

Elvira66

Дата: Суббота, 18.03.2017, 15:35 |
Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

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

Ответить

Elvira66

Дата: Суббота, 18.03.2017, 15:37 |
Сообщение № 2

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Не понятно прикрепился файл или нет, на всякий случай прикреплю еще раз

К сообщению приложен файл:

6193216.xlsx
(14.1 Kb)

Ответить

Manyasha

Дата: Суббота, 18.03.2017, 16:17 |
Сообщение № 3

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация:
898
±

Замечаний:
0% ±

Excel 2010, 2016

Elvira66, щелкните на любую ячейку сводной — Вкладка Конструктор — Макет отчета-показать в табличной форме.
В группе Названия строк — щелкните по 1-значение строки — Параметры поля — разметка и печать — Повторять подписи элементов.

ЯД: 410013299366744 WM: R193491431804

Ответить

Elvira66

Дата: Суббота, 18.03.2017, 16:58 |
Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Спасибо! Вам огромное! Это то что нужно!

Ответить

Источник

Столбцы и строки сводной таблицы

В прошлой статье рассказывалось про то как создать простую сводную таблицу. Сейчас мы ее немного усложним и заодно разберемся в чем отличие областей СТРОК, КОЛОНН и ЗНАЧЕНИЙ. Вы поймете когда и в какую область необходимо переносить поле сводной таблицы.

Задача

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

Решение

Создадим пустую сводную таблицу по алгоритму, который описан в предыдущей статье.

Теперь давайте конструировать отчет. Давайте перенесем поле Филиал в область СТРОКИ, тогда мы получим перечень всех филиалов (без дубликатов) из исходной таблицы.

Теперь давайте расположим все наши группы активов по столбцам, для этого перенесем поле Группа в область КОЛОННЫ. Получим следующую картину:

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

Так получилось намного нагляднее. Осталось добавить сумму по рыночной стоимости в наш отчет. Для этого перенесем поле Рыночная стоимость в область ЗНАЧЕНИЯ.

Поговорим про группировку данных в сводной таблице в Excel, которая позволяет структурировать вид таблицы и существенно упрощает работу с данными.

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

Не так давно мы с вами учились и разбирались в том как создавать сводные таблицы, если еще не читали, то всячески рекомендую к прочтению.

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

Мы разберем 3 основных варианта группировки полей сводной таблицы в зависимости от типа данных:

Дата/время;
Числа;
Произвольная группировка.

При этом абсолютно не важно какие именно поля сводной таблицы мы группируем, алгоритм и для строк и для столбцов одинаковый, поэтому для удобства мы рассмотрим примеры для строк, для столбцов же действия будут идентичными.

Как сгруппировать данные в сводной таблице в Excel?

Рассмотрим простую таблицу, где в качестве данных как раз присутствуют интересные нам срезы данных — даты, числа и наименования:

А теперь приступим к конкретным примерам.

Группировка по датам в сводной таблице

Построим по исходной таблице сводную (подробно про это рассказывал в отдельной статье), в строки таблицы добавим даты, в значения поместим сумму:

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

Встаем в любой ячейку с датой, нажимаем правой кнопкой мыши и выбираем в контекстном меню команду Группировать:

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

Мы можем выбрать любую метрику по времени от секунд до годов (в том числе и сразу показателей несколько), выберем подходящие (к примеру, дни и месяца слева на картинке, кварты и месяца — справа) и группировка дат в сводной таблице будет сделана от более крупного к более мелкому:

Важный момент. Начиная с версии Excel 2016 программа автоматически умеет группировать данные по дате. Тем не менее если группировку захочется поменять, то в этом случае как раз подойдет ручной способ группировки.

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

Группировка по числам в сводной таблице

Немного видоизменим нашу сводную таблицу и в строки вместо дат добавим числовой код товара:

Алгоритм действий точно такой же как и в варианте с датами, щелкаем по любой ячейке с числами правой кнопкой мыши и выбираем команду Группировать:

Мы также можем настроить начальную и конечную точку для группировки чисел, а также задать шаг по которому числа будут делиться на группы:

С числами тоже все оказалось не так сложно, перейдем к последнему варианту.

Произвольная группировка в сводной таблице

Иногда хочется сделать группировку не исходя из каких-то четких правил формирования с заданием шага, а абсолютно произвольной. В этом смысле Excel нас никак не ограничивает и мы можем сгруппировать любые данные (не только даты и числа) по нашему усмотрению. Так как даты и числа мы с вами уже научились группировать, то предлагаю произвольную группировку сделать для текста.

Опять немного видоизменим нашу сводную таблицу и в этот раз в поля добавим наименование в текстовом виде:

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

Например, давайте объединим наименование по компьютерной тематике, в одну группу поместим компьютеры, мониторы, ноутбуки и процессоры. Выделяем мышкой подходящие ячейки, также щелкаем по ним правой кнопкой мыши и выбираем команду Группировать:

Новой группе мы можем дать имя, для этого достаточно непосредственно в ячейке прописать новое название. Если нужно сделать группировку для остальных элементов, то принцип действия точно такой же — выделяем подходящие ячейки и группируем:

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

Как разгруппировать данные в сводной таблице в Excel?

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

Спасибо за внимание!
Если у вас есть вопросы по теме — заходите и пишите комментарии.

Источник

Добрый день!
Есть таблица с данными по складу. Мне надо её обработать и отправить в другой отдел.
Времени на это уходит очень много. Подскажите, пожалуйста, каким образом можно реализовать более автоматизированный вариант.
У товара есть запрошенное кол-во и скомплектованное.
И оно выводится в одну строку. А в некоторых вариантах, необходимо, чтобы было 2 строки. Образец приложил. Там более понятно, чем я опишу.
Заранее спасибо.

Прикрепленные файлы

Отчёт1.xlsx (194.62 КБ)

evgeniygeo

Пользователь

Сообщений: 2276
Регистрация: 19.06.2018

07.07.2022 14:24:25

Евгений Валерьевич,

Код

Sub aa()
lLastRow = Cells(Rows.Count, 9).End(xlUp).Row
For n = 5 To lLastRow

lLastRow2 = Cells(Rows.Count, 2).End(xlUp).Row
a = "B" & n & ":G" & n
b = "B" & lLastRow2 + 1
ñ = "I" & n
d = "H" & lLastRow2 + 1
Range(a).Copy Range(b)
Range(ñ).Copy Range(d)

If Cells(n, 8) <> "" Then
If (Cells(n, 7) - Cells(n, 8)) / Cells(n, 7) >= 0.05 Then
lLastRow2 = Cells(Rows.Count, 2).End(xlUp).Row
a = "B" & n & ":F" & n
b = "B" & lLastRow2 + 1
ñ = "H" & n
d = "G" & lLastRow2 + 1
Range(a).Copy Range(b)
Range(ñ).Copy Range(d)
Cells(lLastRow2 + 1, 8) = "Ìîñêâà"
End If
End If
Next
End Sub

Прикрепленные файлы

Отчёт1.xlsm (197.6 КБ)

Изменено: evgeniygeo — 07.07.2022 14:37:22

surkenny

Пользователь

Сообщений: 2367
Регистрация: 13.06.2014

07.07.2022 14:27:17

Евгений Валерьевич, я бы сделал так с доп таблицей статусов (вашу меру статуса можно убрать):

Код

Кол-во:=
VAR stat =
    IF ( HASONEVALUE ( 'Статус'[Статус] ); VALUES ( 'Статус'[Статус] ) )
VAR requested =
    SUM ( 'Таблица1'[Запрошено] )
VAR completed =
    SUM ( 'Таблица1'[Скомплектовано] )
VAR result =
    SWITCH (
        stat;
        "Резерв"; completed;
        "Москва";
            IF ( 1 - DIVIDE ( completed; requested ) >= 0,05; requested - completed )
    )
RETURN
    result

Прикрепленные файлы

Отчёт1.xlsx (236.16 КБ)

	surkenny, Ваш вариант гораздо изящнее))))

	surkenny и evgeniygeo Огромное спасибо. Теперь осталось изучить их, чтобы в будущем была возможность использовать с другими вариантами. Я правильно понимаю, что первый вариант VBA, а второй DAX?

surkenny

Пользователь

Сообщений: 2367
Регистрация: 13.06.2014

07.07.2022 16:19:29

Цитата
Евгений Валерьевич написал: Я правильно понимаю, что первый вариант VBA, а второй DAX?

Мое решение на DAX.

Источник

У меня возникла та же проблема, и я нашел нужный мне ответ. Как и OP, я хочу вычислить среднее значение — SUM(поле 1), деленное на COUNT(поле 2), — но проблема в том, что в одной формуле есть две функции (SUM, деленная на COUNT). Как мы уже видели, использование нескольких функций в одном расчете приводит к непредвиденным результатам.

Ключ, который работал для меня, состоял в том, чтобы создать новое поле (поле 3) в исходных данных с формулой, которая присваивает 1 элементам, которые я хочу считать, и 0 элементам, которые я не хочу считать, поэтому count этого столбца просто сумма. Таким образом, я конвертирую COUNT(поле 2) в знаменателе в SUM (поле 3). Итак, теперь мне нужно получить результат SUM, деленный на SUM, одну и ту же функцию сверху и снизу, которую может обрабатывать Excel. И, к счастью для меня в этой ситуации, «бесстрастная манера» вычислений в Excel — именно то, чего я хочу.

Как сказал Фернандо, рассчитанное поле должно относиться только к самому полю; он не должен использовать SUM или COUNT или что-либо еще. Требуемая функция будет применена, когда вы добавите поле в сводную таблицу и выберете нужную функцию.

Я установил для своей вычисляемой функции значение [поле 1 / поле 3] с оператором IF, чтобы избежать деления на 0, и я использовал функцию SUM, когда помещал вычисляемое поле в сводную таблицу. Конечным результатом является SUM (поле 1) / SUM(поле 3), что равно SUM (поле 1) / COUNT(поле 2)

Резюме:

Переформулируйте свою формулу так, чтобы одна и та же функция использовалась во всех полях; например, найти способ пересчитать среднее (SUM/COUNT) значение SUM/SUM или COUNT/COUNT и т. д.
Добавьте поля к необработанным данным, которые помогут в пересмотренной формуле; например, если ваша пересчитанная формула использует СУММУ вместо СЧЕТА, создайте новое поле в необработанных данных, которое присваивает 1 и 0 так, чтобы сумма этого нового поля была равна количеству другого поля.
Создайте вычисляемое поле сводной таблицы, в котором используются поля, соответствующие пересчитанной формуле, включая новое поле, которое вы только что создали; не используйте SUM или COUNT на этом этапе.
Добавьте вычисляемое поле в область данных сводной таблицы и выберите нужную функцию; эта функция будет применяться к каждому полю, указанному в формуле вычисляемого поля.

Источник

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

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

В работе со сводными таблицами и сводными диаграммами нам, как и прежде, помогут Мастер сводных таблиц и диаграмм и панель инструментов Сводные таблицы. Вывести эту панель инструментов на экран можно, например, выбрав команду Вид Панели инструментов Сводные таблицы .

Чтобы запустить, Мастер сводных таблиц и диаграмм, выберите команду Данные a Сводная таблица или щелкните на кнопке Сводные таблицы панели инструментов Сводные таблицы .

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

В примере на листе Таблица сформирован отчет по странам и датам, показывающий изменение показателя количества заказов во времени. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

Описанные ранее примеры демонстрируют анализ данных по одному критерию. Электронные таблицы позволяют наглядно представить данные в двух измерениях: по столбцам и по строкам. Сводные таблицы также имеют эти области отображения данных.

Как в Excel разбить ячейку на две или несколько частей

Выведите на экран панель инструментов Сводные таблицы.
Установите указатель мыши на любую ячейку сводной таблицы.
Щелкните на кнопке Обновить данные панели инструментов Сводные таблицы.
Чтобы установить обновление сводной таблицы при каждом открытии книги, откройте диалоговое окно Параметры сводной таблицы (см. рис.9.7). Для этого щелкните правой кнопкой мыши на любой ячейке уже созданной сводной таблицы и в появившемся контекстном меню выберите команду Параметры таблицы. Затем в группе Источник установите флажок обновить при открытии.

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

Разные значения против уникальных значений

Ниже приведен пример со списком имен, в столбцах отдельно выделены уникальные и разные имена.

Разница между уникальным и разными значениями

Уникальные значения / имена — это те, которые встречаются только один раз. Это означает, что все имена, которые повторяются и имеют дубликаты, не являются уникальными. Уникальные имена перечислены в столбце D вышеупомянутого набора данных.

Разными значениями / именами являются те, которые встречаются хотя бы один раз в наборе данных. Поэтому, если имя появляется три раза, оно все равно считается разным значением. Такой список можно получить путем удаления повторяющихся значений / имен и сохранения всех разных значений. Разные имена перечислены в столбце C приведенного выше набора данных.

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Хотя этот метод довольно прост, я должен выделить несколько недостатков, связанных с изменением исходных данных в сводной таблице. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

Если вы используете Excel 2013 или более поздние версии, в сводной таблице есть встроенная функция, которая быстро подсчитывает количество. А если вы используете Excel 2010 или ранние версии, вам придется изменить исходные данные, добавив вспомогательный столбец.

Подсчет разных значений в сводной таблице Excel (простое пошаговое руководство)

Добавление вспомогательного столбца в исходный набор данных для подсчета разных значений (работает во всех версиях).
Добавление данных в модель данных и использование параметра «Число различных элементов» (доступно в Excel 2013 и последующих версиях).

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

Как удалить строки в сводной таблице?

Щелкните на диаграмме объект ,соответствующий полю или элементу, который вы хотите переименовать (например, от столбец, строка или столбец).
Перейдите в окно Средства работы со > анализа и в группе Активное поле щелкните текстовое поле Активное поле. .
Введите новое имя.
Нажмите ввод.

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

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

Пользователям полезно знать, как разделить слова в Экселе по ячейкам. Если диапазон для разбиения текста по секциям не будет пустым, информация перезаписывается. Чтобы сделать свободное пространство, пользователь вставляет новые столбцы (строки) или вырезает и вставляет информацию на другом листе.

Как развернуть и свернуть все поля сводной таблицы читать онлайн

Откройте в Таблицах файл, содержащий исходные данные.
Нажмите «Анализ данных» в правом нижнем углу.
Найдите раздел «Сводная таблица». .
Наведите указатель мыши на нужный вариант и нажмите Вставить сводную таблицу .

Для лучшего понимания отличий скачайте и откройте файлы-примеров nwdata_pivot1.xlsx и nwdata_pivot2.xlsx (в арихиве nwdata_pivot.zip). В первом файле представлен отчет в старом формате, во втором – в новом, исходные данные одинаковые.

Назначить макросы фигурам или кнопкам

Мы можем назначить эти макросы для фигур или кнопок на листе.

Кнопки можно непрерывно нажимать, чтобы проходить вверх или вниз по полям. Это позволяет пользователям ваших отчетов быстро расширять или сворачивать целые поля.

Формы можно вставлять в сводную диаграмму, чтобы имитировать функциональность кнопок развертывания / свертывания в Excel 2016.

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Находить сумму сводные таблицы могут мгновенно, чтобы получить количество разных значений, вам нужно будет сделать еще несколько шагов. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

Microsoft Excel 2003

Вы можете суммировать сводную таблицу, поместив поле в область AL ЗНАЧЕНИЯ на панели задач «Поля сводной таблицы». По умолчанию Excel принимает суммирование как сумму значений поля в области VALUES. Однако у вас есть другие типы вычислений, такие как, Подсчет, Среднее, Макс, Мин и т. Д.

Источник

Столбцы и строки сводной таблицы

Задача

Решение

Как сгруппировать данные в сводной таблице в Excel?

Группировка по датам в сводной таблице

Группировка по числам в сводной таблице

Произвольная группировка в сводной таблице

Как разгруппировать данные в сводной таблице в Excel?

Как в Excel разбить ячейку на две или несколько частей

Разные значения против уникальных значений

Подсчет разных значений в сводной таблице Excel (простое пошаговое руководство)

Как удалить строки в сводной таблице?

Как развернуть и свернуть все поля сводной таблицы читать онлайн

Назначить макросы фигурам или кнопкам

Microsoft Excel 2003

А вот еще интересные статьи: