Содержание
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Для сведения данных в сводной таблице можно использовать такие функции, как СУММ, СЧЁТ и СРЗНАЧ.
По умолчанию для значений в числовых полях сводной таблицы используется функция СУММ. Ниже описано, как выбрать другие функции сведения данных.
В сводной таблице щелкните правой кнопкой мыши то поле, которое нужно изменить, и выберите пункт Итоги по.
Выберите нужную функцию сведения данных.
Примечание: Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.
Функция сведения данных
Сумма значений. Используется по умолчанию для полей с числовыми значениями. Если поле является пустым или содержит нечисловые значения (текст, даты или логические величины), при его переносе в область значений списка полей сводной таблицы для него используется функция СЧЁТ.
После переноса поля в область значений можно изменить функцию сведения на СУММ; при этом для всех пустых полей и нечисловых значений в сводной таблице будет задано значение 0, чтобы их можно было суммировать.
Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями.
Excel работает за вас
Excel works!
Thanks for Visiting
Подсчет уникальных значений. Несколько способов, в т.ч. формулой
Тому, кто давно работает с Excel задача «подсчет уникальных значений» в таблице вполне знакома. Можно подсчитать и специальной функцией, и сводной таблицей, но вот подсчет формулой знаком не всем — способ довольно интересный, рекомендую. Но обо всем по порядку.
Количество уникальных значений может понадобиться в разных ситуациях. Например у вас большая таблица показателей по городам, как подсчитать сколько городов всего было задействовано? Разберем 3 способа
Подсчет уникальных значений. Специальная возможность Excel
В табличном редакторе есть специальная кнопка — Данные — Удалить дубликаты. Здесь останавливаться на этом способе не буду, т.к. на сайте есть отдельная статья . Когда вы удалите дубликаты, то можно подсчитать оставшиеся уникальные города функцией СЧЁТЗ.
Считаем неповторяющиеся значения сводной таблицей
Распространенный способ у любителей Excel подсчитать уникальные значения сводной таблицей. Вполне удобно. Если вы еще на знаете о сводных таблицах — рекомендую прочитать тут .
Выделяем таблицу с городами. Идем на вкладку Вставка на ленте (для версии 2007 и выше) в разделе. Самая левая кнопка — Сводная таблица. Жмем ее.
На отдельном листе формируем сводную таблицу — перетаскиваем/добавляем Города в название строк.
В получившейся таблице считаем уникальные значения
Подсчет уникальных значений формулой
Переходим к самому интересному — как посчитать уникальные города формулой? Здесь нам поможет одна хитрость и формула СЧЁТЕСЛИ . Специальной функции для подсчета уникальных значений в таблице нет, но проявим изобретательность. По факту нам нужно для каждого города понимать сколько раз он встречается в таблице и вместо этого итогового количества учитывать город 1 раз. Если город встречается 4 раза, то 1 строка, исходя из логики предыдущего предложения должна учитываться как четверть раза, т.е. 1 деленный на 4.
Теперь можно посчитать сумму по этому столбцу
Но можно пойти дальше и посчитать все в одной ячейке при помощи СУММПРОИЗВ.
Это уже совсем модно.
Когда я первый раз сам додумался до этого был очень рад, но потом увидел, что таким решением заполнен интернет. Этого следовало ожидать. Но я как и всегда стараюсь описать несколько способов решения проблемы, поэтому, думаю вам будет полезно.
Для отбора уникальных значений можно использовать формулы, расширенный фильтр или можно воспользоваться меню Данные/ Работа с данными/ Удалить дубликаты . В этой статье используем Сводные таблицы.
Пусть в столбце B имеется список с повторяющимися значениями, например список с названиями компаний (см. файл примера ). Столбец А содержит номера позиций уникальных значений.
Для наглядности уникальные значения в исходном списке выделены цветом с помощью Условного форматирования.
Используем сводную таблицу для создания списка уникальных значений. Для этого выделите столбец В таблицы с заголовком (т.е. столбец Исходный список) и во вкладке Вставка , в группе Таблицы нажмите кнопку Сводная таблица .
Примечание: Выделять столбец требуется для того, чтобы сводная таблица содержала только одно поле (столбец В). В противном случае сводная таблица будет содержать 2 поля. Это не повлияет на вычисления, но для наглядности пока не будем включать в сводную таблицу столбец А.
Поле Сводной таблицы Исходный список перетащите в область Названия строк.
Список уникальных значений сформирован. Обратите внимание, что значения в сводной таблице отсортированы по возрастанию.
Сортировка как в источнике данных
Чтобы сохранить сортировку как в исходной таблице нам потребуется создать дополнительный столбец в источнике данных сводной таблицы. Для этого в столбце А введите формулу
Эта формула пронумерует все первые повторы значений, остальные строки будут содержать значение Пустой текст "".
Теперь создадим другую сводную таблицу. Для этого нужно выделить любую ячейку в диапазоне таблице (А7:В22). В этой таблице будет 2 поля.
Поле Исходный список, как и для предыдущей таблицы, поместите в область строк. Поле Позиция поместите в область значений. Нажмите на это поле в области значений и в меню выберите пункт Параметры полей значений. В появившемся диалоговом окне выберите Минимум.
В итоге получим сортировку как в исходной таблице.
Загрузка...
