Excel сортировка данных автоматическое подведение итогов

Подвести промежуточные итоги в таблице Excel можно с помощью встроенных формул и соответствующей команды в группе «Структура» на вкладке «Данные».

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

Вычисление промежуточных итогов в Excel

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

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

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

• Таблица оформлена в виде простого списка или базы данных.
• Первая строка – названия столбцов.
• В столбцах содержатся однотипные значения.
• В таблице нет пустых строк или столбцов.

Приступаем…

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



2. Выделяем любую ячейку в таблице. Выбираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги».



3. Заполняем диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выбираем условие для отбора данных (в примере – «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.



4. Закрываем диалоговое окно, нажав кнопку ОК. Исходная таблица приобретает следующий вид:

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

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

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

Чтобы промежуточные данные отображались НАД группой, снимите условие «Итоги под данными».

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

Снова вызываем меню «Промежуточные итоги». Снимаем галочку «Заменить текущие». В поле «Операция» выбираем «Среднее».



Формула «Промежуточные итоги» в Excel: примеры

Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .

Номер функции – число от 1 до 11, которое указывает статистическую функцию для расчета промежуточных итогов:

1. – СРЗНАЧ (среднее арифметическое);
2. – СЧЕТ (количество ячеек);
3. – СЧЕТЗ (количество непустых ячеек);
4. – МАКС (максимальное значение в диапазоне);
5. – МИН (минимальное значение);
6. – ПРОИЗВЕД (произведение чисел);
7. – СТАНДОТКЛОН (стандартное отклонение по выборке);
8. – СТАНДОТКЛОНП (стандартное отклонение по генеральной совокупности);
9. – СУММ;
10. – ДИСП (дисперсия по выборке);
11. – ДИСПР (дисперсия по генеральной совокупности).

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

Особенности «работы» функции:

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

Рассмотрим на примере использование функции:

1. Создаем дополнительную строку для отображения промежуточных итогов. Например, «сумма отобранных значений».
2. Включим фильтр. Оставим в таблице только данные по значению «Обеденная группа «Амадис»».
3. В ячейку В2 введем формулу: .

Формула для среднего значения промежуточного итога диапазона (для прихожей «Ретро»): .

Формула для максимального значения (для спален): .

Промежуточные итоги в сводной таблице Excel

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

1. При формировании сводного отчета уже заложена автоматическая функция суммирования для расчета итогов.



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



3. Для выведения на экран итогов по отдельным значениям используйте кнопку фильтра в правом углу названия столбца.

В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».

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

Таким образом, для отображения промежуточных итогов в списках 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 обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

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. Все данные будут разгруппированы, а итоги удалены.

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

Можно выполнять сортировку данных по
тексту (от А к Я или от Я к А), числам
(от наименьших к наибольшим или от
наибольших к наименьшим), а также датам
и времени (от старых к новым или от
новых к старым) в нескольких столбцах.
Можно также выполнять сортировку по
настраиваемым спискам (таким как
состоящий из элементов «Большой»,
«Средний» и «Маленький») или по формату,
включая цвет ячеек, цвет шрифта, а также
по значкам. Большинство сортировок
применяются к столбцам, но возможно
также применить сортировку к строкам.
Чтобы выполнить сортировку, установите
курсор на любую ячейку в таблице, которую
требуется отсортировать. Выделять
диапазон данных не надо, MS Excel разберется
в границах таблицы самостоятельно.
Выполните: Данные р Сортировка. В
диалоговом окне выберите столбцы
сортировки в полях Сортировать
по (при необходимости Затем по),
установите остальные параметры сортировки
и нажмите кнопку {OK}. Учтите, что если
Вы не выберите сортировку по подписям
столбцов, Ваши заголовки могут оказаться
в середине таблицы!

При помощи Excel можно подсчитать
промежуточные итоги или итоги для
какой-либо части данных листа электронной
таблицы. Например, в электронной таблице,
содержащей сведения о продажах для трех
различных категорий продуктов, можно
сначала отсортировать продукты по
категории, а затем выделить все ячейки,
содержащие данные, и открыть диалоговое
окно Промежуточные итоги (меню Данные,
команда Итоги). В диалоговом окне
Промежуточные итоги можно выбрать
столбец, на основе которого рассчитывать
промежуточные итоги (например, каждое
изменение значения в столбце «Неделя»),
статистический расчет, который необходимо
выполнить, и столбец или столбцы со
значениями, которые нужно обработать
статистически. Например, можно подсчитать
промежуточные итоги для числа единиц
товара, проданных в каждой категории
(см. предыдущую иллюстрацию). Подсчитанные
промежуточные итоги отображаются в
электронной таблице.

43. Word. Форматирование документов: параметры и способы форматирования символа, абзаца, страницы.

Форматирование – это оформление
документа путем выбора и изменения
шрифта, установления параметров абзацев,
применения списков и т.п.

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

Обратите внимание, что наследование
установленных форматов происходит по
направлению курсора.

Новые установки по форматированию
действуют на выделенный или вновь
набираемый текст.

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

Форматирование шрифта.

Для выбора нужного шрифта выполните:
Формат р Шрифт.

В открывшемся окне выберите имя шрифта
(осторожно с русскими буквами), способ
начертания, размер букв, вид подчеркивания,
цвет и эффекты.

Будьте осторожны с вкладкой Анимация,
поскольку «живые» шрифты утомляют
зрение. Выбор типа шрифта и его размер
можно выбрать в панели Форматирования.

Форматирование абзацев.

С точки зрения текстового процессора,
абзац – это фрагмент текста между двумя
нажатиями {Enter}. Для форматирования
абзаца выполните: Формат р Абзац.
Откроется диалоговое окно. В нем
устанавливают левую и правую границу
текста в абзаце, а также отступ «красной»
строки (удобнее выполнить с помощью
линейки); способ выравнивания абзаца
на странице (по левой границе, по центру,
по ширине); величину междустрочного
интервала (пропорционален размеру
шрифта) и интервала между абзацами.

Работа со списками.

Различают списки маркированные и
нумерованные. Для создания списка:

 Выделите элементы, к которым необходимо
добавить маркеры или номера.

 Для добавления
номеров выполните: Формат р Список (вкладка Нумерованный) .
Выберите вид нумерованного списка,
откройте дополнительное диалоговое
окно используя кнопку Изменить… и
установите дополнительные параметры
(начальный номер, формат номера, положение
маркера и текста, продолжающаяся или
новая нумерация).

 Для добавления
маркеров выполните: Формат р Список (вкладка Маркированный).
Выберите вид маркированного списка,
откройте следующее диалоговое окно с
помощью кнопки Изменить… и
установите дополнительные параметры
(шрифт, вид маркера, положение маркера
и текста).

Создание нумерованного или маркированного
списка можно выполнить нажав
кнопки 
 или 
 в Стандартной
панели инструментов.

При нажатии клавиши {Enter}, для
добавления нового абзаца, следующий
номер или маркер вставляется автоматически.

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

Использование стилей.

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

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

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

В этом окне вводят имя создаваемого
стиля, выбирают в списке Основан на
стиле: тип стиля основы. С помощью
элементов блока Форматирование выполняется
набор необходимых установок для нового
стиля (кнопка Формат для выбора
дополнительных параметров).

Создание оглавления.

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

 Расставьте в документе номера страниц
(см. далее).

 Установите курсор на место вставки
оглавления.

 Выполните: Вставка р Ссылка р Оглавление
и указатели р Оглавление.

 В открывшемся диалоговом окне (рис.
5.21) выберите нужный вариант в списке Форматы.

 Установите другие параметры
оглавления.

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

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

Для этого следует установить курсор по
месту перехода к новому листу и
выполнить: Вставка р Разрыв
страниц. В диалоговом окне (рис. 5.22)
необходимо уточнить что необходимо
создавать после разрыва и где его
начинать (тогда нумерация каждого из
разделов может быть независимой).

Для расстановки в документ номеров
страниц следует выполнить: Вставка р Номера
страниц.

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

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

Для удаления номеров страниц
выполнить: Вид р Колонтитулы, выделите
номер страницы и удалите его
(клавиша {Del}, удаление по всему
документу сразу).

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #