Разделение чисел на группы excel

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

Настраиваем параметры функции

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

1. Переключившись во вкладку “Данные” щелкаем по кнопке “Структура” и в открывшемся перечне команд – по небольшому значку в виде стрелки, направленной по диагонали вниз.
2. На экране отобразится небольшое окошко с параметрами функции. Здесь мы можем настроить отображение итогов. Ставим галочки напротив нужных опций (в т.ч. автоматические стили) и жмем кнопку OK.Примечание: расположение итоговых данных в строках под данными многим кажется неудобным, поэтому данный параметр можно выключить.
3. Все готово, теперь можем перейти, непосредственно, к самой группировке данных.

Группируем данные по строкам

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

1. Вставляем новую строку над или под строками, которые хотим сгруппировать (зависит от того, какой вид расположения итогов по строкам мы выбрали). Как это сделать, читайте в нашей статье – “Как добавить новую строку в Excel“.
2. В самой левой ячейке добавленной строки пишем название, которое хотим присвоить группе.
3. Любым удобным способом, например, с помощью зажатой левой кнопки мыши производим выделение ячеек строк (кроме итоговой), которые требуется сгруппировать. Во вкладке “Данные” щелкаем по кнопке “Структура” и в открывшемся списке выбираем функцию “Группировать”. Щелкнуть нужно именно по значку команды, а не по ее названию. Если же нажать на последнее (со стрелкой вниз), откроется еще одно подменю, в котором следует нажать на одноименную кнопку.
4. В появившемся окошке отмечаем пункт “строки” (должен быть выбран по умолчанию) и подтверждаем действие нажатием OK.Примечание: Если вместо ячеек выделить все строки целиком на вертикальной панели координат, а затем применить группировку, то промежуточного окна с выбором строки или столбца не будет, так как программа сразу понимает, что именно ей необходимо сделать.
5. Группа создана, о чем свидетельствуют появившаяся на панели координат полоска со знаком “минус”. Это означает, что сгруппированные данные раскрыты. Чтобы их скрыть, нажимам по минусу или кнопке с цифрой “1” (самый верхний уровень группировки).
6. Теперь строки скрыты. Чтобы их обратно раскрыть, нажимаем по значку “плюса”, который появился вместо “минуса” (или по кнопке “2”).

Группируем столбцы

Чтобы сгруппировать столбцы, придерживаемся примерно такого же алгоритма действий, описанного выше:

1. Вставляем столбец справа или слева от группируемых – зависит от выбранного параметра в настройках функции. Подробнее о том, как это сделать, читайте в нашей статье – “Как вставить столбец в таблицу Эксель“.
2. Пишем название в самой верхней ячейке нового столбца.
3. Выделяем ячейки группируемых столбцов (за исключением добавленного) и применяем функцию группировки.
4. Ставим отметку напротив варианта “столбцы” и кликам OK.Примечание: как и в случае с группировкой строк, при выделении столбцов целиком на горизонтальной панели координат, группировка данных будет выполнена сразу, минуя промежуточное окно с выбором элементов.
5. Задача успешно выполнена. 

Создаем многоуровневую группировку

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

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

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

Когда ранее выполненная группировка столбцов или строк больше не нужна или требуется выполнить ее иначе, можно воспользоваться обратной функцией – “Разгруппировать”:

1. Производим выделение сгруппированных элементов, после чего все в той же вкладке “Данные” в группе инструментов “Структура” выбираем команду “Разгруппировать”. Жмем именно по значку, а не по названию.
2. В открывшемся окне ставим отметку напротив требуемого пункта (в нашем случае – “строки”) и нажимаем OK.Примечание: в случае многоуровневой группировки или наличия нескольких групп данных, каждую из них необходимо расформировать отдельно.
3. Вот и все, что требовалось сделать.

Заключение

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

Предлагаю разбить ваши данные с помощью автофильтра, затем дать полученным диапазонам — имена

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

Sub Макрос1()
'
' Макрос1 Макрос
' Макрос записан 14.06.2006 (HomePC)
'
    Selection.AutoFilter Field:=1, Criteria1:="2"
End Sub

3. разбить данные на группы — именованные диапазоны с помощью кнопки

Private Sub CommandButton1_Click()

Dim r1 As Range
Set r1 = Range("Лист1!_FilterDatabase")

r1.AutoFilter Field:=1, Criteria1:="<3", Operator:=xlAnd
ActiveWorkbook.Names.Add Name:="Name_r1", RefersToR1C1:=r1.SpecialCells(xlCellTypeVisible)

r1.AutoFilter Field:=1, Criteria1:="2"
ActiveWorkbook.Names.Add Name:="Name_r2", RefersToR1C1:=r1.SpecialCells(xlCellTypeVisible)

'  и т.д. 
End Sub

в итоге получаем именованные группы «Name_r1», «Name_r2» и т.д.

4. Количество строк определять при помощи пользовательской формулы, умеющей считать количество строк в дипазонах, состоящих из нескольких областей, ЧСТРОК что-то здезь не работает

вот один, не самый совершенный прример…

Public Function NumRows(Диапазон As Range)
Dim area_r1 As Range
s = 0
For Each area_r1 In Диапазон.Areas
    i = i + 1
    D_Start_R = area_r1.Row
    D_End_R = area_r1.Row + area_r1.Rows.Count - 1
    s = s + D_End_R - D_Start_R + 1
Next
NumRows = s
End Function

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. В зависимости от параметров, которые мы выбрали в настройках, нам нужно вставить новый столбец слева или справа от области, которая будет сгруппирована.
2. Пишем название группы в верхнюю ячейку появившегося столбца.
3. Выберите все столбцы, которые нам нужно сгруппировать (просто оставьте тот, который мы добавили на первом шаге), затем нажмите кнопку «Сгруппировать» так же, как описано выше.
4. Теперь нам нужно щелкнуть пункт «Столбцы» в маленьком окне и нажать кнопку «ОК».
5. Успех.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Есть два способа создать структуру листа: автоматический и ручной.

Автоматическое создание структуры в Excel

Программа может попытаться создать структуру автоматически. Для этого нажмите на ленте: Структура – Группировать – Создать структуру .

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

Ручное создание структуры

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

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

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

1. Нажмите на ленте Структура – Группировать (ли нажмите Alt+Shift+стрелка вправо ). Будет создана группировка

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

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

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

Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру .

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

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

Следующая статья будет посвящена подключению внешних данных и их консолидации. Это неотъемлемая часть работы большинства пользователей Excel. Присоединяйтесь к чтению!

Добавить комментарий Отменить ответ

8 комментариев

Добрый день!
Как в книге создать 2 группировки друг за другом, чтобы когда они свернуты не отображались страницы.
Например, в таблице 15 строк. 3 всегда отображаются. Затем 4 строки в группировке и следующие 4 строки в группировке. Надо чтобы,когда обе группировки собраны из них ничего не отображалось.

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

Добрый день!
Как сделать чтобы группировки были разного цвета( например 1 группа где знак (+) красного. 2 группа где знак (+) зеленого итд)

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

Здравствуйте! Почему при создании структуры с первого столбца в документе не отображается плюсик? Только точки над структурируемыми столбцами и цифры уровня структуры… Что делать, если структура нужна именно с 1го столбца? Excel 2016. Спасибо.

Здравствуйте, Елена. Не отображается плюсик, потому что он проставляется над столбцом промежуточных итогов. Например, в столбцах у Вас данные по месяцам, и они сворачиваются в итоговые цифры года. Плюсик будет над столбцом соответствующего года.
Итоговый столбец может быть слева или справа от группируемого массива. У Вас в настройках выставлено «слева», однако столбца слева не существует, Вы группируете начиная с первого. Потому, плюсика и нет. Вы можете пойти двумя путями:
1. Скрывать и раскрывать группировку кнопками уровней в верхнем левом углу окна
2. Выбрать расположение столбца итогов справа от группируемого массива. Тогда плюсик будет отображаться в следующем столбце после группируемых. Как это сделать — я рассказал в пункте «Ручное создание структуры»

Спасибо.
Научился группировать столбцы за 15 секунд.

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

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

Инструкция

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Переходим во вкладку «Данные».

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

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

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

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

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

На ленте в блоке инструментов «Структура» кликаем по кнопке «Группировать».

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

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

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

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

Справа или слева от группируемых данных добавляем новый столбец и указываем в нём соответствующее наименование группы.

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

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

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

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

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

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

Представьте себе такую ситуацию: Вы анализируете подборку счетов, собранных в сводной таблице. Вы можете обобщать счета, объединяя их в группы по 5 – 10 или даже 100 счетов. Давайте рассмотрим пример, где идентификатор строки OrderID – это числовое поле. В данный момент каждому счёту соответствует отдельная строка, и результат получается довольно громоздким. Мы можем сгруппировать эти данные, чтобы упростить таблицу.

В этой сводной таблице каждый идентификатор OrderID представлен в отдельной строке. Такие данные можно считать обобщёнными, но нам этого недостаточно.

Для этого кликните правой кнопкой мыши по одному из OrderID данной сводной таблицы и в появившемся меню выберите Group (Группировать). Так как OrderID это числовое поле, то диалоговое окно уже будет подготовлено для численной группировки с полями Starting At (Начиная с), Ending At (По) и By (С шагом). В данной ситуации Excel предлагает Вам группировать элементы по 10 штук.

По умолчанию Excel предлагает в качестве значения Starting At (Начиная с) первый элемент OrderID: 10248. Тем не менее, Вы можете настроить точнее или создать другую группировку, установив собственное значение параметра Starting At (Начиная с). Чтобы начать с элемента 10240, введите это значение в поле Starting At (Начиная с) – Вы можете установить любое стартовое значение, даже такое, которого нет в данных.

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

Итак, мы настроили значение Starting At (Начиная с), оставили значение Ending At (По) таким, как предложил Excel, и оставили значение By (С шагом) равным 10, поскольку оно нам подходит. Вы можете делать группы меньше или больше, изменяя это значение – например: 5, 20, 50 или 100. Теперь жмём ОК. Данные будут сгруппированы по значению OrderID в более удобные для обработки группы.

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

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

Если данные, с которыми Вы работаете, являются датами, то Вы сможете использовать похожий приём. Чтобы сгруппировать информацию по датам, кликните правой кнопкой мыши по дате в столбце или строке Вашей сводной таблицы и выберите Group (Группировать). Вы можете выбрать шаг группировки – Seconds (Секунды), Minutes (Минуты), Hours (Часы), Days (Дни), Months (Месяцы), Quarters (Кварталы) или Years (Годы), и установить начальный и конечный момент времени. Для группировки по годам, кварталам и месяцам интервал зафиксирован равным 1, а для группировки по дням Вы можете установить собственный интервал, например, равный 7 дням, чтобы сгруппировать данные по неделям. Для этого выберите Days (Дни) как шаг группировки, установите количество дней равным 7, укажите для параметра Starting At (Начиная с) дату, которая выпадает на начало недели, и нажмите ОК. Информация будет сгруппирована по неделям.

Большой объём данных будет сгруппирован по неделям (по 7 дней), и первым днем выступит воскресенье – день недели, соответствующий дате, указанной в параметре Starting At (Начиная с).

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

Не всегда набор записей можно разделить по группам с такой же лёгкостью, как последовательность чисел или дат. Иногда группа становится группой просто потому, что Вы так решили. В этом случае можно создать свою собственную группировку. Откройте лист со сводной таблицей, кликните по первому элементу данных, который нужно включить в группу и далее, с нажатой клавишей Ctrl, последовательно щелкните по всем элементам, которые также нужно включить в эту группу. Затем на вкладке PivotTable / Options (Работа со сводными таблицами / Анализ) нажмите Group Selection (Группировка по выделенному) и элементы будут добавлены в новую группу. Таким же образом Вы можете выделить другие элементы и создать еще одну группу.

Выделив несколько полей, Вы можете объединить их в группы так, как будет удобно.

При помощи этого инструмента Вы можете группировать данные в небольшие подборки, с которыми удобно работать. Так как по умолчанию группы названы именами Group 1 (Группа 1) и Group 2 (Группа 2), Вам придётся самостоятельно переименовать их. Для этого кликните ячейку, содержащую имя группы, и в строке формул введите более подходящее имя для группы.

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

Вы можете развернуть или свернуть группу, нажав символы плюс или минус (+/-) слева от имени группы.

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

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

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

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

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

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

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

Вы можете создавать группы в сводной таблице, которая собрана из нескольких консолидированных диапазонов, например, из данных на разных листах рабочей книги. Для этого в Excel 2007 и 2010 на Панель быстрого доступа необходимо добавить команду PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм).

Чтобы сделать это, кликните выпадающее меню Панели быстрого доступа, выберите пункт More Commands (Другие команды), в открывшемся диалоговом окне установите группу All Commands (Все команды) и в списке команд найдите PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм). Нажмите кнопку Add (Добавить), чтобы добавить кнопку на Панель быстрого доступа.

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

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

Создайте новый лист в Вашей рабочей книге Excel, кликните только что добавленную кнопку PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм), выберите пункты Multiple Consolidation Ranges (В нескольких диапазонах консолидации) и PivotTable (Сводная таблица), а затем нажмите Next (Далее). Выберите I will create the page fields (Создать поля страницы) и снова нажмите Next (Далее). Теперь Вам нужно указать каждый из диапазонов. Зайдите на первый лист, выберите все данные, включая заголовки таблицы, и нажмите Add (Добавить). Повторите шаги, чтобы добавить информацию со следующего листа и так далее, пока не добавите данные со всех листов, которые планировали использовать в сводной таблице.

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

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

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

Так, например, диапазон, содержащий даты, может быть частью группы, которая разделяет месяцы по кварталам, а также частью группы, которая разделяет месяцы по сезонам: Winter (Зима), Spring (Весна), Summer (Лето) и Fall (Осень). Группировки могут быть по любому признаку, который Вы посчитаете подходящим для своих данных. Нажмите Next (Далее), кликните по ячейке, в которой должен будет находиться верхний левый угол сводной таблицы и нажмите Finish (Готово).

В Мастере сводных таблиц и диаграмм Вы можете связать диапазоны со страницами, как показано на рисунке выше – Quarters (Кварталы) и Seasons (Сезоны).

Сводная таблица будет сформирована автоматически, группы будут созданы как поля Report Filter (Фильтры) в списке полей сводной таблицы, но Вы, если пожелаете, можете перетащить эти поля в область Row Labels (Строки) или Column Labels (Колонны).

На рисунке выше показано: мы переместили страницу Page 2 (Сезоны) из области Report Filter (Фильтры) в область Row Labels (Строки).

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

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

Группировка в сводных таблицах (831,4 KiB, 976 скачиваний)

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

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

ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.

1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
2. Выбрать из контекстного меню пункт Группировать (Group) ;
3. В поле Начиная с (Starting at) ввести начальную дату для группы;
4. В поле по (Ending at) ввести конечную дату для группы;
5. В поле с шагом (By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years) ;
6. Нажать OK
   

ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше:

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

И вроде бы в ячейках даты/числа и все равно. В данном случае следует проверить — а действительно ли числа это числа, а даты — даты? Потому как бывает и так, что выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:

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

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

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

Если после этого группировка все равно отказывается работать — значит где-то еще есть числа/даты, записанные как текст. Но они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иных программ. Часто побеждают это очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Тогда Excel преобразует дату/число в настоящие дату/число. Но если таких ячеек хотя бы 100 — это уже не на пару минут рутины. Благо, все это можно сделать за пару секунд. Чтобы быстро преобразовать ячейки с датами/числами, записанными как текст в реальные даты/число необходимо:

• скопировать любую пустую ячейку на листе
• выделить все ячейки с датами/числами
• правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values) , операция — Сложить (Multiply)
• ОК

Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам — но это уже совершенно не сложно: правая кнопка мыши —Формат ячеек (Format Cells) -Дата (Date) .
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?

ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

1. Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
2. Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
3. Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Группа по выделенному (Group Selection);
   
   
4. При необходимости задать свое имя группе

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

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1 (Group1) для выбранных элементов или Кв-л1 (Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

1. Выделить имя группы;
2. Нажать клавишу F2;
3. Ввести новое имя группы.

1. Выделить группу элементов, которые требуется разгруппировать;
2. На вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Разгруппировать (Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать (Ungroup) ).

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

1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры— Сервис— Средства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

Так же см.:
[[Общие сведения о сводных таблицах]]
[[Сводная таблица из нескольких листов]]

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Можно ли добавить дополнительные «Промежуточные итоги» для сводной таблицы, содержащей большую структуру — столбцов.
Так, чтобы эти дополнительные итоги — показывали итоги по каждой структуре.
Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).

Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).

Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице). Приходится делать надстройку поверх Сводной.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

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

Выделите ячейки , которые нужно отформатировать.

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

На вкладке число в списке Категория выберите пункт число.

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

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

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

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

Функция ФИКСИРОВАННЫЙ( ) , английская версия FIXED(), округляет число до заданного количества десятичных цифр и возвращает результат в виде текста.

Синтаксис функции

ФИКСИРОВАННЫЙ(число;[число_знаков];[без_разделителей])

Число — число, которое округляется, форматируется и преобразуется в текст.

Число_знаков — число цифр Числа справа от десятичной запятой. Аргумент не обязательный, по умолчанию используется значение 2.

Без_разделителей — логическое значение. Если аргумент без_разделителей имеет значение ИСТИНА (или любое число не равное 0), функция ФИКСИРОВАННЫЙ() не включает разделители разрядов в возвращаемый текст. Если аргумент без_разделителей имеет значение 0 или ЛОЖЬ (по умолчанию в EXCEL 2007), то результат форматируется с разделителями разрядов (в качестве разделителя разряда используется пробел, код символа 32).

Примечание : в России разряды разделяются пробелом, а в США – запятой (десятичная часть отделяется точкой).

Примечание: в диалоговом окне Формат ячеек можно установить флажок для разделения разрядов ячеек в числовом формате. Для этого нажмите CTRL+1, выберите числовой формат Числовой, затем поставьте галочку в поле Разделитель групп разрядов.

Если значение аргумента Число_знаков является отрицательным, число округляется слева от десятичной запятой (см. примеры ниже).

Отличие от Формата ячейки

Различие между форматированием ячейки, содержащей число, с помощью Формата ячейки и форматированием с помощью функции ФИКСИРОВАННЫЙ() состоит в том, что функция ФИКСИРОВАННЫЙ() преобразует свой результат в текст, а число, отформатированное с помощью Формата ячеек, по-прежнему остается числом. После преобразования, значение выравнивается по левому краю, если в Формате ячейки выравнивание по горизонтали указано по значению.

ПРИМЕРЫ

=ФИКСИРОВАННЫЙ(1234,567;1) Округляет число 1234,567 на 1 знак справа от десятичной запятой (1234,6) см. файл примера .

=ФИКСИРОВАННЫЙ(1234,567;-1) Округляет число 1234,567 на 1 знак слева от десятичной запятой (1230)

=ФИКСИРОВАННЫЙ(1234,567;1;ЛОЖЬ) Округляет число 1234,567 на 1 знак справа от десятичной запятой (1234,6) и включает разделители разрядов

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

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

Установка разделителя групп разрядов

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

Выделите ячейку или диапазон ячеек.

.Нажмите кнопку Формат с разделителем группы Число вкладки Главная

В этом случае автоматически установится финансовый формат. Для установки разделителя групп разрядов при числовом формате надо использовать вкладку Число окна диалога Формат ячейки. . Надо поставить флажок в поле Разделитель групп разрядов.

Форматы отрицательных чисел

Используя окно диалога, приведенное на рисунке выше для удобства просмотра данных можно установить отображение отрицательных чисел красным цветом. Знак минус (-) при этом можно отображать или не отображать в зависимости от выбранного образца.

Форматы дробных чисел

Изменение разрядности десятичных дробей

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

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

Форматы простых дробей

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

Выделив ячейку или диапазон ячеек, откройте вкладку Число диалогового окна Формат ячейки. В списке Числовые форматы выберите Дробный, в списке Тип выберите необходимый формат.

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

Процентный формат

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

Для выделенной ячейки или диапазон ячеек нажмите кнопку Процентный формат группы Число вкладки Главная.

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

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

Экспоненциальный формат

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

Например, число 62500000000 будет отображена как 6,25E+11, где 6,25 – это мантисса числа, Е – признак десятичной системы счисления, а +11 – степень, в которую возводится основание системы счисления.

То есть эта запись означает 6,25 умноженное на 10 11 (десять в одиннадцатой степени), а число 0,0000000000000002345 будет отображена как 2,345E-16, что означает 2,345 умноженное на 10 -16 (десять в минус шестнадцатой степени). Для установки экспоненциального формата проще всего воспользоваться раскрывающимся списком Числовой формат.

Финансовый и денежный формат

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

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

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

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

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

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

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

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

Форматирование дат и времени

Формат даты

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

1.Выделите ячейку или диапазон ячеек.

2.Откройте вкладку Число диалогового окна Формат ячейки.

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

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

Формат времени

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

1. Выделите ячейку или диапазон ячеек.
2. Откройте вкладку Число диалогового окна Формат ячейки.
3. В списке Числовые форматы выберите Время, в списке Тип выберите необходимый формат. При необходимости предварительно в раскрывающемся списке Язык выберите язык, в стандартах которого будет отображено время.
   
   Для установки полного формата времени можно также воспользоваться раскрывающимся списком Числовой формат.

Текстовый формат

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

Для установки текстового формата можно воспользоваться раскрывающимся списком Числовой формат.

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

Дополнительные форматы

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

1. Выделите ячейку или диапазон ячеек.
2. Отобразите вкладку Число диалогового окна Формат ячейки.
3. В списке Числовые форматы выберите Дополнительно, в списке Тип выберите необходимый формат. При необходимости предварительно в раскрывающемся списке Язык выберите язык.
   

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

Помогла ли Вам статья?