Комплексная практическая работа на тему создания таблиц в excel вариант 1

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

Как сгруппировать строки вручную

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

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

1. Создание внешних групп (уровень 1)

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

В приведенном ниже наборе данных, чтобы сгруппировать все данные для строки 9 (Всего по востоку), мы выбираем строки со 2 по 8.

На вкладке Данные в группе Структура нажмите кнопку "Группа", выберите "Строки" и нажмите ОК.

Это добавит полосу в левой части рабочего листа, которая охватывает выбранные строки:

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

В этом примере нам нужна еще одна внешняя группа для региона Север. Для этого мы выбираем строки с 10 по 16 и нажимаем вкладку Данные > кнопку Группировать > Строки.

Этот набор строк теперь также сгруппирован:

Совет. Чтобы создать новую группу быстрее, нажмите сочетание клавиш Shift + Alt + Стрелка вправо, а не кнопку Группа на ленте.

2. Создание вложенных групп (уровень 2)

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

Например, чтобы создать группу Яблоки в регионе Восток, выберите строки 2 и 3 и нажмите Группа. Чтобы создать группу Апельсины, выберите строки с 5 по 7 и снова нажмите кнопку Группировать.

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

3. При необходимости добавьте дополнительные уровни группировки

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

В качестве примера давайте вставим строку Общая сумма в нашу таблицу, а затем добавим самый внешний уровень структуры. Для этого выберите все строки, кроме строки Общий итог (строки со 2 по 17), и нажмите вкладку Данные > кнопку Группировать. > Строки.

Как показано на снимке экрана ниже, наши данные теперь сгруппированы по 4 уровням:

• Уровень 1: общая сумма
• Уровень 2. Итоги по региону.
• Уровень 3. Промежуточные итоги по элементам
• Уровень 4: строки сведений

Теперь, когда у нас есть схема строк, давайте посмотрим, как это упрощает просмотр наших данных.

Как свернуть строки в Excel

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

Свернуть строки внутри группы

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

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

Еще один способ свернуть строки в Excel — выбрать любую ячейку в группе и нажать кнопку "Скрыть сведения" на вкладке Данные в группе Структура:

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

Свернуть или развернуть весь план до определенного уровня

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

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

В нашем примере набора данных у нас есть 4 уровня структуры, которые работают следующим образом:

• Уровень 1 показывает только Общую сумму (строка 18) и скрывает все остальные строки.
• Уровень 2 отображает Общий итог и промежуточные итоги Регион (строки 9, 17 и 18).
• Уровень 3 отображает промежуточные итоги Общая сумма, Регион и Элемент (строки 4, 8, 9, 18, 13, 16, 17 и 18).
• Уровень 4 показывает все строки.

На следующем снимке экрана показано, как схема свернута до уровня 3.

Как расширить строки в Excel

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

Или нажмите знак "плюс" для свернутой группы строк, которую вы хотите развернуть:

Как удалить контур в Excel

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

Как удалить весь контур

Перейдите на вкладку Данные > группу Структура, щелкните стрелку в разделе Разгруппировать и выберите Очистить структуру.

Примечания:

1. Удаление контура в Excel не приводит к удалению данных.
2. Если удалить структуру с некоторыми свернутыми строками, эти строки могут остаться скрытыми после очистки структуры. Чтобы отобразить строки, используйте любой из методов, описанных в разделе Как отобразить строки в Excel.
3. После того как контур будет удален, вы не сможете вернуть его, нажав кнопку Отменить или нажав сочетание клавиш Отменить ( Ctrl + Z ). Вам придется воссоздать схему с нуля.

Как разгруппировать определенную группу строк

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

1. Выберите строки, которые нужно разгруппировать.
2. Перейдите на вкладку Данные в группу Структура и нажмите кнопку Разгруппировать. Или нажмите Shift + Alt + Стрелка влево, что является ярлыком "Разгруппировать" в Excel.
3. В диалоговом окне Разгруппировать выберите "Строки" и нажмите "ОК".

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

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

Советы по группировке в Excel

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

Как автоматически рассчитать промежуточные итоги группы

Во всех приведенных выше примерах мы вставили собственные строки промежуточных итогов с формулами СУММ. Чтобы промежуточные итоги рассчитывались автоматически, используйте команду «Промежуточный итог» с выбранной вами функцией суммирования, такой как СУММ, СЧЁТ, СРЗНАЧ, МИН., МАКС. и т. д. Команда «Промежуточный итог» не только вставит итоговые строки, но и создаст схему со сворачиваемыми и расширяемыми строками. , тем самым выполняя сразу две задачи!

Применить стили Excel по умолчанию к итоговым строкам

В Microsoft Excel есть предустановленные стили для двух уровней итоговых строк: RowLevel_1 (жирный шрифт) и RowLevel_2 (курсив). Вы можете применять эти стили до или после группировки строк.

Чтобы автоматически применить стили Excel к новой структуре, перейдите на вкладку Данные > группу Структура, щелкните кнопку запуска диалогового окна Структура, затем установите флажок Автоматические стили и нажмите ОК. После этого вы создаете контур, как обычно.

Чтобы применить стили к существующему контуру, также установите флажок Автоматические стили, как показано выше, но нажмите кнопку Применить стили вместо ОК.

Вот как выглядит схема Excel со стилями по умолчанию для итоговых строк:

Как выбрать и скопировать только видимые строки

После того как вы свернули ненужные строки, вы можете скопировать отображаемые релевантные данные в другое место. Однако, когда вы выбираете видимые строки обычным способом с помощью мыши, вы фактически выбираете и скрытые строки.

Чтобы выбрать только видимые строки, необходимо выполнить несколько дополнительных действий:

Выберите видимые строки с помощью мыши.

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

А теперь просто нажмите Ctrl + C, чтобы скопировать выделенные строки, и Ctrl + V, чтобы вставить их куда угодно.

Как скрыть и показать контурные символы

Чтобы скрыть или отобразить полосы контура и номера уровней в Excel, используйте следующую комбинацию клавиш: Ctrl + 8 .

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

Символы структуры не отображаются в Excel

Если вы не видите ни символов плюса и минуса в полосах групп, ни цифр в верхней части схемы, проверьте следующую настройку в Excel:

1. Перейдите на вкладку Файл в категорию Параметры Дополнительно.
2. Прокрутите вниз до раздела Параметры отображения для этого рабочего листа, выберите интересующий рабочий лист и убедитесь, что установлен флажок Показать символы контура, если применяется контур.

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

Вы когда-нибудь использовали функцию ВПР для переноса столбца из одной таблицы в другую? Теперь, когда в Excel есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами данных на основе сопоставления данных в каждой таблице. Затем вы можете создавать листы Power View и создавать сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы взяты из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете импортировать и связать данные аналитики времени, чтобы анализировать модели продаж по годам и месяцам.

Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.

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

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

Выполните одно из следующих действий: отформатируйте данные в виде таблицы или импортируйте внешние данные в виде таблицы на новый лист.

Дайте каждой таблице значимое имя. В разделе «Инструменты для таблиц» нажмите «Дизайн» > «Имя таблицы» > введите имя.

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

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

Нажмите Данные > Отношения.

Если Отношения неактивны, ваша книга содержит только одну таблицу.

В поле "Управление отношениями" нажмите "Создать".

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

В поле "Столбец (внешний)" выберите столбец, содержащий данные, связанные со связанным столбцом (основным). Например, если бы у вас был столбец даты в обеих таблицах, вы бы выбрали этот столбец сейчас.

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

Для параметра "Связанный столбец (основной)" выберите столбец с уникальными значениями, совпадающими со значениями в столбце, который вы выбрали для параметра "Столбец".

Подробнее о связях между таблицами в Excel

Примечания о взаимоотношениях

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

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

В модели данных отношения между таблицами могут быть "один к одному" (каждый пассажир имеет один посадочный талон) или "один ко многим" (на каждом рейсе много пассажиров), но не "многие ко многим". Отношения «многие ко многим» приводят к ошибкам циклической зависимости, например «Обнаружена циклическая зависимость.Эта ошибка возникает, если вы делаете прямое соединение между двумя таблицами, которые являются многими ко многим, или косвенные соединения (цепочка связей таблиц, которые являются один ко многим внутри каждого отношения, но многие ко многим при просмотре от начала до конца. Узнайте больше о связях между таблицами в модели данных.

Типы данных в двух столбцах должны быть совместимы. Дополнительные сведения см. в разделе Типы данных в моделях данных Excel.

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

Пример: сопоставление данных аналитики времени с данными о рейсах авиакомпаний

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

Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace. Домашняя страница Microsoft Azure Marketplace откроется в мастере импорта таблиц.

В разделе "Цена" нажмите "Бесплатно".

В разделе "Категория" нажмите "Наука и статистика".

Найдите DateStream и нажмите «Подписаться».

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

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

Нажмите "Далее".

Выберите BasicCalendarUS, а затем нажмите Готово, чтобы импортировать данные. При быстром интернет-соединении импорт займет около минуты. Когда закончите, вы должны увидеть отчет о состоянии 73 414 переданных строк. Нажмите Закрыть.

Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace, чтобы импортировать второй набор данных.

В разделе "Тип" нажмите "Данные".

В разделе "Цена" нажмите "Бесплатно".

Найдите задержки рейсов авиаперевозчика США и нажмите «Выбрать».

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

Нажмите "Далее".

Нажмите «Готово», чтобы импортировать данные. При быстром подключении к Интернету импорт может занять 15 минут. По завершении вы должны увидеть отчет о состоянии 2 427 284 переданных строк. Щелкните Закрыть. Теперь у вас должно быть две таблицы в модели данных. Чтобы связать их, нам потребуются совместимые столбцы в каждой таблице.

Обратите внимание, что DateKey в BasicCalendarUS имеет формат 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени, FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одного типа, и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих нескольких шагах вы будете использовать эти столбцы для связи таблиц.

В окне Power Pivot нажмите "Сводная таблица", чтобы создать сводную таблицу на новом или существующем листе.

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

Разверните BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.

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

В списке полей в разделе «Могут потребоваться связи между таблицами» нажмите «Создать».

В связанной таблице выберите On_Time_Performance, а в связанном столбце (основной) выберите FlightDate.

В таблице выберите BasicCalendarUS, а в столбце (иностранный) выберите DateKey. Нажмите OK, чтобы создать связь.

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

В BasicCalendarUS перетащите YearKey в область строк над MonthInCalendar.

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

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

Убедитесь, что таблица BasicCalendarUS открыта в окне Power Pivot.

В таблице "Главная" нажмите "Сортировать по столбцу".

В разделе «Сортировка» выберите «Месяц в календаре».

В поле "По" выберите "МесяцГода".

Сводная таблица теперь сортирует каждую комбинацию месяц-год (октябрь 2011 г., ноябрь 2011 г.) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что фид DateStream предоставляет все необходимые столбцы, чтобы этот сценарий работал. Если вы используете другую таблицу операций со временем, ваш шаг будет другим.

"Могут потребоваться связи между таблицами"

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

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

Шаг 1. Определите, какие таблицы указать в связи

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

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

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

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

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

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

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

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

Чтобы узнать больше о связях между таблицами, см. раздел Отношения между таблицами в модели данных.

поиск меню

Урок 22. Диаграммы

Введение

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

Понимание диаграмм

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

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

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

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

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

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

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

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

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

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

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

Легенда

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

В этом примере легенда идентифицирует разных продавцов на диаграмме.

Название диаграммы

Заголовок должен четко описывать, что иллюстрирует диаграмма.

Вертикальная ось

Вертикальная ось (также известная как ось Y) — это вертикальная часть диаграммы.

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

Горизонтальная ось

Горизонтальная ось (также известная как ось x ) – это горизонтальная часть диаграммы.

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

Серии данных

Серия данных состоит из связанных точек данных на диаграмме.

В этом примере синие столбцы представляют чистые продажи Роберта Джонсона. Мы знаем, что его данные выделены синим цветом из-за легенды справа.

Читая ряд данных, мы видим, что Роберт был лучшим продавцом в 1 и 3 кварталах, а во 2 и 4 кварталах он был вторым по величине.

Чтобы вставить диаграмму:

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

Выбор ячеек A1:F6

Нажатие команды Столбчатая диаграмма

Выбор типа диаграммы

Вставленная диаграмма

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

Нажатие команды "Рекомендуемые диаграммы"

Макет и стиль диаграммы

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

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

Редактирование текста-заполнителя заголовка диаграммы

Выбор макета диаграммы

Выбор нового стиля диаграммы

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

Сочетания клавиш для форматирования диаграмм

Другие параметры диаграммы

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

Чтобы переключить данные строки и столбца:

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

Данные сгруппированы по годам со столбцами для каждого жанра

1. Выберите диаграмму, которую хотите изменить.
2. На вкладке "Дизайн" выберите команду "Переключить строку/столбец".

Нажатие команды "Переключить строки/столбцы"

Переключенные данные строки и столбца

Чтобы изменить тип диаграммы:

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

На вкладке "Дизайн" нажмите команду "Изменить тип диаграммы".

Нажатие команды "Изменить тип диаграммы"

Выбор нового типа диаграммы

Новый тип диаграммы

Чтобы переместить диаграмму:

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

1. Выберите диаграмму, которую хотите переместить.
2. Перейдите на вкладку "Дизайн", затем выберите команду "Переместить диаграмму".

Нажатие команды "Переместить диаграмму"

Перемещение диаграммы на новый лист

Диаграмма на отдельном листе

Поддержание диаграмм в актуальном состоянии

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

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

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

Это происходит, когда вам нужно написать множество формул для визуального поиска, прежде чем вы сможете начать анализировать свои данные. Каждый день миллионы аналитиков и менеджеров попадают в ад ВПР и страдают.Они соединяют таблицу 1 с таблицей 2, чтобы все данные, необходимые для создания этого сводного отчета, находились в одном месте. Если вы один из них, то вам понравится функция модели данных и взаимосвязей Excel.

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

Практический пример — ад V(X)LOOKUP против небес модели данных

Допустим, вы ищете данные о продажах своей компании. У вас есть данные о транзакциях, как показано ниже.

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

К сожалению, у вас есть только идентификатор продукта и идентификатор клиента.

С функцией VLOOKUP Hell

1. Сначала вы извлекаете все данные о клиентах и ​​продуктах и ​​помещаете их в отдельные диапазоны.
2. Затем напишите формулу прямого поиска, чтобы получить категорию продукта, и еще одну, чтобы получить пол клиента.
3. Затем заполните формулы для всего списка транзакций.
4. Теперь создайте сводную таблицу.

Предполагая, что у вас есть 30 000 транзакций, вам нужно написать 60 000 формул ВПР, чтобы создать один отчет.

С небесами модели данных,

1. Создание связей между таблицами "Продажи", "Продукты" и "Клиенты".
2. Создать сводную таблицу

Создание отношения в Excel — пошаговое руководство

1. Сначала настройте свои данные в виде таблиц. Чтобы создать таблицу, выберите любую ячейку в диапазоне и нажмите CTRL+T. Укажите имя для своей таблицы на вкладке «Дизайн». Прочтите введение в таблицы Excel, чтобы узнать больше.
2. Теперь перейдите на ленту данных и нажмите кнопку взаимосвязей.
3. Нажмите «Создать», чтобы создать новую связь.
4. Выберите исходную таблицу и имя столбца. Сопоставьте его с целевой таблицей и именем столбца. Неважно, какой порядок вы используете здесь. Excel достаточно умен, чтобы настроить отношения.
   
5. При необходимости добавьте дополнительные отношения.

Использование отношений в сводных отчетах и ​​анализе

1. Выберите любую таблицу и вставьте сводную таблицу (Вставка > Сводная таблица, подробнее о сводных таблицах).
2. Убедитесь, что установлен флажок "Добавить эти данные в модель данных".
   
3. В списке полей сводной таблицы отметьте «ВСЕ» вместо «АКТИВНО», чтобы увидеть все имена таблиц.
4. Выберите поля из разных таблиц, чтобы создать объединенный сводной отчет или сводную диаграмму.

Пример: отчет о продажах по категориям и полу

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

• Одинаковые типы данных в обоих столбцах: столбцы, которые вы соединяете в обеих таблицах, должны иметь одинаковый тип данных (например, числа, даты, текст и т. д.).
• Только отношения "один ко многим" или "один ко многим": Excel 2013 поддерживает только отношения "один ко многим" или "один ко многим". Это означает, что в одной из таблиц не должно быть повторяющихся значений в столбце, на который вы ссылаетесь. (например, в таблице товаров не должно быть повторяющихся идентификаторов товаров).
• Вы также можете добавить срезы: эти сводные таблицы можно нарезать по любому полю (так же, как обычные сводные таблицы). Например, вы можете дополнительно разбить отчет выше по профессии клиента или размеру артикула продукта.

Преимущества сводных таблиц на основе модели данных

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

• Различные подсчеты. Это простое, но часто сложное для расчета число легко получить, если у вас есть сводная информация на основе модели данных. Просто перейдите к настройкам поля значений и измените тип сводки на «Число уникальных». Вот совет, объясняющий, как получить различные значения в сводках Excel.
• Показатели и DAX. Получив модель данных, вы можете использовать все функции Power Pivot в своей книге. Вы можете создавать показатели (используя язык DAX) и вычислять то, что иначе невозможно в обычном Excel. Вот пример расчета процента чего-либо с помощью DAX и модели данных для начала.
• Сводные данные на основе данных в других файлах и базах данных. Вы можете комбинировать модель данных с возможностями Power Query для создания сводных данных на основе данных в других местах.Например, вы можете сделать сводку данных о продажах в SAP с данными о клиентах в системе CRM. Вот обзор того, что такое Power Query?
• Сводные данные по более чем 1 миллиону строк данных. С помощью модели данных вы можете подключаться к очень большим наборам данных и создавать сводные данные на их основе. Вот демонстрация того, как настроить модель данных для 1 млн строк данных.
• Преобразование сводных таблиц в формулы. Получив сводную таблицу на основе модели данных, вы можете преобразовать ее в набор формул. Вы можете получить доступ к этой функции с ленты «Анализ». Это заменит ваш пивот кучей формул CUBE. Вот обзор формул CUBE.

Недостатки модели данных:

Конечно, это не все кексы и кофе с моделью данных. У сводных таблиц на основе модели данных есть несколько недостатков.

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

Загрузить пример файла

Нажмите здесь, чтобы загрузить демонстрационный файл модели данных Excel. Он содержит 3 разные таблицы и комбинированный сводной отчет (со срезом), чтобы показать вам, что возможно.

Используете ли вы отношения?

С тех пор, как я открыл для себя PowerPivot, я перестал использовать функцию ВПР (или КСПР) для большей части собственного анализа. Теперь, когда отношения являются частью основных функций Excel, я использую их еще больше.

А вы? Используете ли вы отношения и модель данных в Excel? Какие крутые штуки ты с ним делаешь? Поделитесь с нами своими советами в комментариях.

Хотите еще? Попробуйте PowerPivot

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

Читайте также:

  
• Драйвер для подключения xiaomi redmi 5 plus к компьютеру через usb
  
• Интерполяция времени в Adobe Premiere Pro, что лучше
  
• Какие форматы поддерживает Adobe Premiere Pro
  
• Как сделать подпись в фотошопе
  
• Прошивка USB для Haier le32m600