Создать группу функций excel - Word и Excel - помощь в работе с программами

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

Пользовательские функции (как и макросы) записываются на языке программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Из этой статьи вы узнаете, как создавать и применять пользовательские функции. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне.

Предположим, что ваша компания предоставляет скидку в размере 10 % клиентам, заказавшим более 100 единиц товара. Ниже мы объясним, как создать функцию для расчета такой скидки.

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

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

Нажмите клавиши ALT+F11 (или FN+ALT+F11 на Mac), чтобы открыть редактор Visual Basic, а затем щелкните Insert (Вставка) > Module (Модуль). В правой части редактора Visual Basic появится окно нового модуля.

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


Function DISCOUNT(quantity, price)
   If quantity >=100 Then
     DISCOUNT = quantity * price * 0.1
   Else
     DISCOUNT = 0
   End If
 
 DISCOUNT = Application.Round(Discount, 2)
End Function

Примечание: Чтобы код было более удобно читать, можно добавлять отступы строк с помощью клавиши TAB. Отступы необязательны и не влияют на выполнение кода. Если добавить отступ, редактор Visual Basic автоматически вставит его и для следующей строки. Чтобы сдвинуть строку на один знак табуляции влево, нажмите SHIFT+TAB.

Теперь вы готовы использовать новую функцию DISCOUNT. Закройте редактор Visual Basic, выделите ячейку G7 и введите следующий код:

=DISCOUNT(D7;E7)

Excel вычислит 10%-ю скидку для 200 единиц по цене 47,50 ₽ и вернет 950,00 ₽.

В первой строке кода VBA функция DISCOUNT(quantity, price) указывает, что функции DISCOUNT требуется два аргумента: quantity (количество) и price (цена). При вызове функции в ячейке листа необходимо указать эти два аргумента. В формуле =DISCOUNT(D7;E7) аргумент quantity имеет значение D7, а аргумент price — значение E7. Если скопировать формулу в ячейки G8:G13, вы получите указанные ниже результаты.

Рассмотрим, как Excel обрабатывает эту функцию. При нажатии клавиши ВВОД Excel ищет имя DISCOUNT в текущей книге и определяет, что это пользовательская функция в модуле VBA. Имена аргументов, заключенные в скобки (quantity и price), представляют собой заполнители для значений, на основе которых вычисляется скидка.

Оператор If в следующем блоке кода проверяет аргумент quantity и сравнивает количество проданных товаров со значением 100:


If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Если количество проданных товаров не меньше 100, VBA выполняет следующую инструкцию, которая перемножает значения quantity и price, а затем умножает результат на 0,1:

Discount = quantity * price * 0.1

Результат хранится в виде переменной Discount. Оператор VBA, который хранит значение в переменной, называется оператором назначения, так как он вычисляет выражение справа от знака равенства и назначает результат имени переменной слева от него. Так как переменная Discount называется так же, как и процедура функции, значение, хранящееся в переменной, возвращается в формулу листа, из которой была вызвана функция DISCOUNT.

Если значение quantity меньше 100, VBA выполняет следующий оператор:

Discount = 0

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

Discount = Application.Round(Discount, 2)

В VBA нет функции округления, но она есть в Excel. Чтобы использовать округление в этом операторе, необходимо указать VBA, что метод (функцию) Round следует искать в объекте Application (Excel). Для этого добавьте слово Application перед словом Round. Используйте этот синтаксис каждый раз, когда нужно получить доступ к функции Excel из модуля VBA.

Пользовательские функции должны начинаться с оператора Function и заканчиваться оператором End Function. Помимо названия функции, оператор Function обычно включает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel доступно несколько встроенных функций (например, СЛЧИС и ТДАТА), в которых нет аргументов.

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

Количество ключевых слов VBA, которые можно использовать в пользовательских функциях, меньше числа, используемого в макросах. Настраиваемые функции не могут выполнять другие задачи, кроме возврата значения в формулу на этом или в выражение, используемом в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окна, редактировать формулу в ячейке, а также изменять шрифт, цвет или узор текста в ячейке. Если в процедуру функции включить такой код действия, функция возвращает #VALUE! ошибку «#ВЫЧИС!».

Единственное действие, которое может выполнять процедура функции (кроме вычислений), — это отображение диалогового окна. Чтобы получить значение от пользователя, выполняющего функцию, можно использовать в ней оператор InputBox. Кроме того, с помощью оператора MsgBox можно выводить сведения для пользователей. Вы также можете использовать настраиваемые диалоговые окна (UserForms), но эта тема выходит за рамки данной статьи.

Даже простые макросы и пользовательские функции может быть сложно понять. Чтобы сделать эту задачу проще, добавьте комментарии с пояснениями. Для этого нужно ввести перед текстом апостроф. Например, ниже показана функция DISCOUNT с комментариями. Благодаря подобным комментариями и вам, и другим будет впоследствии проще работать с кодом VBA. Так, код будет легче понять, если потребуется внести в него изменения.

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

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

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

Для использования настраиваемой функции должна быть открыта книга, содержащая модуль, в котором она была создана. Если книга не открыта, вы получите #NAME? при попытке использования функции. Если вы ссылались на функцию в другой книге, ее имя должно предшествовать названию книги, в которой она находится. Например, при создании функции DISCOUNT в книге Personal.xlsb и вызове ее из другой книги необходимо ввести =personal.xlsb!discount(),а не просто =discount().

Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне «Вставка функции». Пользовательские функции доступны в категории «Определенные пользователем»:

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

Создав нужные функции, выберите Файл > Сохранить как.

В Excel 2007 нажмите кнопку Microsoft Office, а затем щелкните Сохранить как.
В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions, в папке AddIns. Она будет автоматически предложена в диалоговом окне Сохранить как, поэтому вам потребуется только принять расположение, используемое по умолчанию.
Сохранив книгу, выберите Файл > Параметры Excel.

В Excel 2007 нажмите кнопку Microsoft Office и щелкните Параметры Excel.
В диалоговом окне Параметры Excel выберите категорию Надстройки.
В раскрывающемся списке Управление выберите Надстройки Excel. Затем нажмите кнопку Перейти.
В диалоговом окне Надстройки установите флажок рядом с именем книги, как показано ниже.

Создав нужные функции, выберите Файл > Сохранить как.
В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions.
Сохранив книгу, выберите Сервис > Надстройки Excel.
В диалоговом окне Надстройки нажмите кнопку «Обзор», найдите свою надстройку, нажмите кнопку Открыть, а затем установите флажок рядом с надстройкой в поле Доступные надстройки.

После этого пользовательские функции будут доступны при каждом запуске Excel. Если вы хотите добавить его в библиотеку функций, вернимся в Visual Basic редактора. Если вы заглянуть в Visual Basic редактора Project проводника под заголовком VBAProject, вы увидите модуль с именем файла надстройки. У надстройки будет расширение XLAM.

Дважды щелкните модуль в Project Explorer, чтобы вывести код функций. Чтобы добавить новую функцию, установите точку вставки после оператора End Function, который завершает последнюю функцию в окне кода, и начните ввод. Вы можете создать любое количество функций, и они будут всегда доступны в категории «Определенные пользователем» диалогового окна Вставка функции.

Эта статья основана на главе книги Microsoft Office Excel 2007 Inside Out, написанной Марком Доджем (Mark Dodge) и Крейгом Стинсоном (Craig Stinson). В нее были добавлены сведения, относящиеся к более поздним версиям Excel.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Нужна дополнительная помощь?

Источник

Содержание

Работа Мастера функций
- Переход в Мастер функций
- Категории элементов в Мастере
- Выбор функции
- Аргументы функции
- Выполнение функции
Вопросы и ответы

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

Работа Мастера функций

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

Переход в Мастер функций

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

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

Кроме того, нужный нам инструмент можно запустить, перейдя во вкладку «Формулы». Затем следует нажать на самую крайнюю слева кнопку на ленте «Вставить функцию». Она располагается в блоке инструментов «Библиотека функций». Это способ хуже предыдущего тем, что если вы не находитесь во вкладке «Формулы», то придется выполнять дополнительные действия.

Можно также кликнуть на любую другую кнопку блока инструментов «Библиотека функций». При этом, в выпадающем меню появится список, в самом низу которого есть пункт «Вставить функцию…». Вот по нему и нужно кликнуть. Но, данный способ является ещё запутаннее предыдущего.

Очень простым способом перехода в режим Мастера является нажатие комбинации горячих клавиш Shift+F3. Этот вариант предусматривает быстрый переход без дополнительных «телодвижений». Главный недостаток его состоит в том, что далеко не каждый пользователь способен удержать у себя в голове все комбинации горячих клавиш. Так что для новичков в освоении Excel этот вариант не подходит.

Категории элементов в Мастере

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

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

Все функции разделены на следующие 12 категорий:

Текстовые;
Финансовые;
Дата и время;
Ссылки и массивы;
Статистические;
Аналитические;
Работа с базой данных;
Проверка свойств и значений;
Логические;
Инженерные;
Математические;
Определенные пользователем;
Совместимость.

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

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

Выбор функции

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

Аргументы функции

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

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

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

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

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

Выполнение функции

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

Урок: Полезные функции Excel

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

Источник

Как создать пользовательскую функцию?

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

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

Что такое пользовательская функция
Для чего ее используют?
Как создать пользовательскую функцию в VBA?
Как использовать пользовательскую функцию в формуле?
Какие бывают типы пользовательских функций

Что такое пользовательская функция в Excel?

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

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

Как можно решить эти проблемы?

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

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

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

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

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

Существует несколько способов создания собственных функций:

при помощи Visual Basic for Applications (VBA). Этот способ описывается в данной статье.
с использованием замечательной функции LAMBDA, которая появилась в Office365.
при помощи Office Scripts. На момент написания этой статьи они доступны в Excel Online в подписке на Office365.

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

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

А на ввод функции вы потратите всего несколько секунд.

Для чего можно использовать?

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

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

Для чего нельзя использовать пользовательские функции:

Любого изменения другой ячейки, кроме той, в которую она записана,
Изменения имени рабочего листа,
Копирования листов рабочей книги,
Поиска и замены значений,
Изменения форматирования ячейки, шрифта, фона, границ, включения и отключения линий сетки,
Вызова и выполнения макроса VBA, если его выполнение нарушит перечисленные выше ограничения. Если вы используете строку кода, который не может быть выполнен, вы можете получить ошибку RUNTIME ERROR либо просто одну из стандартных ошибок (например, #ЗНАЧЕН!).

Как создать пользовательскую функцию в VBA?

Прежде всего, необходимо открыть редактор Visual Basic (сокращенно — VBE). Обратите внимание, что он открывается в новом окне. Окно Excel при этом не закрывается.

Самый простой способ открыть VBE — использовать комбинацию клавиш. Это быстро и всегда доступно. Нет необходимости настраивать ленту или панель инструментов быстрого доступа. Нажмите Alt + F11 на клавиатуре, чтобы открыть VBE. И снова нажмите Alt + F11, когда редактор открыт, чтобы вернуться назад в окно Excel.

После открытия VBE вам нужно добавить новый модуль. В него вы будете записывать ваш код. Щелкните правой кнопкой мыши на панели проекта VBA слева и выберите «Insert», затем появившемся справа окне — “Module”.

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

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

Пользовательская функция всегда начинается с оператора Function и заканчивается инструкцией End Function.
После оператора Function указывают имя функции. Это название, которое вы создаете и присваиваете, чтобы вы могли идентифицировать и использовать ее позже. Оно не должно содержать пробелов. Если вы хотите разделять слова, используйте подчеркивания. Например, Count_Words.
Кроме того, это имя также не может совпадать с именами стандартных функций Excel. Если вы сделаете это, то всегда будет выполняться стандартная функция.
Имя пользовательской функции не может совпадать с адресами ячеек на листе. Например, имя ABC1234 невозможно присвоить.
Настоятельно рекомендуется давать описательные имена. Тогда вы можете легко выбрать нужное из длинного списка функций. Например, имя CountWords позволяет легко понять, что она делает, и при необходимости применить ее для подсчета слов.
Далее в скобках обычно перечисляют аргументы. Это те данные, с которыми она будет работать. Может быть один или несколько аргументов. Если у вас несколько аргументов, их нужно перечислить через запятую.
После этого обычно объявляются переменные, которые использует пользовательская функция. Указывается тип этих переменных – число, дата, текст, массив.
Если операторы, которые вы используете внутри вашей функции, не используют никакие аргументы (например, NOW (СЕЙЧАС), TODAY (СЕГОДНЯ) или RAND (СЛЧИС)), то вы можете создать функцию без аргументов. Также аргументы не нужны, если вы используете функцию для хранения констант (например, числа Пи).
Затем записывают несколько операторов VBA, которые выполняют вычисления с использованием переданных аргументов.
В конце вы должны вставить оператор, который присваивает итоговое значение переменной с тем же именем, что и имя функции. Это значение возвращается в формулу, из которой была вызвана пользовательская функция.
Записанный вами код может включать комментарии. Они помогут вам не забыть назначение функции и отдельных ее операторов. Если вы в будущем захотите внести какие-то изменения, комментарии будут вам очень полезны. Комментарий всегда начинается с апострофа (‘). Апостроф указывает Excel игнорировать всё, что записано после него, и до конца строки.

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

Для этого в окно модуля вставим этот код:

Function CountWords(NumRange As Range) As Long
Dim rCell As Range, lCount As Long
    For Each rCell In NumRange
        lCount = lCount + _
          Len(WorksheetFunction.Trim(rCell)) - Len(Replace(WorksheetFunction.Trim(rCell), " ", "")) + 1
    Next rCell
CountWords = lCount
End Function

Я думаю, здесь могут потребоваться некоторые пояснения.

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

В начале мы должны записать ее имя: CountWords.

Затем в скобках указываем, какие исходные данные она будет использовать. NumRange As Range означает, что аргументом будет диапазон значений. Сюда нужно передать только один аргумент — диапазон ячеек, в котором будет происходить подсчёт.

As Long указывает, что результат выполнения функции CountWords будет целым числом.

Во второй строке кода мы объявляем переменные.

Оператор Dim объявляет переменные:

rCell — переменная диапазона ячеек, в котором мы будем подсчитывать слова.

lCount — переменная целое число, в которой будет записано число слов.

Цикл For Each… Next предназначен для выполнения вычислений по отношению к каждому элементу из группы элементов (нашего диапазона ячеек). Этот оператор цикла применяется, когда неизвестно количество элементов в группе. Начинаем с первого элемента, затем берем следующий и так повторяем до самого последнего значения. Цикл повторяется столько раз, сколько ячеек имеется во входном диапазоне.

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

Len(WorksheetFunction.Trim(rCell)) — Len(Replace(WorksheetFunction.Trim(rCell), » «, «»)) + 1

Как видите, это обычная формула Excel, которая использует стандартные средства работы с текстом: LEN, TRIM и REPLACE. Это английские названия знакомых нам русскоязычных ДЛСТР, СЖПРОБЕЛЫ и ЗАМЕНИТЬ. Вместо адреса ячейки рабочего листа используем переменную диапазона rCell. То есть, для каждой ячейки диапазона мы последовательно считаем количество слов в ней.

Подсчитанные числа суммируются и сохраняются в переменной lCount:

lCount = lCount + Len(WorksheetFunction.Trim(rCell)) — Len(Replace(WorksheetFunction.Trim(rCell), » «, «»)) + 1

Когда цикл будет завершен, значение переменной присваивается функции.

CountWords = lCount

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

Именно эта строка кода гарантирует, что функция вернет значение lCount обратно в ячейку, из которой она была вызвана.

Закрываем наш код с помощью «End Function».

Как видите, не очень сложно.

Сохраните вашу работу. Для этого просто нажмите кнопку “Save” на ленте VB редактора.

После этого вы можете закрыть окно редактора. Для этого можно использовать комбинацию клавиш Alt+Q. Или просто вернитесь на лист Excel, нажав Alt+F11.

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

Как использовать пользовательскую функцию в формуле?

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

Чтобы использовать ее, у вас есть две возможности.

Первый способ. Нажмите кнопку fx в строке формул. Среди появившихся категорий вы увидите новую группу — Определённые пользователем. И внутри этой категории вы можете увидеть нашу новую пользовательскую функцию CountWords.

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

Можно посчитать этой же функцией и количество слов в диапазоне. Запишите в ячейку С3:

=CountWords(A2:A5)

Нажмите Enter.

Мы только что указали функцию и установили диапазон, и вот результат подсчета: 14 слов.

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

Как видите, результаты одинаковы. Только использовать CountWords() гораздо проще и быстрее.

Различные типы пользовательских функций с использованием VBA.

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

Без аргументов.

В Excel есть несколько стандартных функций, которые не требуют аргументов (например, СЛЧИС , СЕГОДНЯ , СЕЧАС). Например, СЛЧИС возвращает случайное число от 0 до 1. СЕГОДНЯ вернет текущую дату. Вам не нужно передавать им какие-либо значения.

Вы можете создать такую функцию и в VBA.

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

Function SheetName() as String
    Application.Volatile
    SheetName = Application.Caller.Worksheet.Name
End Function

Или же можно использовать такой код:

SheetName = ActiveSheet.Name

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

Приведенный выше код определяет результат функции как тип данных String (поскольку желаемый результат — это имя файла, которое является текстом). Если вы не укажете тип данных, то Excel будет определять его самостоятельно.

С одним аргументом.

Создадим простую функцию, которая работает с одним аргументом, то есть с одной ячейкой. Наша задача – извлечь из текстовой строки последнее слово.

Function ReturnLastWord(The_Text As String)
Dim stLastWord As String
'Extracts the LAST word from a text string
    stLastWord = StrReverse(The_Text)
    stLastWord = Left(stLastWord, InStr(1, stLastWord, " ", vbTextCompare))
    ReturnLastWord = StrReverse(Trim(stLastWord))
End Function

Аргумент The_Text — это значение выбранной ячейки. Указываем, что это должно быть текстовое значение (As String).

Оператор StrReverse возвращает текст с обратным порядком следования знаков. Далее InStr определяет позицию первого пробела. При помощи Left получаем все знаки заканчивая первым пробелом. Затем удаляем пробелы при помощи Trim. Вновь меняем порядок следования символов при помощи StrReverse. Получаем последнее слово из текста.

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

Использование массива в качестве аргумента.

Многие функции Excel используют массивы значений как аргументы. Вспомните функции СУММ, СУММЕСЛИ, СУММПРОИЗВ.

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

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

Function SumEven(NumRange as Range)
 Dim RngCell As Range
 For Each RngCell In NumRange
 If IsNumeric(RngCell.Value) Then
 If RngCell.Value Mod 2 = 0 Then
 Result = Result + RngCell.Value
 End If
 End If
 Next RngCell
 SumEven = Result
 End Function

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

Function SumEven(NumRange as Variant)

Тип Variant обеспечивает «безразмерный» контейнер для хранения данных. Такая переменная может хранить данные любого из допустимых в VBA типов, включая числовые значения, текст, даты и массивы. Более того, одна и та же такая переменная в одной и той же программе в разные моменты может хранить данные различных типов. Excel самостоятельно будет определять, какие данные передаются в функцию.

В коде есть цикл For Each … Next, который берет каждую ячейку и проверяет, есть ли в ней число. Если это не так, то ничего не происходит, и он переходит к следующей ячейке. Если найдено число, он проверяет, четное оно или нет (с помощью функции MOD).

Все чётные числа суммируются в переменной Result.

Когда цикл будет закончен, значение Result присваивается переменной SumEven и передаётся функции.

С несколькими аргументами.

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

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

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

Function GetMaxBetween(rngCells As Range, MinNum, MaxNum)
Dim NumRange As Range
Dim vMax
Dim arrNums()
Dim i As Integer
ReDim arrNums(rngCells.Count)
    For Each NumRange In rngCells
     vMax = NumRange
        Select Case vMax
           Case MinNum + 0.01 To MaxNum - 0.01
              arrNums(i) = vMax
              i = i + 1
           Case Else
               GetMaxBetween = 0
           End Select
    Next NumRange
    GetMaxBetween = WorksheetFunction.Max(arrNums)
End Function

Здесь мы используем три аргумента. Первый из них — rngCells As Range. Это диапазон ячеек, в которых нужно искать максимальное значение. Второй и третий аргумент (MinNum, MaxNum) указаны без объявления типа. Это означает, что по умолчанию к ним будет применён тип данных Variant. В VBA используется 6 различных числовых типов данных. Указывать только один из них — это значит ограничить применение функции. Поэтому более целесообразно, если Excel сам определит тип числовых данных.

Цикл For Each … Next последовательно просматривает все значения в выбранном диапазоне. Числа, которые находятся в интервале от максимального до минимального значения, записываются в специальный массив arrNums. При помощи стандартного оператора MAX в этом массиве находим наибольшее число.

С обязательными и необязательными аргументами.

Чтобы понять, что такое необязательный аргумент, вспомните функцию ВПР (VLOOKUP). Её четвертый аргумент [range_lookup] является необязательным. Если вы не укажете один из обязательных аргументов, получите ошибку. Но если вы пропустите необязательный аргумент, всё будет работать.

Но необязательные аргументы не бесполезны. Они позволяют вам выбирать вариант расчётов.

Например, в функции ВПР, если вы не укажете четвертый аргумент, будет выполнен приблизительный поиск. Если вы укажете его как ЛОЖЬ (или 0), то будет найдено точное совпадение.

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

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

Теперь давайте посмотрим, как создать функцию в VBA с необязательными аргументами.

Function GetText(textCell As Range, Optional CaseText = False) As String
Dim StringLength As Integer
Dim Result As String
StringLength = Len(textCell)
For i = 1 To StringLength
If Not (IsNumeric(Mid(textCell, i, 1))) Then Result = Result & Mid(textCell, i, 1)
Next i
If CaseText = True Then Result = UCase(Result)
GetText = Result
End Function

Этот код извлекает текст из ячейки. Optional CaseText = False означает, что аргумент CaseText необязательный. По умолчанию его значение установлено FALSE.

Если необязательный аргумент CaseText имеет значение TRUE, то возвращается результат в верхнем регистре. Если необязательный аргумент FALSE или опущен, результат остается как есть, без изменения регистра символов.

Думаю, что у вас возник вопрос: «Могут ли в пользовательской функции быть только необязательные аргументы?». Ответ смотрите ниже.

Только с необязательным аргументом.

Насколько мне известно, нет встроенной функции Excel, которая имеет только необязательные аргументы. Здесь я могу ошибаться, но я не могу припомнить ни одной такой.

Но при создании пользовательской такое возможно.

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

Function UserName(Optional Uppercase As Variant)
    If IsMissing(Uppercase) Then Uppercase = False
    UserName = Application.UserName
    If Uppercase Then UserName = UCase(UserName)
End Function

Как видите, здесь есть только один аргумент Uppercase, и он не обязательный.

Если аргумент равен FALSE или опущен, то имя пользователя возвращается без каких-либо изменений. Если же аргумент TRUE, то имя возвращается в символах верхнего регистра (с помощью VBA-оператора Ucase). Обратите внимание на вторую строку кода. Она содержит VBA-функцию IsMissing, которая определяет наличие аргумента. Если аргумент отсутствует, оператор присваивает переменной Uppercase значение FALSE.

Можно предложить и другой вариант этой функции.

Function UserName(Optional Uppercase As Variant)
    If IsMissing(Uppercase) Then Uppercase = False
    UserName = Application.UserName
    If Uppercase Then UserName = UCase(UserName)
End Function

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

Возвращаемое значение — массив.

В VBA имеется весьма полезная функция — Array. Она возвращает значение с типом данных Variant, которое представляет собой массив (т.е. несколько значений).

Пользовательские функции, которые возвращают массив, весьма полезны при хранении массивов значений. Например, Months() вернёт массив названий месяцев:

Function Months() As Variant
Months = Array("Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", _
"Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь")
End Function

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

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

А если необходим вертикальный массив значений?

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

Используем Months() как аргумент функции ТРАНСП:

=ТРАНСП(Months())

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

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

=ИНДЕКС(Months();1;A1)

Альтернативный вариант этой формулы:

=ИНДЕКС( {«Январь»; «Февраль»; «Март»; «Апрель»; «Май»; «Июнь»; «Июль»; «Август»; «Сентябрь»; «Октябрь»; «Ноябрь»; «Декабрь»};1;A1)

Согласитесь, написанная нами функция делает формулу Excel значительно проще.

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

Сумма по цвету и подсчёт по цвету в Excel — В этой статье вы узнаете, как посчитать ячейки по цвету и получить сумму по цвету ячеек в Excel. Эти решения работают как для окрашенных вручную, так и с условным форматированием. Если…

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

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

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

4 способа отладки пользовательской функции — Как правильно создавать пользовательские функции и где нужно размещать их код, мы подробно рассмотрели ранее в этой статье. Чтобы решить проблемы при создании пользовательской функции, вам скорее всего придется выполнить…

Источник

Группировка данных в Microsoft Excel

Смотрите также 3 ‘количество уровней нажмите как показано в сводной таблицы и Вашей рабочей книгеЕсли у Вас есть выступит воскресенье –OrderID Это может быть промежуточные итоги. ВКоманда подразделам. уровни. Цифра 2ExcelТеперь выделенные группы будут хотим сгруппировать –При работе с таблицами, Set ws =

Обновить сводной таблице ниже.

Настройка группировки

нажмите Excel, кликните только пустые ячейки в день недели, соответствующийпо десять последовательно сделано с помощью нашем случае данные

Промежуточный итогКак удалить группировку в – разворачивает средний
. расформированы, а структура строки или столбцы. в которые входит ActiveSheet ws.UsedRange.ClearOutline ‘убираем(Refresh). Данные в
Предположим, мы создали своднуюFinish что добавленную кнопку тех полях, где дате, указанной в пронумерованных заказов. группировки данных, и сгруппированы по размерупозволяет автоматически создаватьExcel уровень.Группируем вручную отдельные листа примет свой Ставим переключатель в большое количество строк все группировки на сводной таблице будут таблицу (как на(Готово).PivotTable and PivotChart Wizard должны быть даты параметреИтак, мы настроили значение цель этой статьи футболок, а количество группы и использовать.

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

Группировка по строкам

позицию или столбцов, актуальным

листе LastRow = обновлены, и теперь картинке ниже), котораяВ(Мастер сводных таблиц или числа, тоStarting AtStarting At – показать Вам, заказанных футболок для
базовые функции, такиеЧтобыКак группировать вСначала сгруппируем подразделКак видим, создать группу«Строки»
становится вопрос структурирования WorksheetFunction.Match(«Конец», ws.Columns(FIRST_COLUMN), 0) группировка строк или группирует список изМастере сводных таблиц и и диаграмм), выберите
это тоже станет(Начиная с).(Начиная с), оставили как это делается. каждого размера указано как СУММ, СЧЁТубрать структуру таблицыExcel сразу все строки. «Связь». Выделяем строки столбцов или строки жмем на данных. В Экселе

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

причиной сообщения обНе всегда набор записей значение

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

Группируем по значению под соответствующей группой.

и СРЗНАЧ, чтобыExcelМожно сгруппировать сразу 8, 9, 10 довольно просто. В
кнопку этого можно достичь строки ‘проходим во доступна. возрасту. Группы делятсяВы можете связатьMultiple Consolidation Ranges
ошибке. Ошибка появится можно разделить поEnding AtГруппируем по датеПри подведении промежуточных итогов упростить подведение итогов., выделяем ее и

все строки. Выделяем – перечень данных то же время,«OK» путем использования группировки вложенном цикле поУрок подготовлен для Вас

Создание вложенных групп

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

5 до 16 как показано на консолидации) и случае, если вместо же лёгкостью, как предложил Excel, иИсправляем ошибки лист разбивается наПромежуточный итог кнопки «Разгруппировать» Выбираем на стрелку у

Разгруппирование

«Данные» в разделе процедуры пользователь можетНа этом создание группы инструмент позволяет не

For level =Источник: http://www.excelfunctions.net/group-pivot-table.html лет. рисунке выше –PivotTable даты или числа последовательность чисел или оставили значениеДетализация групп
различные уровни. Выспособна вычислить стоимость функцию «Удалить структуру». кнопки функции «Группировать» «Структура» нажимаем на значительно облегчить себе завершено. Для того, только удобно структурировать

1 To NUMBER_OF_LEVELSПеревел: Антон АндроновЕсли нужно пойти ещеQuarters

(Сводная таблица), а в ячейке содержится дат. Иногда группаByГруппировка и несколько диапазонов можете переключаться между канцтоваров по группам Удалятся все группировки. и выбираем функцию кнопку «Группировать». Получилось работу с таблицей, чтобы свернуть её данные, но и start = 0Автор: Антон Андронов дальше и объединить

(Кварталы) и

lumpics.ru

Группировать в Excel.

затем нажмите текстовое значение. В  становится группой просто(С шагом) равным консолидации этими уровнями, чтобы в большом заказе.Чтобы удалить конкретную «Создать структуру». Excel так.
 особенно если она  достаточно нажать на временно спрятать ненужные For i =Если у вас имеется возрастные группы вSeasonsNext каждом случае, проверьте потому, что Вы
10Представьте себе такую ситуацию: иметь возможность регулировать Команда создаст иерархию группировку, например, одного проанализирует формулы и
В левой стороне таблицы сильно большая. В знак «минус». элементы, что позволяет FIRST_ROW To LastRow большая и сложная
категории 5-8 лет,(Сезоны).(Далее). Выберите
исходные данные и так решили. В, поскольку оно нам Вы анализируете подборку количество отображаемой информации, групп, также называемую раздела по строкам, создаст структуру. появилась кнопка со этом случае такжеЧтобы заново развернуть группу, сконцентрировать своё внимания ‘если нашли начало таблица на небольшом 9-12 лет иСводная таблица будет сформированаI will create the исправьте ошибку, а этом случае можно подходит. Вы можете счетов, собранных в используя иконки структуры структурой, чтобы упорядочить выделяем эти строки,Внимание! знаком «Минус» - может помочь создание нужно нажать на
на других частях группы — запоминаем мониторе (например, ноутбука), 13-16 лет, то автоматически, группы будут page fields затем обновите сводную
 создать свою собственную делать группы меньше
 сводной таблице. Вы  1, 2, 3 информацию на листе.
нажимаем на кнопкуЕсли выйдет диалоговое
значить подраздел раскрыт. вложенных групп. Провести знак «плюс». таблицы. Давайте выясним, номер строки If то в процессе можно поступить вот созданы как поля(Создать поля страницы) таблицу и попытайтесь группировку. Откройте лист или больше, изменяя можете обобщать счета, в левой частиВаши данные должны быть «Разгруппировать». окно, что Excel
Если нажмем на разгруппирование так жеАналогичным образом проводится и как произвести группировку ws.Cells(i, level+FIRST_COLUMN-1) <> работы с ней
так:Report Filter и снова нажмите снова. со сводной таблицей, это значение – объединяя их в
листа. В следующем
правильно отсортированы передМожно группировать данные не может создать эту кнопку, то просто, как и группировка по столбцам. в Экселе. «» And _ часто возникает желаниеКликните правой кнопкой мыши(Фильтры) в спискеNextОдним из преимуществ, которые кликните по первому например: 5, 20, группы по 5 примере мы переключимся использованием команды по другому принципу, структуру, значит в подраздел свернется и сгруппировать данные.Справа или слева отСкачать последнюю версию WorksheetFunction.CountA(ws.Cells(i + 1, скрыть часть ненужных по левому столбцу полей сводной таблицы,
(Далее). Теперь Вам  даёт группировка данных, элементу данных, который
50 или 100. – 10 или между всеми тремяПромежуточный итог с шагом, по таблице нет формул. получится так.
Автор: Максим Тютюшев группируемых данных добавляем Excel FIRST_COLUMN).Resize(1, level)) = в текущий момент
сводной таблицы (столбец но Вы, если
нужно указать каждый является то, что нужно включить в Теперь жмём даже 100 счетов. уровнями структуры., Вы можете изучить
периодам, по датам.Тогда создаем структуруКазалось бы, все хорошо,Можно новый столбец иПрежде чем перейти к 0 Then start данных (строк или с возрастами) и пожелаете, можете перетащить
 из диапазонов. Зайдите
 Вы можете извлечь  группу и далее,
ОК Давайте рассмотрим пример,Хоть в этом примере серию уроков Сортировка Подробнее, смотрите в функцией «Консолидация». Подробнее но смущает тотгруппировать данные указываем в нём
 группировке строк или
= i ‘если столбцов). Основные способы выберите команду эти поля в на первый лист,
подмножество сгруппированных данных с нажатой клавишей. Данные будут сгруппированы где идентификатор строки представлено всего три данных в Excel, статье «Как группировать
смотрите в статье факт, что этаExcel  соответствующее наименование группы. столбцов, нужно настроить нашли конец группы для решения этойГруппировать область выберите все данные, на новый лист.
Ctrl по значениюOrderID
уровня, Excel позволяет для получения дополнительной в Excel» тут. «Как сложить и кнопка расположена нев разделы, подразделы,
Выделяем ячейки в столбцах,  этот инструмент так, — группируем If проблемы — скрытие
(Group). Появится диалоговоеRow Labels включая заголовки таблицы,
 Чтобы сделать это,, последовательно щелкните по
OrderID – это числовое
создавать до 8 информации.Группировку можно применить удалить дубли в напротив слова «Связь». т.д. Эти разделы,
которые собираемся сгруппировать, чтобы конечный результат WorksheetFunction.CountA(ws.Cells(i + 1, строк и группировка. окно(Строки) или и нажмите выберите группу, и  всем элементам, которыев более удобные
поле. В данный уровней вложенности.В следующем примере мы в большом прайсе. Excel» (ссылка на Поправим это так. строки, столбцы таблицы кроме столбца с был близок к FIRST_COLUMN).Resize(1, level)) >
Вот о последнемГруппированиеColumn LabelsAdd дважды щелкните по также нужно включить
для обработки группы. момент каждому счётуЩелкните нижний уровень, чтобы воспользуемся командой
 Как его сделать,  статью выше).На закладке «Данные»
можно сворачивать. Рассмотрим, наименованием. Кликаем на  ожиданиям пользователя. 0 And start способе и пойдет(Grouping) для чисел.Excel(Колонны).(Добавить). Повторите шаги,
ячейке с интересующим в эту группу.Вот так выглядят данные, соответствует отдельная строка, отобразить минимальное количествоПромежуточный итог
читайте в статьеМожно и без в разделе «Структура» как свернуть таблицу кнопкуПереходим во вкладку > 0 Then
речь далее. автоматически заполнит поляНа рисунке выше показано: чтобы добавить информацию Вас итоговым значением. Затем на вкладке
сгруппированные по и результат получается информации. Мы выберем, чтобы определить сколько «Как сделать прайс-лист формул нажимаем на стрелочку в Excel по«Группировать»«Данные»

excel-office.ru

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

ws.Rows(start + 1Предположим, что мы работаемНачиная с мы переместили страницу со следующего листа Данные, из которыхPivotTable / OptionsOrderID довольно громоздким. Мы уровень 1, который заказано футболок каждого в Excel» здесь.группировать данные в правом нижнем разделам. названиям, т.д.... & «:» & вот с такой(Starting At) и

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

Page 2 и так далее, было собрано это(Работа со сводными. Как видите, они можем сгруппировать эти
содержит только общее размера (S, M,В Excel можноExcel углу раздела (обведенаГруппировать в
В открывшемся окошке наВ нижнем левом углу i).Group start = сложной многоуровневой таблицейПо

(Сезоны) из области пока не добавите итоговое значение, будут таблицами / Анализ) значительно более обобщены, данные, чтобы упростить количество заказанных футболок.

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

L и XL). посчитать данные, выбравфункцией группировки, но, на изображении красным
Excel этот раз ставим блока инструментов 0 End If с данными:

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

(Ending At) минимальнымReport Filter данные со всех извлечены на новый нажмите чем ранее. таблицу.Щелкните следующий уровень, чтобы В результате рабочий их из таблицы чтобы между разделами цветом).можно несколькими способами переключатель в позицию«Структура» Next i NextОбратите внимание на то,

и максимальным значениями(Фильтры) в область листов, которые планировали лист текущей рабочейGroup SelectionЕсли данные, с которымиВ этой сводной таблице отобразить более подробную лист Excel обретет

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

по конкретномым нескольким в таблице былиПоявится такое диалоговое окно. – сгруппировать отдельные«Столбцы»на ленте расположена level End Sub что в таблице из наших исходныхRow Labels использовать в сводной книги Excel.(Группировка по выделенному) Вы работаете, являются каждый идентификатор

информацию. В нашем структуру в виде условиям из любого строки, которые неВ нем убираем галочки разделы, все строки,. Жмем на кнопку маленькая наклонная стрелочка.При необходимости, текст можно ясно и четко данных (в нашем(Строки). таблице.
Двойной щелчок мышью по и элементы будут датами, то ВыOrderID примере мы выберем
групп по каждому столбца, строки, т.д. входят в разделы. у функций «Итоги или строки и«OK» Кликаем по ней. слегка подкорректировать под прослеживаются вложенные друг примере это 5Группируете ли Вы данные
Мастер сводных таблиц и любому значению в добавлены в новую сможете использовать похожийпредставлен в отдельной уровень 2, который размеру футболок, а Читайте об этом Получится всего один в строках под
столбцы. Рассмотрим, как.Открывается окно настройки группировки. ваши особенности, а в друга уровни и 16). в уже готовой диаграмм
сводной таблице создаёт группу. Таким же приём. Чтобы сгруппировать строке. Такие данные
содержит все строки затем будет подсчитано статью «Суммирование ячеек уровень. данными» — это группировать таблицу сГруппа готова. Аналогично, как Как видим по именно изменить: данных. Такую структуру

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

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

FIRST_ROW часто можно увидеть группы в категории Вы делаете это несколько диапазонов в

все данные, относящиеся выделить другие элементы кликните правой кнопкой но нам этого скрывает остальные данные в каждой группе.
условию». таблице разделы закрашены у слов «Итого без формул. столбцов, её можно итоги и наименования- номер первой в: по 4 года,
вместе с созданием одной сводной таблице. к этому значению. и создать еще мыши по дате

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

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

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

в столбце илиДля этого кликните правойЩелкните наивысший уровень, чтобы для которых требуется объем информации, иногда
Между разделами есть строки, от данных», чтобы таблицы с помощью нажимая на знаки справа от них,
с которой пойдет группируются в блоки

С шагом нескольких диапазонов –Мастере сводных таблиц и в сводной таблице,Выделив несколько полей, Вы строке Вашей сводной кнопкой мыши по развернуть все данные

подвести итог. В

office-guru.ru

Как группировать данные в сводной таблице Excel для их дальнейшего обобщения и анализа

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

а по строкам группировка. Если у (cost centers) или(By) вводим значение результат будет одинаковый, диаграмм которая собрана из можете объединить их таблицы и выберите одному из на листе. В этом примере мы

и даже трудночитаемыми.
разделы (расходы, командировки).
Нажимаем «ОК». Получилось
, смотрите в статье
соответственно.
– внизу. Многих вас шапка не

Группируем по значению

по географическому признаку 4. Жмём и это станетпервым делом нужно нескольких консолидированных диапазонов, в группы так,GroupOrderID нашем случае это подводим промежуточный итог Excel позволяет группировать В свернутом виде так. «Как сложить иВ Эксель можно создавать пользователей это не из одной строки (страна-край-город)ОК ещё одним полезным выбрать, какие диапазоны

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

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

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

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

подзадачи и действия 5-8 лет и таблицах Excel. в сводную таблицу 2007 и 2010 подборки, с которыми(Секунды),(Группировать). Так какили по столбцу же Вы можете сразу подраздел «Связь». Выделяем « развернутом состоянии материнской галочку с соответствующего- номер первогостроительных сметах — похожим далее с шагомУрок подготовлен для Вас – вероятнее всего, на Панель быстрого удобно работать. ТакMinutesOrderID

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

Группируем по дате

группы выделить в пункта. В общем, столбца списка, с образом обычно расписываются по 4 года. командой сайта office-guru.ru Вам понадобится одно доступа необходимо добавить как по умолчанию(Минуты),это числовое поле,, чтобы скрыть илиот меньшего к при помощи командыТак же, как с сгруппированными и» тут. ней определенные ячейки, каждый пользователь может которого начинается анализ расчеты расхода материалов В итоге получитсяИсточник: https://www.ablebits.com/office-addins-blog/2011/10/06/excel-group-pivottable-data/ или два. Теперь команду группы названы именамиHours то диалоговое окно отобразить группы. большему.Промежуточный итог и строки, предварительно развернутыми строками подразделов.Здесь рассмотрим, как которые вы собираетесь настроить данные параметры и группировка. Если и их стоимости вот такая таблица:Перевел: Антон Андронов выберите один изPivotTable and PivotChart WizardGroup 1(Часы), уже будет подготовленоСо временем необходимость вОткройте вкладкуи придать структуру выделив все столбцы. Мы выделили строки групприровать отдельные строки, сгруппировать отдельно. Затем под себя. Кроме слева от вашей при строительствеЧтобы разгруппировать значения вАвтор: Антон Андронов

диапазонов в поле(Мастер сводных таблиц(Группа 1) иDays для численной группировки промежуточных итогах пропадает,Данные рабочему листу Excel.Третий способ раздела «Расход» -

Группируем данные вручную

столбцы в таблице. следует провести одну того, тут же таблицы есть ещеи т.д. — дальше сводной таблице, нужно:Часто возникает необходимость выполнить немного выше и и диаграмм).Group 2(Дни), с полями особенно, когда требуется, затем нажмите команду В этом уроке. 6, 7, 8,Например, у нас из тех процедур, можно включить автоматические колонки, то эту придумайте сами.Кликнуть правой кнопкой мыши группировку в сводной введите для негоЧтобы сделать это, кликните(Группа 2), ВамMonthsStarting At иначе перегруппировать данныеПромежуточный итог мы рассмотрим обаФункция «Группировать» в 9, 10. Нажимаем большая таблица с какие были описаны стили, установив галочку

 константу также нужноДелать такую группировку вручную по левому столбцу таблице по заголовкам

имя группы. Затем выпадающее меню Панели придётся самостоятельно переименовать(Месяцы),(Начиная с), на листе Excel.. этих инструмента максимальноExcel. кнопку «Группировать. Получится данными. В этой выше, в зависимости около данного наименования. изменить. весьма мучительно, вследствие сводной таблицы (столбец, строки или столбца. выберите следующий диапазон

 быстрого доступа, выберите их. Для этогоQuartersEnding At

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

Исправляем ошибки

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

средств группировки самогоВ появившемся меню нажать Excel может сделать него те жеMore Commands имя группы, иYearsBy промежуточные итоги, их. Из раскрывающегося спискаВыделите строки или столбцы, в определенных строках другие разделы в эти формулы и, или со строками. по кнопке (столбцов) для анализа. Excel. Поэтому, столкнувшисьРазгруппировать

Детализация групп

это автоматически (в самые шаги. И(Другие команды), в в строке формул(Годы), и установить(С шагом). В можно удалить. в поле которые необходимо сгруппировать. или столбцах, чтобы строках. на основе анализаПосле этого вложенная группа«OK» В приведенном выше как-то с подобной(Ungroup).

том числе для так далее для открывшемся диалоговом окне введите более подходящее начальный и конечный данной ситуации Excel

Группировка и несколько диапазонов консолидации

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

момент времени. Для предлагает Вам группироватьДанные, выберите столбец, который мы выделим столбцы группировки.Excel В таблице должны создавать неограниченное количествоНа этом настройка параметров проанализировать три первых из своих проектов, сводной таблице: Далее это продемонстрировано имя группы ужеAll CommandsПосле того, как группы группировки по годам, элементы по 10, затем нажмите команду

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

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

нашем случае это C. структуры по всейЧтобы сгруппировать столбцы, строк. Смотрите статью между ними легко завершена. этой константы =3 для автоматической группировки в группуПредположим, мы создали сводную не вводить его, в списке команд переименовывать их, как интервал зафиксирован равнымПо умолчанию Excel предлагает. столбецОткройте вкладку таблице, сгруппировать сразу выделяем эти столбцы «Как сделать таблицу проводить, перемещаясь поВыполним группировку данных поВажно! подобных списков, которым(Cannot group that таблицу (как на а выбрать из найдите угодно.1 в качестве значенияОткроется диалоговое окноРазмерДанные и все строки, и группируем как

 в Excel». цифрам, расположенным слева строкам.Макрос предполагает, что: и хочу тут

selection). картинке ниже), которая выпадающего списка.PivotTable and PivotChart WizardВы можете развернуть или, а для группировки

Starting AtПромежуточные итоги.на Ленте, затем и все столбцы, обычно.В столбце А есть или сверху листаДобавляем строчку над группойУровни заполняются по порядку, поделиться. Чтобы применитьИногда при попытке выполнить показывает данные продажТак, например, диапазон, содержащий(Мастер сводных таблиц свернуть группу, нажав по дням Вы(Начиная с) первый. НажмитеНажмите на кнопку со нажмите команду то можно воспользоваться

Здесь мы выделили столбцы три раздела – в зависимости от столбцов или под т.е., например, уровень его к вашему группировку в сводной для каждого дня даты, может быть и диаграмм). Нажмите символы можете установить собственный элементУбрать все стрелкой в полеГруппировать автоматической группировкой, т.е. D, E, F. «Доход», «Расход», «Прибыль». того, что сгруппировано ней, в зависимости 3 не может списку, проделайте следующие таблице оказывается, что первого квартала 2016 частью группы, которая кнопкуплюс интервал, например, равный

OrderID.Операция.свернуть таблицу Нажали кнопку «Группировать». Второй уровень – строки или столбцы. от того, как быть написан, если действия:

команда года. разделяет месяцы поAddили7: 10248. Тем неВсе данные будут разгруппированы,, чтобы выбрать типВыделенные строки или столбцыExcelМожно группировать данные это то, чтоЕсли вы хотите переформатировать

планируем выводить наименование ему не предшествовалНажмите сочетание клавишГруппироватьЕсли нужно выполнить группировку кварталам, а также(Добавить), чтобы добавитьминус

дням, чтобы сгруппировать менее, Вы можете а итоги удалены. используемой функции. Мы будут сгруппированы. В. в таблице таким входит в «Доход», или просто удалить и итоги. В уровень 2.ALT+F11(Group) в меню данных о продажах

частью группы, которая кнопку на Панель
(+/-) слева от
данные по неделям.

настроить точнее или

office-guru.ru

Группировка в сводной таблице Excel

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

Пример 1: Группировка в сводной таблице по дате

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

Количество столбцы A, B Можно нажать на уровней вложения одних расшифровка пункта «Связь»

нужно будет разгруппировать. произвольное наименование группы, в последней строке Visual Basic. В появляется окно с это можно сделать сезонам:Теперь Вы можете создатьИногда, пытаясь сгруппировать выделенныеDays
установив собственное значение воспользуйтесь пунктом, чтобы подсчитать количество и C. верхний левый уголок, уровней в другие. — это третийВыделяем ячейки столбцов или

Пример 2: Группировка в сводной таблице по диапазону

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

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

по контексту.Конец меню командуВыделенные объекты нельзя объединитьКликните правой кнопкой мыши(Зима), нескольких диапазонов Excel сообщение об ошибке, группировки, установите количествоStarting Atиз выпадающего меню каждого размера. Excel, выделите сгруппированные таблицу. Как быстро таблицы получился такой. уровня информации. По разгруппированию. Жмем наВыделяем строки, которые нужно, которое необходимо, чтобыInsert — Module
в группу по левому столбцуSpring с единообразной компоновкой уведомляющее, что выбранные дней равным(Начиная с). Чтобы командыВ поле строки или столбцы, выделить строки, таблицу,Плюсиками у строк и столбцам тоже самое, кнопку сгруппировать, кроме итоговой макрос понял, где

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

, чтобы вставить новый(Cannot group that

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

Распространённые ошибки при группировке в сводной таблице

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

строки. Переходим во заканчивается список и модуль и скопируйте selection). Это происходит с датами) иSummer данные с одинаковым быть объединены в, укажите для параметра10240.выберите столбец, в команду статье «Как выделить раздел. А цифрами год, квартал и, расположенную на ленте вкладку пора остановиться: туда текст макроса: чаще всего потому, выберите команду

(Лето) и количеством столбцов, одинаковыми группу. Есть целыйStarting At, введите это значениеАвтор: Антон Андронов который необходимо вывестиРазгруппировать в Excel ячейки, в верхнем левом месяцы.

в блоке настроек«Данные»
Чтобы запустить добавленный макрос
Sub Multilevel_Group() Dim

что в столбце

office-guru.ru

Многоуровневая группировка строк

ГруппироватьFall заголовками столбцов, при ряд причин, почему(Начиная с) дату, в полеHelen Bradley рассказывает, как итог. В нашем. таблицу, др.». углу таблицы разворачиваемВсе эти данные«Структура». для списка на level As Single, данных в исходной

(Group). Появится диалоговое(Осень). Группировки могут этом каждая таблица это может произойти.

которая выпадает наStarting At группировать данные в примере это столбецЧтобы скрыть группу вНа закладке «Данные» или сворачиваем все мы можем свернуть

.На ленте в блоке текущем листе, нажмите i As Single таблице содержатся нечисловые
окно быть по любому может иметь разное Одна из причин:
начало недели, и(Начиная с) – сводной таблице ExcelРазмер Excel, нажмите иконку
в разделе «Структура» уровни или разделы.

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

Например, чтобы развернут маленькая таблица. Но, что именно нам«Структура»ALT+F8 Single, LastRow As Чтобы исправить это,(Grouping) для дат. посчитаете подходящим дляЭта команда исключена из

создать собственную группировку,ОК любое стартовое значение, обобщения и анализа.Если все параметры заданы(минус). у кнопки «Группировать» все уровни строк, можно развернуть любой нужно разъединить: строкикликаем по кнопке, выберите в списке Single Const FIRST_ROW нужно вместо нечисловыхВыберите своих данных. Нажмите меню Excel 2010, то должны выделить. Информация будет сгруппирована даже такое, которогоРаботая в Excel со правильно, нажмитеГруппа будет скрыта. Чтобы и вбираем функцию нажимаем на цифру раздел, подраздел и или столбцы. После«Группировать» наш макрос = 2 ‘первая значений вставить числаМесяцыNext но Вы можете два или более по неделям. нет в данных. сводной таблицей, ВыОК показать скрытую группу, «Создать структуру». 3 (последнюю в просмотреть информацию детально. этого, жмем на.Multilevel_Group строка списка Const

или даты.(Month) и нажмите(Далее), кликните по добавить её на

 элемента для созданияБольшой объём данных будетМы настраиваем параметры группировки можете прийти к. нажмите иконкуExcel создаст сразу списке, если уровнейПервый способ кнопку
Открывается небольшое окно, ви нажмите кнопку FIRST_COLUMN = 1После этого кликните правойОК ячейке, в которой Панель быстрого доступа группы – нельзя сгруппирован по неделям данных – они
 выводу, что многиеИнформация на листе будетПоказать детали все структуры по много). Цифра 1.«OK» котором нужно дать

Выполнить (Run) ‘первый столбец списка

кнопкой мыши по. Данные таблицы будут должен будет находиться самостоятельно. создать группу из (по 7 дней),
будут сгруппированы по данные должны быть сгруппирована, а под(плюс). столбцам, строкам, разделам, – сворачивает всеГруппировать строки в.

ответ, что мы. Const NUMBER_OF_LEVELS = сводной таблице и сгруппированы по месяцам, верхний левый уголСоздайте новый лист в одной записи. и первым днем полю обобщены ещё больше.

planetaexcel.ru

каждой группой появятся

Источник

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

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

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

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

Как настроить параметры функции

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

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

Важно: на практике многим людям кажется довольно неудобным отображение итогов под данными. Поэтому можно эту галочку не ставить. Также рекомендуется поставить флажок «Автоматические стили».

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

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

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

Создать новую строку над или под теми, которые мы хотим группировать. Здесь все зависит от того, какой способ отображения итогов был выбран на предыдущем этапе.
Следующий шаг – создание названия таблицы в левой верхней ячейке добавленной строки. Это будет название группы, в которую будут объединяться ячейки, общие по определенному признаку.
Выделить все ячейки в области под новосозданной строкой или над ней (в зависимости от того, что мы сделали на первом этапе). После этого ищем кнопку «Структура» на вкладке с данными и там находим опцию «Группировать». Важно при этом не нажимать на стрелочку или название команды во всплывающей панели, а именно по значку.

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

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

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

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

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

Как произвести группировку столбцов

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

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

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

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

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

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

Инструкция по разгруппировке данных

Может появиться ситуация, когда ранее созданная группа или подгруппа больше не нужна. Для этого существует отдельная функция – «Разгруппировать». Чтобы ее реализовать, нужно выполнить следующие шаги:

Выделяем элементы, входящие в состав группы.
Открываем вкладку «Данные».
Находим там группу «Структура», открываем ее с помощью стрелочки внизу.
Там нажимаем на кнопку «Разгруппировать». Очень важно нажать именно по значку, а не надписи.

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

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

Вот мы и получаем такой результат.

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

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

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

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

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

После того, как изменения были внесены, можно теперь и сгруппировать часть листов. Для этого нужно нажать на клавишу Ctrl или Cmd (первая – для компьютеров под управлением Windows, а вторая – для техники Apple), и с зажатой кнопкой попеременно щелкнуть по тем листам, которые нужно объединить в группу. После этого программа сделает все за пользователя.

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

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

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

Мало кто знает, что есть еще один способ разгруппировки ячеек, сгруппированных вручную – использование горячих клавиш.

Для этого необходимо сначала выделить те столбцы, которые были сгруппированы, после чего нажать клавиши Alt + Shift + стрелка влево. Если работа осуществляется в компьютере, управляемым под Mac OS, то нужно воспользоваться комбинацией клавиш Command + Shift + J.

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

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

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

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

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

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

Группировка строк и столбцов в Excel — как сделать?

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

Как в Экселе (Excel) сделать группировку данных?

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

Группировка в Excel — возможности

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

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

Параметры группировки

В программе Excel по умолчанию строчка/столбец с итоговыми данными располагается под данными (группировка строк) или справа от данных (группировка столбцов).

Если вы хотите изменить отображение итогов, то нужно открыть вкладку «Данные» и в разделе «Структура» нажать на наклонную стрелку.

Откроется окно настройки, где вам нужно будет убрать соответствующие флажки.

Как сделать группировку данных в Excel?

Рассмотрим подробно на примере, как можно сгруппировать данные в таблице разными способами:

1) Группировка по строкам.

2) Группировка по столбцам.

3) Автоматическая группировка.

Группировка по строкам

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

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

Для того, чтобы сделать группировку по строкам в Экселе (Excel), необходимо выделить требуемую область (группу) и в меню выбрать пункт «Данные» -> «Группировать». В появившемся окне выбираем «Группировать строки».

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

Таблица приобретёт следующий вид:

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

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

Автоматическая группировка

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

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

Для создания автоматической группировки строк в Экселе заходим в «Данные» -> «Группировать» -> «Создание структуры».

Если всё сделали правильно, то строки автоматически будут сгруппированы. Если нет — Excel выдаст ошибку «Невозможно создать структуру документа». Это означает, что на листе отсутствуют формулы, позволяющие сгруппировать данные автоматически по определённым признакам.

Наша таблица будет иметь следующий вид:

Создание нескольких уровней группировки

Как было сказано выше, в Экселе можно создавать довольно сложные структуры данных.

Добавим в нашу таблицу ещё один уровень группировки. Для этого создадим наверху таблицы строчку «Итого» и напишем формулы, позволяющие считать итоги по месяцам.

После этого воспользуемся автоматической группировкой. Вот какая таблица получится:

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

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

Такой вид группировки в Excel осуществляется схожим образом — указываем требуемые столбцы и создаём группу (в диалоговом окне выбираем «Группировать столбцы»).

Убрать группировку

Для того, чтобы разгруппировать данные, нужно выбрать «Данные» -> «Разгруппировать». В зависимости от способа группировки выбираем либо «Разгруппировать», либо «Удалить структуру».

Как сделать группировку строк в excel с плюсом сверху?

Хочу облегчить жизнь тем, кто работает с большими таблицами. Для этого мы сейчас разберемся с понятием группировка строк в excel. Благодаря ему ваши данные примут структурированный вид, вы сможете сворачивать ненужную в настоящий момент информацию, а потом быстро ее находить. Удобно, правда?

Инструкция

Открываем файл excel и приступаем к группировке:

Выделите нужные строки;
Откройте вкладку «Данные» в меню сверху;
Под ним в поле «Структура» найдите команду «Группировать»;

В появившемся окошке поставьте галочку напротив строк;

Ничего сложного, не так ли?
Объединенные экселем области имеют рядом и/или сверху полоску. Под ней находится знак минуса. Нажмите на него. Сгруппированные ячейки свернулись. Чтобы снова их увидеть, нажмите на плюсик сверху.

Задаем название

Если вы еще не давали название блоку из выделенных строк, сделайте это сейчас. С заголовком вам будет легче находить нужную часть при повторных открытиях документа. Запишите наименование в нижней строке столбца.
Вы скажете, что данное расположение неправильно. И будете правы. Такие настройки придуманы разработчиками, но мы сделаем по-своему:

Выполните те же действия, что описаны в инструкции выше. Но не спешите применять команду «Группировать».
Сначала нажмите на маленький квадратик рядом со словом «Структура».
В появившемся окне «Расположение итоговых данных» снимите все галочки.

Теперь нам необходимо исправить заданную ранее систематизацию:

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

Автоматическая структуризация

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

Благодаря этому таблица не занимает много места.

Правда, не всегда программа структурирует то, что нужно. Тогда приходиться прибегать к ручным настройкам. Однако если вы имеете дело с формулами, то автоматическая группировка вероятнее всего вам подойдет. Чтобы ее применить, команде «Группировать» выберите подпункт «Создание структуры» — готово.

Как отменить группировку, созданную вручную, вы узнали выше. Как это сделать после применения автоматического способа? В той же вкладке «Разгруппировать» нажмите «Очистить структуру».

Как сортировать данные таблицы?

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

Цветовое деление

Вы выделяли некоторые строки, ячейки или текст в них другим цветом? Или только хотели бы так сделать? Тогда этот способ поможет вам быстро их сгруппировать:

Во вкладке «Данные» переходим к полю «Сортировка и фильтр».
В зависимости от версии excel нужная нам команда может называться просто «Сортировка» или «Настраиваемая». После нажатия на нее должно появиться новое окно.

В разделе «Столбец» в группе «Сортировать по» выберите необходимый столбец.
В разделе сортировки кликните, по какому условию необходимо выполнить деление. Вам нужно сгруппировать по цвету ячейки? Выбирайте этот пункт.
Для определения цвета в разделе «Порядок» кликните на стрелочку. Рядом вы можете скомандовать, куда переместить отсортированные данные. Если нажмете «Сверху», они сместятся наверх по столбцу, «Влево» — по строке.

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

Объединение значений

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

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

Упрощаем большую таблицу

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

Проверьте, чтобы в начале всех столбцов, которые мы будем объединять, был заголовок, что все они содержат информацию одинакового типа, и нет пустых мест.
Чтобы столбцы имели опрятный вид, в поле сортировки выберите команду «Сортировать от А до Я» или наоборот.
Вставьте итоговые строки, то есть, те, что имеют формулы и ссылаются на объединяемые нами ячейки. Сделать это можно с помощью команды «Промежуточные итоги», которая находится в том же поле, что и кнопка «Группировать».

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

На этом всё, друзья.

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

Также статьи о работе с данными в Экселе:

Ввод данных в Эксель
Типы данных в Эксель
Ввод данных в Excel через форму
Как выполнить сортировку данных в Excel?

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

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

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

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

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

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

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

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

Чтобы сразу разгруппировать все строки и столбцы, необходимо в меню «Разгруппировать» выбрать пункт «Удалить структуру».

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

Настройка группировки

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

Переходим во вкладку «Данные».
В нижнем левом углу блока инструментов «Структура» на ленте расположена маленькая наклонная стрелочка. Кликаем по ней.
Открывается окно настройки группировки. Как видим по умолчанию установлено, что итоги и наименования по столбцам располагаются справа от них, а по строкам – внизу. Многих пользователей это не устраивает, так как удобнее, когда наименование размещается сверху. Для этого нужно снять галочку с соответствующего пункта. В общем, каждый пользователь может настроить данные параметры под себя. Кроме того, тут же можно включить автоматические стили, установив галочку около данного наименования. После того, как настройки выставлены, кликаем по кнопке «OK».

На этом настройка параметров группировки в Эксель завершена.

Группировка по строкам

Выполним группировку данных по строкам.

Добавляем строчку над группой столбцов или под ней, в зависимости от того, как планируем выводить наименование и итоги. В новой ячейке вводим произвольное наименование группы, подходящее к ней по контексту.
Выделяем строки, которые нужно сгруппировать, кроме итоговой строки. Переходим во вкладку «Данные».
На ленте в блоке инструментов «Структура» кликаем по кнопке «Группировать».
Открывается небольшое окно, в котором нужно дать ответ, что мы хотим сгруппировать – строки или столбцы. Ставим переключатель в позицию «Строки» и жмем на кнопку «OK».

На этом создание группы завершено. Для того, чтобы свернуть её достаточно нажать на знак «минус».

Чтобы заново развернуть группу, нужно нажать на знак «плюс».

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

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

Справа или слева от группируемых данных добавляем новый столбец и указываем в нём соответствующее наименование группы.
Выделяем ячейки в столбцах, которые собираемся сгруппировать, кроме столбца с наименованием. Кликаем на кнопку «Группировать».
В открывшемся окошке на этот раз ставим переключатель в позицию «Столбцы». Жмем на кнопку «OK».

Группа готова. Аналогично, как и при группировании столбцов, её можно сворачивать и разворачивать, нажимая на знаки «минус» и «плюс» соответственно.

Создание вложенных групп

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

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

Разгруппирование

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

Выделяем ячейки столбцов или строк, которые подлежат разгруппированию. Жмем на кнопку «Разгруппировать», расположенную на ленте в блоке настроек «Структура».
В появившемся окошке выбираем, что именно нам нужно разъединить: строки или столбцы. После этого, жмем на кнопку «OK».

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Как сделать группировку строк в excel с плюсом сверху?

Инструкция

Открываем файл excel и приступаем к группировке:

Выделите нужные строки;
Откройте вкладку «Данные» в меню сверху;
Под ним в поле «Структура» найдите команду «Группировать»;

В появившемся окошке поставьте галочку напротив строк;

Задаем название

Выполните те же действия, что описаны в инструкции выше. Но не спешите применять команду «Группировать».
Сначала нажмите на маленький квадратик рядом со словом «Структура».
В появившемся окне «Расположение итоговых данных» снимите все галочки.

Теперь нам необходимо исправить заданную ранее систематизацию:

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

Автоматическая структуризация

Благодаря этому таблица не занимает много места.

Как сортировать данные таблицы?

Цветовое деление

Во вкладке «Данные» переходим к полю «Сортировка и фильтр».
В зависимости от версии excel нужная нам команда может называться просто «Сортировка» или «Настраиваемая». После нажатия на нее должно появиться новое окно.

В разделе «Столбец» в группе «Сортировать по» выберите необходимый столбец.
В разделе сортировки кликните, по какому условию необходимо выполнить деление. Вам нужно сгруппировать по цвету ячейки? Выбирайте этот пункт.
Для определения цвета в разделе «Порядок» кликните на стрелочку. Рядом вы можете скомандовать, куда переместить отсортированные данные. Если нажмете «Сверху», они сместятся наверх по столбцу, «Влево» — по строке.

Объединение значений

Упрощаем большую таблицу

Проверьте, чтобы в начале всех столбцов, которые мы будем объединять, был заголовок, что все они содержат информацию одинакового типа, и нет пустых мест.
Чтобы столбцы имели опрятный вид, в поле сортировки выберите команду «Сортировать от А до Я» или наоборот.
Вставьте итоговые строки, то есть, те, что имеют формулы и ссылаются на объединяемые нами ячейки. Сделать это можно с помощью команды «Промежуточные итоги», которая находится в том же поле, что и кнопка «Группировать».

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

На этом всё, друзья.

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

Также статьи о работе с данными в Экселе:

Ввод данных в Эксель
Типы данных в Эксель
Ввод данных в Excel через форму
Как выполнить сортировку данных в Excel?

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

Настройка группировки

Переходим во вкладку «Данные».
В нижнем левом углу блока инструментов «Структура» на ленте расположена маленькая наклонная стрелочка. Кликаем по ней.
Открывается окно настройки группировки. Как видим по умолчанию установлено, что итоги и наименования по столбцам располагаются справа от них, а по строкам – внизу. Многих пользователей это не устраивает, так как удобнее, когда наименование размещается сверху. Для этого нужно снять галочку с соответствующего пункта. В общем, каждый пользователь может настроить данные параметры под себя. Кроме того, тут же можно включить автоматические стили, установив галочку около данного наименования. После того, как настройки выставлены, кликаем по кнопке «OK».

На этом настройка параметров группировки в Эксель завершена.

Группировка по строкам

Выполним группировку данных по строкам.

Добавляем строчку над группой столбцов или под ней, в зависимости от того, как планируем выводить наименование и итоги. В новой ячейке вводим произвольное наименование группы, подходящее к ней по контексту.
Выделяем строки, которые нужно сгруппировать, кроме итоговой строки. Переходим во вкладку «Данные».
На ленте в блоке инструментов «Структура» кликаем по кнопке «Группировать».
Открывается небольшое окно, в котором нужно дать ответ, что мы хотим сгруппировать – строки или столбцы. Ставим переключатель в позицию «Строки» и жмем на кнопку «OK».

На этом создание группы завершено. Для того, чтобы свернуть её достаточно нажать на знак «минус».

Чтобы заново развернуть группу, нужно нажать на знак «плюс».

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

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

Справа или слева от группируемых данных добавляем новый столбец и указываем в нём соответствующее наименование группы.
Выделяем ячейки в столбцах, которые собираемся сгруппировать, кроме столбца с наименованием. Кликаем на кнопку «Группировать».
В открывшемся окошке на этот раз ставим переключатель в позицию «Столбцы». Жмем на кнопку «OK».

Создание вложенных групп

Разгруппирование

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

Выделяем ячейки столбцов или строк, которые подлежат разгруппированию. Жмем на кнопку «Разгруппировать», расположенную на ленте в блоке настроек «Структура».
В появившемся окошке выбираем, что именно нам нужно разъединить: строки или столбцы. После этого, жмем на кнопку «OK».

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

Мы рады, что смогли помочь Вам в решении проблемы.

Как настроить группировку строк в excel?

Инструкция

Открываем файл excel и приступаем к группировке:

Выделите нужные строки;
Откройте вкладку «Данные» в меню сверху;
Под ним в поле «Структура» найдите команду «Группировать»;

В появившемся окошке поставьте галочку напротив строк;

Задаем название

Выполните те же действия, что описаны в инструкции выше. Но не спешите применять команду «Группировать».
Сначала нажмите на маленький квадратик рядом со словом «Структура».
В появившемся окне «Расположение итоговых данных» снимите все галочки.

Теперь нам необходимо исправить заданную ранее систематизацию:

Автоматическая структуризация

Благодаря этому таблица не занимает много места.

Как сортировать данные таблицы?

Цветовое деление

Во вкладке «Данные» переходим к полю «Сортировка и фильтр».
В зависимости от версии excel нужная нам команда может называться просто «Сортировка» или «Настраиваемая». После нажатия на нее должно появиться новое окно.

В разделе «Столбец» в группе «Сортировать по» выберите необходимый столбец.
В разделе сортировки кликните, по какому условию необходимо выполнить деление. Вам нужно сгруппировать по цвету ячейки? Выбирайте этот пункт.
Для определения цвета в разделе «Порядок» кликните на стрелочку. Рядом вы можете скомандовать, куда переместить отсортированные данные. Если нажмете «Сверху», они сместятся наверх по столбцу, «Влево» — по строке.

Объединение значений

Упрощаем большую таблицу

На этом всё, друзья.

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

Источник

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

Пример создания своей пользовательской функции в Excel

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

Открыть редактор языка VBA с помощью комбинации клавиш ALT+F11.
В открывшемся окне выбрать пункт Insert и подпункт Module, как показано на рисунке:

VBA.

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

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

После ввода представленного выше кода необходимо нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
Чтобы воспользоваться созданной функцией, необходимо вернуться к табличному редактору Excel, установить курсор в любую ячейку и ввести название пользовательской функции после символа «=»:

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

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

Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать:

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

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

В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» — переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» — массив переменных типа String с текстами описаний аргументов пользовательской функции.
Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:

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

Примеры использования пользовательских функций, которых нет в Excel

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

Вид исходной таблицы данных:

Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:

N – суммарная зарплата за год;
n – число праздничных дней в году.

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

Код примера:

Public Function Otpusknye(summZp As Long, holidays As Long) As Long If IsNumeric(holidays) = False Or IsNumeric(summZp) = False Then Otpusknye = "Введены нечисловые данные" Exit Function ElseIf holidays <= 0 Or summZp <= 0 Then Otpusknye = "Отрицательное число или 0" Exit Function Else Otpusknye = summZp * 24 / (365 - holidays) End If End Function

Сохраним функцию и выполним расчет с ее использованием:

=Otpusknye(B3;C3)

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

Калькулятор расчета калорий в Excel

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

Вид исходной таблицы данных:

Для расчета используем формулу Миффлина — Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:

Public Function CaloriesPerDay(sex As String, age As Integer, weight As Integer, height As Integer) As Integer If sex = "женский" Then CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age - 161 ElseIf sex = "мужской" Then CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age + 5 Else: CaloriesPerDay = 0 End If End Function

Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).

Пример расчета для первого участника:

=CaloriesPerDay(B3;C3;D3;E3)

В результате использования автозаполнения получим следующие результаты:

Пользовательская функция для решения квадратных уравнений в Excel

Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.

Вид исходной таблицы:

Для решения создадим следующую пользовательскую функцию:

Код примера:

Public Function SquareEquation(a As Integer, b As Integer, c As Integer) As String Dim answer1 As String Dim answer2 As String If a = 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & -c / b & ")" ElseIf c = 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & -b / a & ")" ElseIf b = 0 And c < 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & Sqr(a / c) & ")" ElseIf b ^ 2 - 4 * a * c >= 0 Then answer1 = "Первый корень - " answer2 = "Второй корень - " SquareEquation = answer1 & "(" & (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")" & "; " & _ answer2 & "(" & (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")" Else: SquareEquation = "Решений нет" End If End Function

Найдем корни первого уравнения:

=SquareEquation(A3;B3;C3)

Выполним расчеты для остальных уравнений. Полученные результаты:

Скачать примеры создания пользовательский функций в Excel

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

Источник