Сводные таблицы Excel
Работа со сводными таблицами в Excel на примерах
Сводные таблицы в Excel
Попробуйте Microsoft Edge быстрый и безопасный браузер, разработанный для Windows 10 Нет Начало работы. В отчетах сводных таблиц можно использовать функции сведения в полях значений для объединения значений из исходных данных. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в отчете сводной таблицы. В отчетах сводных таблиц можно рассчитывать данные разными способами. Вы узнаете о доступных методах вычислений, о влиянии типа исходных данных на вычисления и о том, как использовать формулы в отчетах сводных таблиц и сводных диаграмм. Для вычисления значений в отчете сводной таблицы можно использовать любые из описанных ниже методов. Функции сведения в полях значений. В отчете сводной таблицы в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:. Отчет сводной таблицы и отчет сводной диаграммы выглядят, как показано на рисунке ниже. Если создать отчет сводной диаграммы на основе данных из отчета сводной таблицы, то значения на диаграмме будут соответствовать вычислениям в базовом отчете сводной таблицы. В отчете сводной таблицы поле столбца Месяц содержит элементы Март и Апрель. Поле строки Регион содержит элементы Север , Юг , Восток и Запад. В отчете сводной диаграммы поле Регион может представлять собой поле категорий, в котором элементы Север , Юг , Восток и Запад отображаются как категории. Поле Месяц поле может быть полем рядов, в котором элементы Март , Апрель и Май отображаются как ряды, представленные в легенде. Поле значений с именем Сумма продаж может содержать маркеры данных, которые представляют общую выручку в каждом регионе за каждый месяц. Например, один маркер данных может представлять своим положением на вертикальной оси, т. Ниже перечислены функции сведения, с помощью которых можно вычислять поля значений. Эти функции доступны для всех типов исходных данных, кроме OLAP. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых. Действует аналогично функции СЧЁТ. Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. Служат для отображения значений на основе других элементов или ячеек в области данных. Например, можно отобразить значения в поле данных Сумма продаж как процент от продаж за месяц Март или как нарастающий итог по элементам в поле Месяц. Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду. Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории. Значения в процентах от значения базового элемента в соответствующем базовом поле. Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле. Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле. Значение в виде нарастающего итога для последовательных элементов в базовом поле. Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в отчете. Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP. Вычисления на основе исходных данных OLAP. При создании отчетов сводных таблиц на основе кубов OLAP сводные значения вычисляются на сервере OLAP еще до отображения результатов в Excel. В отчете сводной таблицы невозможно изменить способ вычисления этих значений. Например, вы не сможете выбрать другую функцию сведения для вычисления полей данных или промежуточных итогов и добавить вычисляемые поля или вычисляемые объекты. Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые "вычисляемыми элементами", вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений VBA и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP. Если исходные данные получены из базы данных OLAP, то при вычислении промежуточных и общих итогов можно включить или исключить значения для скрытых элементов. Вычисления на основе исходных данных не из базы данных OLAP. Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например "Среднее", "Максимум" или "Минимум". Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле. Формулы можно создавать только в отчетах, которые основаны на исходных данных, полученных не из базы данных OLAP. В отчетах, основанных на базе данных OLAP, формулы не используются. При использовании формул в отчетах сводных таблиц нужно учитывать описанные ниже правила синтаксиса и поведения формул. Элементы формулы сводной таблицы. В формулах, которые создаются для вычисляемых полей и вычисляемых объектов, можно использовать операторы и выражения, как и в других формулах на листе. Также можно использовать константы и ссылаться на данные из отчета, но не допускается использование ссылок на ячейки и определенных имен. Невозможно использовать функции листа, для которых нужны аргументы в виде ссылок на ячейки или определенных имен, а также формулы массива. Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3: C9 используется имя поля Молоко. В отчете сводной диаграммы имена полей отображаются в списке полей сводной таблицы, а имена элементов можно просмотреть в каждом раскрывающемся списке полей. Не следует путать эти имена с теми, которые отображаются в подсказках к диаграммам и соответствуют именам рядов и точек данных. Формулы работают с итоговыми суммами, а не с отдельными записями. Формула для вычисляемого поля оперирует суммой исходных данных для каждого используемого поля. Формулы для вычисляемых объектов оперируют отдельными записями. Пробелы, цифры и символы в именах. В имени, которое содержит два или несколько полей, их порядок не имеет значения. В примере выше ячейки C6: Имена, которые состоят из нескольких слов либо содержат цифры или символы, нужно заключать в одинарные кавычки. Имена полей в ссылках на элементы. Вы можете включить имя поля в ссылку на элемент. Имя элемента должно быть заключено в квадратные скобки, например: Используйте этот формат, чтобы избежать ошибок ИМЯ? Например, если в отчете есть два элемента с именем "Мясо" в полях "Тип" и "Категория", можно избежать появления ошибок ИМЯ? Ссылки на элементы по позиции. Вы можете сослаться на элемент, указав его позицию в отчете с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе. Для ссылки на элементы можно использовать относительные позиции. Они определяются относительно вычисляемого объекта, содержащего формулу. Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку ССЫЛКА!. Чтобы использовать формулы в отчете сводной диаграммы, их нужно создать в связанном отчете сводной таблицы. Там вы увидите отдельные значения, из которых состоят данные, а затем сможете посмотреть на результаты в графическом представлении в отчете сводной диаграммы. Например, в этом отчете сводной диаграммы представлены данные о продажах для каждого продавца по регионам:. Однако вычисляемый объект, созданный в поле "Продавец", будет отображаться как ряд, представленный в легенде, и появится на диаграмме в виде точки данных в каждой категории. Создание формул в отчете сводной таблицы Важно: Создать формулу в отчете сводной таблицы, подключенном к источнику данных OLAP, невозможно. Прежде всего определитесь, что нужно создать в поле: Если вы хотите использовать в формуле данные из другого поля, создайте вычисляемое поле. Если нужно использовать в формуле данные из одного или нескольких конкретных элементов в определенном поле, создайте вычисляемый объект. В случае вычисляемых объектов в отдельных ячейках можно вводить разные формулы. Отобразится вкладка "Работа со сводными таблицами" с дополнительными вкладками Анализ и Конструктор. Чтобы использовать в формуле данные из другого поля, щелкните его в списке Поля и нажмите кнопку Добавить поле. Если элементы в поле сгруппированы, на вкладке Анализ в группе Группировать выберите команду Разгруппировать. Чтобы использовать в формуле данные из имеющегося элемента, щелкните его в списке Элементы и нажмите кнопку Добавить элемент он должен находиться в том же поле, что и вычисляемый объект. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки. Щелкните одну из формул и нажмите кнопку Вверх или Вниз. Перед тем как редактировать формулу, определите, к чему она относится: Если формула находится в вычисляемом объекте, также определите, является ли она единственной для него. В случае вычисляемых объектов можно изменять отдельные формулы для конкретных ячеек одного объекта. Найдите в списке формулу, которую нужно изменить. Она может находиться в разделе "Вычисляемое поле" или "Вычисляемый объект". Если для одного вычисляемого объекта задано несколько формул, то формулой по умолчанию, введенной при его создании, является та, для которой в столбце B указано его имя. Для остальных формул в столбце B указывается не только имя самого вычисляемого объекта, но и имена элементов, на пересечении которых используется формула. Предположим, что есть формула по умолчанию для вычисляемого объекта с именем МойЭлемент , а также другая формула для этого объекта с именем МойЭлемент Январь Продажи. В отчете сводной таблицы вы увидите эту формулу в ячейке "Продажи" для строки "МойЭлемент" и столбца "Январь". В списке Имя выберите вычисляемое поле, для которого нужно изменить формулу. Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений. Если удалить формулу из сводной таблицы, восстановить ее будет невозможно. Если вы не хотите удалять формулу безвозвратно, то можете просто скрыть поле или элемент, перетащив его за пределы отчета сводной таблицы. Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые объекты отображаются в виде элементов в других полях. Просмотр всех формул, используемых в отчете сводной таблицы Чтобы отобразить список всех формул, которые используются в текущем отчете сводной таблицы, выполните указанные ниже действия. Определите, к чему относится формула: Если формула находится в вычисляемом объекте, также определите, является ли она единственной для него, выполнив указанные ниже действия. Редактирование одной формулы для вычисляемого объекта. Редактирование отдельных формул для конкретных ячеек вычисляемого объекта. Если есть несколько вычисляемых объектов или формул, измените порядок вычислений, выполнив указанные ниже действия. Если вы не хотите удалять формулу безвозвратно, то можете просто скрыть поле или элемент. Чтобы скрыть поле, перетащите его за пределы отчета. Поиск в справке Office Нет результатов. Импорт и анализ данных. Вычисление значений в отчете сводной таблицы. Дополнительные сведения о вычислении значений в отчете сводной таблицы. Доступные методы вычислений Для вычисления значений в отчете сводной таблицы можно использовать любые из описанных ниже методов. Рассмотрим пример с такими исходными данными: Функция Сведение данных Сумма Сумма значений. Функция по умолчанию для числовых данных. Количество чисел Количество числовых значений. Стандартное отклонение Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. Несмещенное отклонение Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. Дисперсия Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. Несмещенная дисперсия Дисперсия генеральной совокупности, которая содержит все сводимые данные. Для настраиваемых вычислений в полях значений доступны перечисленные ниже функции. Функция Результат Без вычислений Значение, введенное в данное поле. Доля Значения в процентах от значения базового элемента в соответствующем базовом поле. Отличие Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле. Приведенное отличие Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле. С нарастающим итогом в поле Значение в виде нарастающего итога для последовательных элементов в базовом поле. Индекс Рассчитывает значения следующим образом: Влияние типа источника данных на вычисления Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP. Использование формул в отчетах сводных таблиц Формулы можно создавать только в отчетах, которые основаны на исходных данных, полученных не из базы данных OLAP. Выберите отчет сводной таблицы. Редактирование формулы в сводной таблице Определите, к чему относится формула: Выполните одно из указанных ниже действий. В поле Формула измените формулу. В поле Имя выберите вычисляемый объект. Щелкните ячейку, для которой нужно изменить формулу. В строке формул внесите изменения в формулу. Повторите эти действия для других формул, чтобы расположить их в нужном порядке. Удаление формулы из сводной таблицы Совет: Первоочередный доступ к новым возможностям. Были ли сведения полезными? Что мы могли бы улучшить? Спасибо за ваш отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office. Обращение в службу поддержки. Обучение Windows Office Skype Outlook OneDrive MSN. Устройства Microsoft Surface Xbox Устройства на Windows Microsoft Lumia. Магазин Microsoft Адресная книга Заказы Возврат товаров Продажа и поддержка. Загрузки Центр загрузок Microsoft Загрузки для Windows Windows 10 Apps Office Apps Microsoft Lumia Apps Internet Explorer. Ценности Правила обеспечения конфиденциальности корпорации Майкрософт Образование. Компания Карьера в Microsoft Microsoft Новости и публикации Продукты Политика по персональным данным. Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. Дисперсия генеральной совокупности, которая содержит все сводимые данные. Значения в процентах от общей суммы всех значений или точек данных в отчете. Рассчитывает значения следующим образом:
Проверка свойств и значений. Работы с датами и временем. Преобразования в системы счисления. Вход Регистрация Напомнить пароль. Главная сайта Главная форумов Блог Пройти тестирование Заказать работу Главные страницы. О сайте Контакты Карта сайта Книга отзывов Статистика Информация о сайте. Новости Помочь сайту Развитие сайта. Часто задаваемые вопросы FAQ. Рабочая область Формулы Сводные таблицы Форматирование Выпадающие списки Примечания Гиперссылки Приемы работы. Инструменты Тонкая настройка Инструменты и настройки. Импорт данных Excel и Web. Различия Excel для Windows и MacOS Excel Windows и Excel Macintosh. Формулы Сводные таблицы Диаграммы Полезные приемы Excel. Процедуры Пользовательские функции Полезные приемы VBA. Правила форума Главная форумов Главные страницы. Вопросы по Excel Вопросы по VBA Готовые решения Excel и другие приложения Вопросы и решения. Вопросы по работе форума и сайта Объявления администрации Работа форума и сайта. Ленточный вариант форума Новые сообщения. Функции и формулы Графики и диаграммы Сводные таблицы Excel и VBA Книги по Excel. Применение VBA в Excel Программирование на VBA Книги по VBA. Сборники книг Книги для студентов ВУЗов Специализированные книги Разное. Видео к статьям Видеофайлы Видео. Все функции Автосумма Часто используемые ВПР ЕСЛИ СУММЕСЛИ. Статистические Инженерные Аналитические Проверка свойств и значений Баз данных. Visual Basic Макросы Пользовательские функции Пользовательские формы Код. Надстройки Надстройки COM Надстройки. Элементы управления формы Элементы ActiveX Элементы управления. Листы макросов Макрофункции XLM. Программы для работы с Excel Программы на VBA Программы. Во-первых хочу сказать спасибо Вашему форуму за помощь. Нужно добавить в сводную таблицу расчетный столбец, который бы показывал средние продажи за 3 месяца текущего года. К сообщению приложен файл: Автор - qpp Дата добавления - Можно ли вывести формулу которая будет автоматом пересчитывать последние 3 месяца? Сообщение то есть при обновлении данных нужно будет руками двинуть диапазон. Сообщение отредактировал qpp - Среда, Сообщение прошу прощения, но я не могу добавить вычисляемый объект в свой файл, как вы это сделали, объясните пожалуйста. Сообщение спасибо Автор - qpp Дата добавления - Добрый день, столкнулся с проблемой, что не работет вычисляемое поле, если в его формуле стоит условие ЕСЛИ по строковым полям в примере желтым. При этом если добавить колонку, где строковому полю соответствует число, то все работает. А так же не считаются итоги по вычисляемому полю в сводной таблице, если вычисляемое поле содержит формулу с условием ЕСЛИ в примере красным , при этом само поле расчитывается. Уже не знаю куда ткнуться, ниге не могу найти описание подобной проблемы. Уже не знаю куда ткнуться, ниге не могу найти описание подобной проблемы AiK. Сообщение Добрый день, столкнулся с проблемой, что не работет вычисляемое поле, если в его формуле стоит условие ЕСЛИ по строковым полям в примере желтым. Уже не знаю куда ткнуться, ниге не могу найти описание подобной проблемы Автор - AiK Дата добавления - AiK , на мой взгляд, проще всего сделать вычисляемый столбец для суммы в исходной таблице и его уже использовать в сводной, тогда и итоги получатся, иначе итоги по вычисляемому полю, созданному в сводной таблице с использованием функции ЕСЛИ все равно не будут считаться так, как надо сама с этим сталкивалась. Сообщение AiK , на мой взгляд, проще всего сделать вычисляемый столбец для суммы в исходной таблице и его уже использовать в сводной, тогда и итоги получатся, иначе итоги по вычисляемому полю, созданному в сводной таблице с использованием функции ЕСЛИ все равно не будут считаться так, как надо сама с этим сталкивалась Автор - Pelena Дата добавления - Понятно, хотелось бы без лишних колонок. Но, видимо, другого варианта нет. Сообщение Понятно, хотелось бы без лишних колонок. У меня тоже проблема с использованием в вычисляемом поле формулы с функцией ЕСЛИ при текстовом условии. Добавить поле в исходник не возможно - так как исходник вешний access. Может есть решение все же без добавления доп. Сообщение У меня тоже проблема с использованием в вычисляемом поле формулы с функцией ЕСЛИ при текстовом условии. Если Вы подгружаете данные через MS Query, то можно в запросе создать вычисляемое поле. В любом случае, это другой вопрос и другая тема. Читайте Правила форума, создавайте свою тему в соответствующем разделе, прикладывайте пример. Сообщение Если Вы подгружаете данные через MS Query, то можно в запросе создать вычисляемое поле. Автор - Pelena Дата добавления - Главная Excel Готовые решения Форумы Библиотека Видео Справочники Разработчик. Отметить все сообщения прочитанными и перейти на главную страницу форума. Страница 1 из 1. Можно, если сделать доп. Для и ранее, так: Уже не знаю куда ткнуться, ниге не могу найти описание подобной проблемы AiK К сообщению приложен файл: Спасибо Quote Pelena ? Pelena "Черт возьми, Холмс!
Внутреннее ухо болезни
Скачать гта 5 через стим
Кхл новости хк цска
Тверь расположение на карте
Рейтинг шин для легковых автомобилей