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

Как быстро ранжировать данные по нескольким диапазонам и листам в Excel?

Как быстро ранжировать данные по нескольким диапазонам и листам в Excel?

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

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

Сводный итоговый отчет для перекрестной вкладки может включать следующие поля в области списка полей, добавленной к перекрестной вкладке: «Строка», «Столбец» и «Значение». Кроме того, отчет может содержать до четырех полей фильтрации, названных Page1, Page2, Page3 и Page4.

Настройка исходных данных

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

Поля страницы при консолидации данных

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

Использование именованных диапазонов

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

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

Другие способы консолидации данных

Excel также предлагает несколько различных способов консолидации данных, позволяющих работать с ними в нескольких форматах. Можно создавать формулы с объемными ссылками или использовать команду Консолидация (находится на вкладке Данные в группе Данные).

Консолидация нескольких диапазонов

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

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

Чтобы объединить данные из всех диапазонов и создать консолидированный диапазон без полей страниц, выполните следующие действия.

Сделайте мастер сводных таблиц и диаграмм доступным на панели инструментов быстрого доступа. Для этого:

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

Выберите опцию Все команды в списке Выбрать команды из.

Выберите Мастер таблиц и перекрестных диаграмм в списке, нажмите Добавить, а затем нажмите OK.

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

На панели быстрого доступа щелкните значок мастера.

Выберите один из нескольких диапазонов консолидации на странице Шаг 1 мастера, затем нажмите Далее.

На странице Шаг 2a выберите Создать поля страницы, а затем нажмите Далее.

На странице Шаг 2b выполните следующее

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

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

Укажите количество полей на странице сводной таблицы в поле Первый, введя 0, а затем нажмите кнопку Далее.

Нажмите кнопку Готово на странице Шаг 3, чтобы выбрать между добавлением сводной таблицы в новый рабочий лист или существующий рабочий лист.

Консолидация данных с использованием одного поля страницы

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

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

Добавьте мастер сводных таблиц и диаграмм на панель инструментов быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите пункт Дополнительные команды.

Вы можете выбрать Все команды в списке Выбрать команды из.

Выберите из списка мастер Таблицы и перекрестные таблицы и нажмите Добавить, затем OK.

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

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

На странице Шаг 2а выберите опцию Создать поле страницы и нажмите кнопку Далее.

На странице Шаг 2b выполните следующие действия.

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

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

Нажмите кнопку Next (Далее).

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

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

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

Мастер сводных таблиц и диаграмм можно добавить на панель инструментов быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите пункт Дополнительные команды.

В списке Select orders (Выбрать заказы) выберите All orders (Все заказы).

В списке выберите Мастер перекрестных таблиц и диаграмм, нажмите Добавить, а затем нажмите OK.

Читайте так же:
Как автоматически пронумеровать столбец в Excel?

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

Когда вы перейдете к шагу 1, выберите параметр из нескольких диапазонов консолидации, а затем нажмите кнопку Next.

На странице Шаг 2a выберите опцию Создать поля страницы, а затем нажмите Далее.

На странице шаг 2b выполните следующее

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

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

В разделе «Первый» укажите количество полей в сводной таблице, нажав на нужное количество полей.

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

В поле Первое поле выберите каждый из диапазонов, а затем введите уникальное имя для каждого диапазона. Если у вас четыре диапазона, каждый из которых представляет один квартал, выберите первый диапазон, введите его имя «Kv1», выберите второй диапазон, введите его имя «Kv2» и повторите процедуру для диапазонов «Kv3» и «Kv4».

Укажите количество полей в поле First, укажите количество полей в поле First, а затем выполните следующие действия. Если вы выбираете два диапазона, введите одинаковые имена во втором поле, например, «Pg1» и «Pg2». Вы можете ввести «Pg1» в качестве имени первого диапазона, «Pg2» в качестве имени второго диапазона, «Pg3» в качестве имени третьего диапазона и «Pg4» в качестве имени четвертого диапазона.

Нажмите кнопку Next (Далее).

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

Читайте так же:
Как автоматически заполнять дату в ячейке, когда соседняя ячейка обновляется в Excel?

Как быстро ранжировать данные по нескольким диапазонам и листам в Excel?

x

x

x

Как быстро ранжировать данные по нескольким диапазонам и листам в Excel?

Если у вас есть три листа, которые содержат несколько оценок студентов, теперь вы хотите ранжировать оценки по этим трем листам, как показано на скриншоте ниже, как вы можете быстро справиться с этим, кроме сравнения одного за другим в Excel?
рейтинг документа по диапазонам листов 1

Ранжируйте значения в одном диапазоне на нескольких листах Kutools for Excelхорошая идея3

Ранжируйте значения по нескольким диапазонам и листам по формулам

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

1. Активируйте используемую книгу, щелкните Формулы > Менеджер имен, то в Менеджер имен диалоговое окно, нажмите Создать кнопка. Смотрите скриншот:
рейтинг документа по диапазонам листов 2

2. Затем в Новое имя диалоговом окне укажите имя диапазона и выберите Workbook из Объем раскрывающийся список, затем вставьте эту формулу = <"Class1!$E$2:$E$5","Class2!$B$2:$B$5","Class3!$B$8:$B$11">в Относится к текстовое окно. Смотрите скриншот:
рейтинг документа по диапазонам листов 3

В формуле Class1! $ E $ 2: $ E $ 5, Класс2! $ B $ 2: $ B $ 5 и Class3! $ B $ 8: $ B $ 11 — это диапазоны, по которым вы хотите ранжировать.

3. Нажмите OK > Закрыть чтобы закрыть диалоги. И теперь вы можете перейти в пустую ячейку рядом со значением, которое хотите ранжировать, введите эту формулу = ЕСЛИ (СУММПРОИЗВ (СЧЁТЕСЛИ (КОСВЕННАЯ (оценка); E2)) = 0, «НЕТ», СУММПРОИЗВ (СЧЁТЕСЛИ (КОСВЕННАЯ (оценка), «>» & E2)) + 1) , и перетащите дескриптор автозаполнения вниз, чтобы применить эту формулу. Смотрите скриншот:
рейтинг документа по диапазонам листов 4

В формуле E2 — это ячейка, которую вы хотите ранжировать, Score — это имя диапазона, указанное на шаге 2. Вы можете изменить ссылки и применить их к другим диапазонам.
рейтинг документа по диапазонам листов 5

Ранжируйте значения в одном диапазоне на нескольких листах Kutools for Excel

Если вы хотите ранжировать значения, которые находятся в одном диапазоне, например I2: I5 на трех листах, Лист Class1 , Лист Class2 и Лист Class3 , вы можете подать заявку Kutools for ExcelАвтора Динамически обращаться к рабочим листам утилита для извлечения ячеек в список, а затем их ранжирования.
рейтинг документа по диапазонам листов 6

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

После установки Kutools for Excel выполните следующие действия:( Скачайте Kutools for Excel бесплатно прямо сейчас!)

1. Включите пустой лист, например, Лист Мастер , наведите на Ячейка I2 и нажмите Кутулс > Еще (в группе Формула)> Динамически обращаться к рабочим листам. Смотрите скриншот:
рейтинг документа по диапазонам листов 7

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

3. Нажмите Диапазон заполнения, а затем закройте Заполнить рабочие листы Ссылки диалог. Теперь все ячейки I2 в Sheet Class1, Class2 и Class3 были заполнены, а затем перетащите дескриптор заполнения вниз, пока не появятся нулевые значения. Смотрите скриншот:
рейтинг документа по диапазонам листов 9

4. Теперь все оценки занесены в диапазон, теперь вы можете их ранжировать. Выберите пустую ячейку, введите эту формулу = RANK (I2, $ I $ 2: $ K $ 6), затем перетащите дескриптор автозаполнения вниз, затем перетащите вправо, чтобы заполнить эту формулу в нужные ячейки. Смотрите скриншот:
рейтинг документа по диапазонам листов 10

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

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