Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Сводные таблицы в Excel

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

Создание сводной таблицы

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

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

В открывшемся окне выберите представляемый источник данных:

  1. Диапазон (может быть в другой бухгалтерской книге);
  2. Таблица данных (укажите название);
  3. Данные из внешнего источника, полученные SQL-запросом из базы данных и т.д.

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

Окно создания

Управление списком полей таблицы

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

Пустая сводная таблица

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

  • Имена строк — отображает значения полей в виде имен строк. По сути, это поле ничего не меняет, если вы сопоставите со всеми доступными полями, вы получите исходную таблицу;
  • Имена столбцов — значения полей отображаются как имена столбцов. Действие аналогично обычному отображению;
  • Значения — вычисления производятся со значениями столбцов, размещенных в этой области. Здесь вы можете отобразить поле более одного раза;
  • Фильтр отчета — здесь вы размещаете поля, значения которых используются для фильтрации строк исходных данных, которые должна игнорировать поворотная таблица.

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

МесяцДатаКол-воКурсИзменение
Январь10.01.2013130,42150,0488
Январь11.01.2013130,3650-0,0565
Январь12.01.2013130,2537-0,1113
.....
Сентябрь28.09.2013132,34510,1715

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

Читайте так же:
Как создать индикатор выполнения в Excel?

Перетащите поле «Месяц» в область «Имена строк». Для области «Значения» назначим «Ставку», а затем установим параметры так, чтобы вычислялось среднее арифметическое. Это можно сделать, щелкнув по нужному элементу в области, а затем выбрав в появившемся меню пункт «Параметры поля значений». В появившемся окне находится вкладка «Операция». Из списка необходимо выбрать «Среднее». Готово.

Параметры поля значений

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

Вычисляемые поля сводной таблицы

Если предусмотренных в Excel операций и вычислений недостаточно, можно создать собственные вычисляемые поля в сводной таблице. Для этого нужно щелкнуть ячейку в области таблицы, затем перейти на вкладку Параметры («Анализ» в Excel 2013). Далее в разделе «Сервис» нажмите на значок «Формулы» и из выпадающего меню (в версии 2010 и выше путь другой: Раздел «Вычисления» -> Поля, элементы и наборы, выпадающий список) выберите пункт Вычисляемое поле. Должно появиться окно:

Вставка вычисляемого поля

Создайте понятное имя поля и используйте любые функции по своему усмотрению (помните, что вычисляемые поля не работают с текстами). В качестве примера умножьте ставку на 1000 и вычтите 13 процентов (=Курс*1000*0,87). Определите поле как «Зарплата», добавьте его в область значений и примените максимум как операцию. Посмотрите на новый вид отчета:

Неверные вычисления

Вы можете заметить, что результат нового поля возвращает «странный» результат. Это происходит потому, что колонка «ставка» добавляется до того, как результат используется в расчете. Если вы добавите дату в поле имени строки, вычисления дадут нужный результат:

Правильный результат вычисления

Параметры сводной таблицы в Excel

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

Макет сжатой формы

Из примера видно, что сводная таблица представляет древовидную структуру, если используется более 1 поля. Корнем являются значения столбца, который в списке области «Названия строк» идет первым. Все последующие поля вкладываются в него и в друг друга, согласно своей очередности в списке, изменить которую можно простым перетаскиванием мыши. Каждую отдельную ветвь подобного дерева можно сворачивать и раскрывать. Данное свойство так же применимо к области названий столбцов.
По умолчанию эксель задает сводным таблицам макет в сжатом виде. Его можно изменить через параметры (клик правой кнопкой мыши по области таблицы -> параметры сводной таблицы -> Вывод -> Классический макет) либо через конструктор:

Макет табличной формы

Применение макета табличной формы позволяет расположить каждое поле в отдельном столбце и дополнительно вывести по нему промежуточные итоги.
Если подводить дополнительно итог не требуется, то его нужно удалить, чтобы облегчить чтение таблицы. Достаточно правого клика мыши по нему и в списке снять галочку с соответствующего пункта. Для избавления от всех итогов кроме основных, на вкладке конструктор в разделе макет выберите «Промежуточные итоги» -> «Не показывать промежуточные суммы».

Читайте так же:
Как создать новую книгу с определенным количеством листов?

Удаление промежуточных итогов

Поскольку перекрестная таблица представляет собой древовидную структуру, имя строки отображается только один раз. В Microsoft Excel, начиная с версии 2010, можно применять повторение меток элементов в макете.

Повторение подписей сводной таблицы

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

Макет табличной формы с повторением подписей

В дополнение к свойствам, описанным выше, можно также задать параметры с помощью таблицы:

  1. Название сводной таблицы;
  2. Объединить и выровнять подзаголовки;
  3. Вывести значения пустых ячеек;
  4. Автоматически изменить ширину столбцов;
  5. Показать итоговые значения строк и столбцов;
  6. Сортировка;
  7. Печать;
  8. Обновить и т. д.

Теперь вы знаете, как пользоваться электронными таблицами Excel. Полученные здесь знания позволят вам самостоятельно экспериментировать и совершенствовать свои навыки.

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

Начиная с Excel 2007, появилась возможность использовать так называемые электронные таблицы Excel.

Обычную таблицу можно превратить в умную таблицу, выделив любую ячейку, развернув список Формат как таблица и выбрав нужный стиль (см. скриншот выше) или выбрав в меню Вставка -> Таблица.

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

Вначале о преимуществах:

  • преобразование вашей таблицы в "умную" придаст ей презентабельный «полосатый» внешний вид;
  • "умная" таблица автоматически увеличивается размерах при добавлении данных правее последнего столбца или ниже последней строки. Причем при добавлении строк формулы в ячейках сохраняются. Еще один способ добавить в таблицу строки и столбцы — просто «потащить» ее за правый нижний угол;
  • можно мгновенно создать строку итогов с результатами на ваш выбор (сумма, количество, среднее, минимум, максимум и т.д.);
  • Excel автоматически присваивает имена столбцам «умной» таблицы, которые можно использовать в формулах.
  • с умной таблицей очень удобно работать в надстройке Power Query . На мой взгляд это главное достоинство умных таблиц.

» Умные» столы имеют недостатки, которые являются лишь продолжением их достоинств:

  • При большом количестве строк «умная» таблица начинает изрядно «тормозить» по сравнению с обычной. Ее «полосатый» внешний вид основан на том же принципе, что и условное форматирование, поэтому эта "фишка" потребляет много ресурсов компьютера. В больших таблицах рекомендуется эту красоту и возвращать «умной» таблице одноцветный внешний вид.

Кстати, если вам так хочется "полосатости", это легко можно сделать и для обычной таблицы. Достаточно задать для вашей таблицы правило условного форматирование вот с такой формулой: =ЕЧЁТН(СТРОКА()

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

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

  • Первая строка умной таблицы должна быть заголовком столбца. Аналогичные требования предъявляются к исходным данным при создании таблиц pivot. Это избавит вас от создания сложных заголовков, а также от использования одного из трюков ExcelSoft: строки суперфильтра.
  • Если обращаться к столбцам таблицы, используя имена столбцов, которые автоматически назначила «умная таблица», формулы могут стать «трехуровневыми».

Например, можно вручную присвоить имя диапазона, такое как CounterName, столбцу, содержащему имя контрагента. Оно компактно и информативно, поэтому его можно использовать в формулах.

Второй вариант — использовать в формуле название интеллектуальной таблицы, например, Таблица 1[Название контрагента] . Но если у вас сложные формулы, они будут во много раз длиннее и просто нечитабельны.

Конечно, можно принудительно изменить имя и адрес, но тогда где экономия времени при использовании смарт-досок?

Вот пример не самой длинной формулы в ячейке на листе "Контрагенты" в программе ES: “Финансовое планирование и учет” , которая рассчитывает сальдо по контрагенту:

=ЕСЛИ(ИЛИ(НаТ1;НаТ4);ЕСЛИ($B19<>"";-СУММЕСЛИМН(КаПрихРасхБДР;КаДата;"<="&НаКД;КаНомКонтр;$U19;КаПодчинСправ;"КонтрИмя";КаВидУч;"Б")-СУММЕСЛИМН(РсПрихРасхБДР;РсДата;"<="&НаКД;РсНомКонтр;$U19;РсПодчинСправ;"КонтрИмя";РсВидУч;"Б") +СУММЕСЛИМН(ОпПрихРасхБДР;ОпДата;"<="&НаКД;ОпНомКонтр;$U19;ОпПодчСправКонтр;"КонтрИмя";ОпВидУч;"Б") +СУММЕСЛИМН(ОпПрихРасхБДР;ОпДата;"<="&НаКД;ОпНомКонтр;$U19;ОпПодчСправКонтр;"КонтрИмя";ОпВидУч;"НоБ");0);0)

Если бы вместо имен диапазонов использовались имена столбцов, автоматически присваиваемые «интеллектуальной таблицей», формула была бы в 2-3 раза длиннее и визуально гораздо сложнее для понимания.

  • В защищенных электронных таблицах функция укладки строк в угол или авторасширения при добавлении данных не работает. Также невозможно вставить строки в «умную» таблицу на защищенном рабочем листе. И если для обычных таблиц существует «костыль» — использование методов Insert , Delete в макросах VBA, то смарт-таблицы Excel не поддерживают эти методы и пока не ясно, как решить проблему вставки строк с помощью макросов.
  • В Excel есть очень полезный инструмент: «представления». Макеты позволяют сохранить внешний вид таблицы на данном листе (области фиксации, скрытые и видимые строки и столбцы, условия фильтрации и т.д.), что особенно полезно при подготовке отчетов. ), что особенно полезно при подготовке отчетов. Подробнее о представительствах вы можете прочитать здесь. К сожалению, если в рабочей книге есть хотя бы одна «умная» таблица, использование представлений становится невозможным.
  • Еще один существенный недостаток «умных» таблиц в том, что при наличии в рабочей книге Excel хотя бы одной «умной» таблицы совместный доступ к файлу сделать невозможно. Excel предложит преобразовать все "умные" таблицы в обычные и удалить карты XML.

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

Конечно, обмен файлами является устаревшим инструментом и имеет множество ограничений. Но многие продолжают им пользоваться, потому что его альтернатива, совместное редактирование, по-прежнему доступна только при подписке на Office 365, которую могут позволить себе не все компании.

В качестве заключения следует отметить, что электронные таблицы — это будущее Excel. Скоро ими будут пользоваться все. Что касается существующих недостатков, будем надеяться, что Microsoft исправит их, добавив в коробочные версии MS Office.

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

Чтобы удалить структуру таблицы Excel, выделите ее и нажмите на стрелку рядом с кнопкой «Разгруппировать», затем выберите функцию «Удалить структуру». Все группировки будут удалены. Чтобы удалить конкретную группировку, например, раздел по строкам, выделите эти строки и нажмите кнопку «Разгруппировать».

Читайте так же:
Как скопировать исходное форматирование ячейки поиска при использовании Vlookup в Excel?

Как отменить группировку в Excel?

Совет: Чтобы отменить группировку, выберите диапазон, содержащий сгруппированные столбцы, и нажмите Shift+Alt+Стрелка влево. Инструмент группировки добавит на лист Excel специальные символы структуры, чтобы показать, какие столбцы входят в группу.

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

  1. Щелкните значение в сводной таблице правой кнопкой мыши и выберите команду Группировать.
  2. В окне Группировка установите флажки Начиная с и Заканчивая и при необходимости измените значения.
  3. В разделе С шагом выберите период времени. …
  4. Нажмите кнопку ОК.

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

Если вы хотите сгруппировать данные о продажах по месяцам, то это можно сделать следующим образом:

  1. Щелкните правой кнопкой мыши в левом столбце сводной таблицы (столбец даты) и выберите Группировать. Появится диалоговое окно Группировать по дате. Выберите Месяц и нажмите OK.

Как разгруппировать строки в Excel?

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

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

Удаление промежуточных итогов в программе Excel

  1. Откройте вкладку Данные, а затем щелкните Промежуточный итог.
  2. Появится диалоговое окно «Промежуточные итоги». Нажмите кнопку Очистить все .
  3. Все данные удаляются из группы, а итоговые данные стираются.

Как скрыть ненужные столбцы в Excel?

  1. Выберите один или несколько столбцов и нажмите клавишу CTRL, чтобы выбрать другие, не смежные столбцы.
  2. Щелкните правой кнопкой мыши выбранные столбцы и выберите Скрыть.

Как сделать группировку в Excel сверху?

  1. Выберите нужные строки;
  2. Откройте вкладку «Данные» в верхнем меню;
  3. Найдите команду «Группа» в разделе «Структура»;
Читайте так же:
Как создать несколько имен из выделения в Excel?

Как сгруппировать график в Excel?

Шаг за шагом, как объединить два графа

  1. Создайте свою таблицу с данными, которые вы хотите объединить. …
  2. Нажмите на стол. …
  3. в окне «Вставка диаграммы» выберите «Столбцы», а затем шаблон «Сгруппированные столбцы».
  4. На вставленном графике вы должны нажать на полосу, соответствующую прибыли (третья переменная).

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

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

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

Расширение и свертывание уровней в сводной таблице

  1. Нажмите Развернуть, чтобы отобразить подробную информацию о текущем элементе.
  2. Чтобы скрыть сведения о текущем элементе, нажмите Свернуть.
  3. Чтобы скрыть сведения обо всех элементах в поле, нажмите Свернуть все поле.

Как добавить Вычисляемую строку в сводную таблицу?

Добавление рассчитываемого поля

  1. Щелкните сводную таблицу. …
  2. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
  3. В поле Имя введите имя для поля.
  4. В поле Формула введите формулу для поля. …
  5. Нажмите кнопку Добавить.

Как настроить фильтр в Экселе по месяцам?

Сортировка по месяцам в Excel и списки с порядком сортировки

  1. Перейдите к любой ячейке в исходном диапазоне A1:C10 и выберите инструмент «ДАННЫЕ» — «Сортировка».
  2. В появившемся диалоговом окне в первом разделе «Колонка» выберите «Сортировать по:», в первом выпадающем списке выберите значение «Месяц», во втором выпадающем списке выберите «Значение» без изменений.

Как сгруппировать таблицу?

Нажмите кнопку Группировать на вкладке Формат фигуры или Формат рисунка, а затем выберите Разгруппировать. Нажмите Упорядочить, чтобы отобразить его. После расформирования Office отдельные объекты останутся выделенными и в своем текущем расположении.

Читайте так же:
Как скрыть все листы кроме указанного или активного в Excel?

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

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

мою статью «Даты группирования в сводной таблице».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector