Semenalidery.com

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

Вывод итогов в excel

Работа со строкой итогов «умной таблицы» Excel

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

Чтобы добавить строку итогов в таблицу, выберите любую ячейку внутри нее и на появившейся контекстной вкладке » Конструктор » в группе команд » Параметры стилей таблицы » установите галочку напротив » Строка итогов «.

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

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

Доступны следующие варианты:

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

Особенности работы со строкой итогов

  • В строку итогов можно вводить любые формулы (не обязательно выбирать только варианты из выпадающего списка);
  • При выборе варианта из выпадающего списка в ячейку вставляется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ с соответствующим аргументом;
  • Если отключить строку итогов, добавить/удалить строки таблицы и снова включить — в строке итогов будут те же формулы, что и до отключения (но соответствующим образом будут изменены ссылки на диапазоны ячеек);
  • Чтобы добавить данные в таблицу без отключения строки итогов, нужно вставить новую строку перед ней. Это можно сделать командой «Вставить строку» или нажав Tab, когда активна ячейка последнего столбца последней строки;
  • Если сослаться на ячейку строки итогов в формуле, то ссылка примет вид: ИмяТаблицы[[#Итоги];[Название столбца по которому подведен итог]]. Если после этого скрыть строку итогов — такая формула вернет ошибку #ССЫЛКА!

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel — подписывайтесь на наш канал в Telegram Excel Everyday

Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) — на нашем канале в Telegram Office Killer

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

Вопросы по другому ПО (кроме Excel) задавайте второму боту — @KillOfBot

Группы и промежуточные итоги в Excel

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

Группировка строк и столбцов в Excel

  1. Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.
  2. Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.
  3. Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

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

Как скрыть и показать группы

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

Подведение итогов в Excel

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

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

Создание промежуточного итога

В следующем примере мы воспользуемся командой Промежуточный итог, чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

Читать еще:  Сломать пароль excel

  1. Прежде всего отсортируйте данные, для которых требуется подвести итог. В этом примере мы подводим промежуточный итог для каждого размера футболок, поэтому информация на листе Excel должна быть отсортирована по столбцу Размер от меньшего к большему.
  2. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
  3. Откроется диалоговое окно Промежуточные итоги. Из раскрывающегося списка в поле При каждом изменении в, выберите столбец, который необходимо подытожить. В нашем случае это столбец Размер.
  4. Нажмите на кнопку со стрелкой в поле Операция, чтобы выбрать тип используемой функции. Мы выберем Количество, чтобы подсчитать количество футболок, заказанных для каждого размера.
  5. В поле Добавить итоги по выберите столбец, в который необходимо вывести итог. В нашем примере это столбец Размер.
  6. Если все параметры заданы правильно, нажмите ОК.
  7. Информация на листе будет сгруппирована, а под каждой группой появятся промежуточные итоги. В нашем случае данные сгруппированы по размеру футболок, а количество заказанных футболок для каждого размера указано под соответствующей группой.

Просмотр групп по уровням

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

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

  1. Щелкните нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.
  2. Щелкните следующий уровень, чтобы отобразить более подробную информацию. В нашем примере мы выберем уровень 2, который содержит все строки с итогами, но скрывает остальные данные на листе.
  3. Щелкните наивысший уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.

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

Удаление промежуточных итогов в Excel

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

  1. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
  2. Откроется диалоговое окно Промежуточные итоги. Нажмите Убрать все.
  3. Все данные будут разгруппированы, а итоги удалены.

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

Промежуточные итоги в Excel

Промежуточные итоги в Excel

Добрый день, уважаемые читатели! Сегодня речь пойдёт о формуле ПРОМЕЖУТОЧНЫЕ ИТОГИ. Для меня самым большим вопросом всегда оставалось предназначение этой формулы. В этой статье я постараюсь раскрыть суть наших манипуляций с данной функцией.

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

Если мне по каким-либо причинам понадобится скрыть отдельные строки (фильтр, к примеру) обычная формула автосуммы (СУММ), всё равно будет включать даже скрытые значения.

А что делать если я хочу сделать отбор только по контрагенту «Юлмарт» и посчитать итоги только по нему? Вот здесь и пригодится формула промежуточных итогов.

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

Взглянем на формулу — =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Сумма]). Нас особенно будут интересовать аргументы в скобках, так как название формулы, думается, пояснять не стоит.

В скобках мы видим загадочную цифру 109 и слово Сумма. Здесь тоже ничего загадочного нет, так как взглянув на справку от компании Майкрософт всё тут же станет ясно.

Цифра 109 обозначает что нужно делать, если включён фильтр (в названии столбца с сотнями так и написано — «с исключением скрытых значений» если же нам нужно «с включением скрытых значений» просто заменим на нужный элемент из таблицы). Если бы мне понадобилось другое значение, к примеру, 104 — максимальное значение в таблице, я просто могу от руки поправить формулу или просто выбрать её из выпадающего списка.

Видите, мне тут же покажет максимальную сумму продажи другому контрагенту — «Топкомпьютер». Аналогично можно попробовать и минимальное значение.

Также можно исправлять всё не вручную, а использовать открывающийся список после нашей ячейки с формулой.

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

Вот и всё! Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ поможет вам быстро производить расчёты даже при скрытых данных!

Если что-то осталось непонятным — смотрите наше новое видео! А вот ССЫЛКА на таблицу из статьи! Наша ПРОШЛАЯ СТАТЬЯ про функцию «Заменить»!

Читать еще:  Какая формула в excel вычитание

Вывод итогов в excel

8. Автоматическое подведение промежуточных итогов

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

  1. Отсортируйте исходный список по столбцу, для которого необходимо подвести промежуточные итоги. Применительно к нашему примеру это столбец Фамилия продавца.
    Различают итоги двух видов: промежуточные и вложенные. Рассмотрим подведение каждый из итогов в отдельности.
  2. Щелкните мышью в любой ячейке отсортированного списка.
  3. Выберите команду ДанныеИтоги
  4. В появившемся окне Промежуточные итоги (рис. 8.2) выберите столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.
  5. Выберите функцию, необходимую для подведения итогов, из списка Операция. Для нашего примера выберите функцию Сумма.
  6. Выберите столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по. Применительно к нашему примеру таким столбцом будет Сумма.

№ п.п.

Дата

Фамилия продавца

Категория товара

Сумма

Рис.8.1. Список отпущенных товаров

Задайте режимы отображения итогов:
Заменить текущие итоги. Этот режим используется для вычисления новых промежуточных итогов с заменой текущих (если они были). При подведении обычных (не вложенных итогов) этот режим необходимо задавать всегда.

Рис.8.2. Вид окна Промежуточные итоги

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

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

    Рис. 8.3. Вид экрана с подведенными промежуточными итогами

    Замечание. Если на листе отсутствуют символы структуры, то установите флажок Символы структуры вкладки Вид диалогового окна Параметры (меню Сервис).

    Структура позволяет скрыть и отобразить уровни детализации простым нажатием кнопки мыши на кнопках с изображением знака «-« или «+» соответственно. Для просмотра или скрытия детальных данных в структуре щелкайте мышью по символам уровня строки или столбца ( 1 2 3 ).

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

    После подведения итогов Excel выводит информацию для низшего уровня структуры. Применительно к рассматриваемому примеру это уровень 3; информация для этого уровня детализации и отображена на рис. 8.3.

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

    Подведение вложенных или многоуровневых итогов

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

    1. Чтобы вставить итоги для групп, находящихся внутри уже существующих групп, необходимо отсортировать список по двум или более столбцам, для которых необходимо подвести итоги. Например, чтобы просуммировать отпущенный товар по фамилиям продавцов и по категориям товара, следует отсортировать список по столбцу Фамилия продавца, а затем — по столбцу Категория товара.
    2. Подведите итоги для первого (внешнего) уровня по столбцу Фамилия продавца. В окне Промежуточные итоги обязательно должен быть включен режим Заменить текущие итоги.
    3. Подведите итоги для второго (внутреннего) уровня по столбцу Категория товара. Для этого:
      • Щелкните мышью в любой ячейке исходного списка и дайте команду Данныеa Итоги.
      • Выберите следующий столбец, по которому необходимо подвести итоги, из списка При каждом изменении в. (в нашем примере – это столбец Категория товара).
      • Выключите режим Заменить текущие итоги в окне Промежуточные итоги и нажмите кнопку OK.
    4. Повторите пункт 3 для каждого столбца, по которому необходимо подвести внутренние (вложенные) итоги.

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

    Рис. 8.4. Фрагмент списка с вложенными итогами

    Удаление промежуточных итогов

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

    1. Щелкните мышью в любой ячейке списка, содержащего промежуточные итоги.
    2. Дайте команду Данныеa Итоги
    3. Нажмите кнопку Убрать все.

    Итоговые функции для промежуточных итогов

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

    Функция СУММЕСЛИ и ПРОМЕЖУТОЧНЫЕ ИТОГИ

    Пожалуйста помогите разобраться с формулами

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

    2. Как выглядит формула СУММЕСЛИ при условии что критерий нужно написать руками?
    стандартная формула выглядит вот так : =СУММЕСЛИ(I2:I35;I16;H2:H35)
    а нужно, что бы критерий I16 был равен какому то слову например=КТ

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

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

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

    Промежуточные итоги + Счетесли
    Добрый день. Не нашел ответа на свой вопрос. После сравнения двух таблиц на одном листе через.

    Промежуточные итоги по строке
    Добрый днь. Подскажите пожалуйста. Есть файл, в котором посредством кнопок (от 1 до 12).

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

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

    по п.3 требуется сумма чисел в листе октябрь в столбце «предоплата» (J) при условии, что там проставлена дата за октябрь + сумма чисел в листе ноябрь в столбце «предоплата» (J) при условии что там проставлена дата за октябрь.
    то есть, есть клиенты которые заплатили в октябре, выполнение заказа в октябре, а есть клиенты которые заплатили тоже в октябре, но выполнение в ноябре. требуется сумма денег полученных в октябре , при этом не имеет значения когда стоит выполнение заказа в октябре или ноябре, поэтому суммируем 2 листа.
    получилось объяснить ?

    Добавлено через 3 минуты
    и еще вопрос

    как посчитать в промежуточных итогах сумму ячеек без заливки?

    есть вот такая формула
    =СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ(D$2;СТРОКА($2:$44);0))*(C3:C45=»отменен»))

    но она считает ячейки с критерием «отменен»
    а мне нужна сумма ячеек (числа) без заливки..

    =СУММЕСЛИМН($H$2:$H$34;$I$2:$I$34;»нал»;$B$2:$B$34;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;$B$2:$B$34))
    =СУММЕСЛИМН($H$2:$H$34;$I$2:$I$34;»онлайн»;$B$2:$B$34;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;$B$2:$B$34))
    =СУММЕСЛИМН($H$2:$H$34;$I$2:$I$34;»КТ»;$B$2:$B$34;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;$B$2:$B$34))

    Это работает!
    все погоняла данные считаются, расшарила на другие критерии — спасибо

    Добавлено через 27 минут
    «..С третьим пунктом все еще довольно туманно — предоплата в октябре м.б. только на листах «Октябрь» и «Ноябрь»? Если да, то возможный вариант отмечен на листе «Октябрь». Если нет — тут надо подробнее разбираться, а то вдруг у Вас могут быть случаи, когда в октябре внесли предоплату за декабрь, январь и т.д.? ..»

    Простите не нашла где цитату вставить.

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

    посмотрите часть формулы

    где знак «» должен определить пустую ячейку
    логика такова , если в столбце K2:K35 — пустая ячейка, произвести подсчет в столбце H2:H35 и суммировать с ноябрем

    только со знаком «» формула у меня не работает, так как я не знаю как правильно обозначить пустую ячейку.

    Ссылка на основную публикацию
    Adblock
    detector