000818.ru

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

Как отключить выбор нескольких элементов в сводной таблице?

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

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

Показать итог в верхней части итоговой таблицы

  • Повторное использование чего угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте повторно их в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Отображать общий итог вверху в сводной таблице таблица

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50% своего времени и уменьшите тысячи щелчков мышью каждый день!

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

Шаг 1: Создание поддельного поля Total

1 . Затем добавьте новый столбец в диапазон исходных данных, переименуйте этот новый столбец в «GT», а затем введите туда «Total Total», как показано на скриншоте:

2 . Щелкнув правой кнопкой мыши на сводной таблице, выберите Обновить. Он был добавлен в список Select fields to add to reports list. а затем перетащите новое поле GT в поле Row Labels и поместите его поверх других меток строк, см. снимок экрана:

Совет . Обновив сводную таблицу для добавления нового столбца, вы также можете щелкните сводную таблицу и выберите вкладку Параметры или вкладку Анализ >Изменить источник данных >Изменить данные Источник , и чтобы выбрать новый диапазон, включая новый столбец, добавленный на шаге 1, теперь столбец GT добавлен в Список полей сводной таблицы .

Читайте так же:
Как найти самую раннюю или самую последнюю дату на основе критериев в Excel?

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

Шаг 2: Отображение общей суммы

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

5 . На следующем снимке экрана вы увидите общую сумму:

Шаг 3: Скрыть исходную строку итоговой суммы

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

6 . Щелкните любую ячейку сводной таблицы, а затем нажмите Дизайн >Общие итоги >Вкл. Только для строк , см. снимок экрана:

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

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

Как удалить сводную таблицу из Excel?

Давайте разберемся в этом на некоторых практических примерах.

Пример # 1 — Удаление сводной таблицы

Шаг первый:

Если появится сообщение «Мы не можем выполнить это изменение для выделенных ячеек, поскольку оно повлияет на сводную таблицу, как показано на следующем снимке экрана», выделите всю сводную таблицу, нажав Ctrl + A, а затем снова нажмите «Delete».

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

Шаг 2:

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

Выберите всю перекрестную таблицу с помощью вкладки Выбрать и нажмите Удалить.

Пример 1-1

или выберите опцию «Очистить все» на той же вкладке на главной странице >> Очистить. (Как показано на скриншоте ниже).

Удаление сводной таблицы, пример 1-2

Необработанные данные :

Пример 1-3

Данные сводной таблицы:

Удаление сводной таблицы, пример 1-4

Пример # 2 — Удаление сводной таблицы с сохранением сохраненных данных

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

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

Пример # 3 — Удаление всего рабочего листа

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

Выберите лист, на котором расположена ваша сводная таблица, затем щелкните правой кнопкой мыши имя листа, затем выберите «Удалить лист», чтобы удалить весь рабочий лист, как показано на рисунке ниже.

Читайте так же:
Как никогда не показывать информацию о заблокированном контенте в Excel?

Удаление сводной таблицы, пример 1-6

Что следует помнить здесь:

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

Вы можете скачать этот шаблон Как удалить сводную таблицу в Excel здесь — Удалить шаблон сводной таблицы в Excel

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

Перекрестная таблица — это базовый тип аналитики. Большинство людей считают, что его нельзя сделать с помощью инструментов SQL. Конечно, это не так.

В следующей таблице приведены данные о закупках нескольких различных товаров (Товар 1, 2, 3, 4) у нескольких поставщиков (А, В, С):

Одна из типичных задач — оценить размер закупок, сделанных поставщиками и товарами, т.е. построить сводную таблицу. Такая аналитика обычно доступна в MS Excel с помощью нескольких щелчков мыши:

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

1. Оператор CASE и аналоги

Используя оператор CASE, мы можем автоматически создать сводную таблицу. Чтобы рассчитать объем поставки для поставщика A, можно вычислить сумму объемов (case when T.supplier = ‘A’ then T.volume end). Добавьте группировку по полю продукта, чтобы получить объем поставок каждого продукта:

Для товаров, по которым не было доставок, вместо null будут отображаться нули:

Если мы продублируем код для всех поставщиков (которых у нас три — A, B, C), то получим необходимую нам сводную таблицу:

Можно объединить итоговые суммы по строкам (как обычную сумму, т.е. sum(t.volume) ):

Также легко добавить итоги по столбцам. Это делается с помощью команды ROLLUP для добавления строки суммирования. Большинство баз данных используют синтаксис rollup(t.product), хотя иногда встречается альтернатива t.product с rollup (например, SQL Server).

Результат можно сделать еще лучше, заменив NULL на пользовательскую глобальную подпись. Это можно сделать с помощью функции coalesce() : coalesce(t.product, ‘total_sum’), или аналога, специфичного для базы данных (например, nvl() в Oracle). Результат следующий:

Если ваша СУБД настолько стара, что не поддерживает сворачивание, вам придется полагаться на костыли. Например, так:

Можно (но вряд ли стоит) использовать одну из функций, специфичных для производителя, вместо стандартной функции CASE. Например, в PostgreSQL и SQLite доступен оператор FILTER:

Особенность FILTER в том, что он является частью стандарта (SQL:2003), но реализован только в PostgreSQL и SQLite.

В других СУБД есть ряд эквивалентов CASE, которые не охвачены стандартом: IF в MySQL, DECODE в Oracle, IIF в SQL Server 2012+ и т.д. В целом, их использование не дает никаких преимуществ, лишь усложняя поддержку кода в будущем.

Читайте так же:
Как найти первые / все числа в строке в Excel?

2. Использование PIVOT (SQL Server и Oracle)

Описанный выше подход не является красивым. По крайней мере, не следует дублировать код для каждого поставщика, а просто перечислить их. Это можно сделать, расширив таблицу PIVOT, которая доступна в SQL Server и Oracle. Хотя этот оператор не предусмотрен стандартом SQL, обе СУБД предлагают идентичный синтаксис.

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

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

Если вы используете оператор CUBE, вы можете значительно упростить этот запрос:

Чтобы сигнатура итогов была ‘total_sum’, а не NULL, нам придется немного подправить запрос:

К такому результату уже можно применять PIVOT:

Таблица вращается с помощью агрегатной функции sum(agg). Заголовки столбцов с полем поставщика используются в («A», «B», «C», «total_sum»), чтобы указать, какие поставщики должны быть отображены (total_sum — столбец для итогов строк).

3. Common table expression

P IVOT в принципе нет необходимости «вращать» таблицу. Используя стандартный синтаксис: комбинацию CTE (общих табличных выражений) и соединений, этот запрос можно легко переписать. Для этого мы будем использовать тот же запрос, что и для PIVOT:

Из результатов, полученных в cte, нам нужны только уникальные значения товаров:

… к которому, в свою очередь, можно привязать объем закупок каждого поставщика:

В этом случае мы используем левое соединение, так как некоторые товары могут отсутствовать у поставщика.

Окончательный запрос будет выглядеть таким образом:

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

4. Функция CROSSTAB (PostgreSQL)

В PostgreSQL доступна функция CROSSTAB , которая примерно эквивалентна PIVOT в SQL Server или Oracle. Для работы с ней необходимо расширение tablefunc :

C ROSSTAB принимает один аргумент в виде текстового sql-запроса. Аналогичен PIVOT, но с обязательным использованием сортировки:

В отличие от PIVOT, при «разворачивании» таблицы необходимо указывать не только имена столбцов, но и типы данных. В качестве примера, вы можете иметь: «product» varchar, «A» bigint, «B» bigint, «C» bigint, «total_sum» bigint.

Еще одна деталь заключается в том, что CROSSTAB заполняет строки слева направо и игнорирует значения NULL. Например, запрос следующего содержания:

… не вернет нам то, что мы хотим:

Как видите, все «переместилось» влево, где были значения NULL. Например, в первой строке для продукта1 общая строка находится в столбце для поставщика C, а поставщик B (для которого не было поставок) находится в столбце для поставщика C. Только продукт3 указан правильно, потому что у всех поставщиков были значения для этого продукта. Другими словами, если бы у нас не было значений NULL, запрос был бы корректным и дал бы нужный результат.

Читайте так же:
Как объединить только видимые ячейки в Excel?

Чтобы CROSSTAB не вел себя подобным образом, следует использовать вариант функции с двумя параметрами. Второй параметр должен содержать запрос со всеми колонками результата. В нашем случае это все названия поставщиков в таблице + «total_sum» для итогов:

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

5. Динамический SQL (на примере SQL Server)

Запрос с PIVOT или CROSSTAB уже более функционален, чем исходный запрос с CASE (или CTE), но названия поставщиков все равно приходится вводить вручную. Но что делать, если поставщиков много? Или если их список регулярно обновляется? Я хотел бы выбрать их автоматически как select distinct supplier из test_supply (или из словаря, если есть).

Чистого SQL здесь недостаточно. Для создания плана запроса СУБД должны заранее указать количество столбцов. В PIVOT, например, нельзя использовать подзапросы. Но это ограничение можно легко обойти с помощью динамического SQL! Для этого имена столбцов нужно преобразовать в строки с форматом «item_1», «item_2» и т.д., а затем использовать в запросе.

Например, в SQL Server мы можем использовать STUFF для получения такой строки

… и затем включить его в окончательное предложение:

Если CASE вообще используется, то вполне может быть применен Dynamic SQL. Например, так:

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

Многие СУБД имеют аналогичное решение. Однако мы уже слишком далеко ушли от чистого SQL. Любое использование динамического SQL предполагает углубление в специфику конкретной СУБД (и соответствующего процедурного расширения SQL).

В целом, как мы видели, инструменты SQL позволяют легко создать сводную таблицу. Более того, вы можете сделать это разными способами — просто выберите лучший для вашей СУБД.

Фильтр в сводной таблице Excel

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

Ниже приведена таблица в качестве примера:

Создайте сводную таблицу: «Вставка — «Crosstab». Поместите его на новый лист.

Мы добавили данные о поставщике, количестве и стоимости в сводный отчет.

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

Напомним, как выглядит диалоговое окно сводного отчета:

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

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

Например, среднее количество заказов на поставщика:

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

Мы установим фильтр в сводном отчете:

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

Теперь в отчете мы можем отфильтровать результаты по номеру склада. Наш интерес выбирается нажатием на стрелку в углу ячейки:

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

Вы также можете фильтровать отчет по значениям в первой колонке.

Вариант 4: Создание выпадающего списка из таблицы

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

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

Выберите подходящий для вас тип оформления.Создание таблицы для создания выпадающего списка в Excel

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

Вы автоматически окажетесь на вкладке «‎Конструктор таблицы», где можете изменить ее имя на любое удобное, начинающееся с буквы. Изменение названия таблицы для создания выпадающего списка в Excel

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

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

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

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

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