Как сгруппировать ячейки в excel с одинаковыми значениями - Word и Excel

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

Как выделить одинаковые ячейки группами

Допустим мы имеем список поставщиков:

Перед тем как найти повторяющиеся ячейки в Excel, отсортируем поставщиков по их идентификатору. Переходим в ячейку A2 и выбираем на закладке «ДАННЫЕ» в разделе «Сортировка и фильтр» инструмент «Сортировка от А до Я».

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

Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».

В поле для ввода формулы вводим следующе значение: =$A2<>$A3

Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.

В результате получаем эффект как отображено на рисунке.

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

Так как мы сортировали поставщиков по их идентификатору в формуле мы зафиксировали смешанной ссылкой столбец листа $A. Если значения в соседних ячейках столбца $A равные между собой тогда формула возвращает значения ЛОЖЬ и форматирование границе не применяется. Но если верхнее значение неравно (оператор <>) нижнему значению тогда формула возвращает значение ИСТИНА и применяется форматирования нижней границы целой строки (так как в смешанной ссылке номер строки не есть абсолютным, а является относительным адресом).

Полезный совет! Если нужно разграничить данные не по первому столбцу таблицы, по любому другому, тогда соответственно отсортируйте и просто укажите адрес столбца. Например, разграничим по повторяющимся датам, а не по поставщикам. Для этого сначала сортируем данные по датам, а потом используем условное форматирование немного изменив формулу: =$C2<>$C3

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

Источник

Как сложить и удалить дубли в Excel.

Смотрите такжеNextМихаил С. местах они больше, как это сделать.Закрыть и загрузить элементы из второго один и применить сложности. – этот способ
B1, то связанные с(Выделенная область содержит или функции СЦЕП.
 12 в ячейке
Рассмотрим,On Error Resume: Без объединения (УФ) не встречаются), такSub Merge() Dim Да и объёми выбираем
списка. потом к полученномуНемного изменим структуру исходнойИмеется отчет продаж в гораздо быстрее., нажимаем ними данные в несколько значений данных.Выделите ячейку, в которую В2 основной таблицыкак сложить и удалить Nextwebley
же эти ячейки myTable As Range файла большой -Закрыть и загрузить в…Вложенные друг в друга набору инструмент таблицы: сети магазинов поУрок подготовлен для ВасShift+Стрелка вправо столбце Объединение ячеек приведёт вы хотите вставить
на цифру 2. дубли в ExcelWith Workbooks.Add.Worksheets(1): Можно так. Единственное,
местами уже объединены. Dim address As макрос быстрее должен (Close&Load to…) две функции ЕСЛИОШИБКАУдалить дубликатыНа этот раз нам целой стране. Нам командой сайта office-guru.ru
, чтобы добавить кFull Name к потере всех объединенные данные. Получилось так.
. Как выделить дубли.Range(«A1»).Resize(oDict.Count) = Application.Transpose(oDict.keys)
последняя ячейка вТак вот хотелось String Application.ScreenUpdating =
справляться.: реализуют вывод сначалас вкладки необходимо объединить все
нужно сделать таблицуИсточник: https://www.ablebits.com/office-addins-blog/2013/10/13/merge-columns-excel-without-losing-data/
выделению ячейкутакже будут потеряны. значений, кроме левогоВведите = (знак равенства)
Как работать с таблицей в Excel, читайте.Range(«B1»).Resize(oDict.Count) = Application.Transpose(oDict.items) таблице должна быть бы объединить эти False address =UPD Важно чтобыВ следующем диалоговом окне уникальных из списка-1,Данные (Data — Remove ячейки с одинаковыми данного отчета болееПеревел: Антон АндроновC1Теперь нам нужно превратить верхнего). и выберите первую консолидации, как объединить в статье «КакEnd With заполнена (можно руками значения в одну «A1:AI12» Set myTable цикл искал дубли
 (оно может выглядеть
а потом из Duplicates) значениями в столбце
читабельной. Для этогоАвтор: Антон Андронов. Затем нажимаем формулу в значение,Далее в этой статье ячейку, которую нужно несколько таблиц в выделить повторяющиеся значенияOn Error GoTo
:)) ячейку. = Range(address) For компании не по немного по-другому - списка-2 друг за: «Штат» (B) в
следует красиво сформатироватьYYYSCtrl+Shift+Стрелка вниз
чтобы мы могли Вы найдёте 2 объединить.
одну консолидацией, читайте в Excel» тут. 0Jon1242
Подскажите как это i = 2 всему листу, а не пугайтесь) выбираем другом.Само-собой, такой способ не столбце «№» (A). первый столбец, в: Здравствуйте! Нужна помощь, чтобы выделить все удалить ненужные столбцы способа, как объединить
Введите символ в статье «Как Как удалить повторяющиесяEnd Sub: Уважаемый СЛЭН.
можно реализовать. To myTable.Rows.Count ‘ только подряд. ЕслиТолько создать подключение (OnlyОбратите внимание, что это
подойдет, если данныеЕсли мы хотим, чтобы котором содержаться названия
 в объединении одинаковых
ячейки с данными с нашего листа данные из нескольких& объединить таблицы в значения, смотрите вKuklPА не можете
P.S. По поиску For j = встречается другая компания, create connection) формула массива, т.е. в исходных списках действия макроса распространялось штатов: ячеек, причем так в двух столбцах.
Excel. Выделите все столбцов в один

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

excel-office.ru

Объединение текста из двух или нескольких ячеек в одну

к сожалению не i + 1 тогда записывать объединённый: после набора ее

Объединение данных с помощью символа «амперсанд» (&)

часто меняются - на несколько выделенныхМы хотим объединить все
чтобы строки тожеСкопируйте данные в буфер ячейки с данными без потери данных
в кавычки.Второй способ. дубли в Excel».Но загляните еще
он также объединял нашел, хотя решение To myTable.Rows.Count If результат в КатегориюПовторяем всю процедуру (пункты нужно ввести в

Объединение данных с помощью функции СЦЕП

придется повторять всю столбцов, то делаем ячейки с одинаковыми
объединялись пример прилагаю! обмена ( в объединённом столбце
и, не используяВыберите следующую ячейку, которую

Сложить все дублиИтак, нам нужно сюда: ячейки , только такой задачи наверняка
myTable.Cells(i, 3) = и переходить к 2-6) для второго ячейку не обычным процедуру после каждого следующее. Сначала добавим

support.office.com

Как объединить два столбца в Excel без потери данных

значениями в столбце Отблагодарю чем смогу)Ctrl+C (выделите первую ячейку макрос VBA. нужно объединить, и

с помощью формул. перед удалением дублей,Hugo по строкам. обсуждалось. myTable.Cells(j, 3) And следующей компании. Это списка. При переименовании Enter, а сочетанием изменения заново. новую переменную: «Штат» (A). ЭтоSkyProили в столбцеОбъединяем два столбца при нажмите клавишу ВВОД. Таблица та же.

их сложить. Рассмотрим: В той теме,Заранее спасибо, простотухачевский myTable.Cells(i, 4) = чтобы не грузить заголовка столбца важно клавиш Ctrl+Shift+Enter иЭтот способ является, поDim j As Long можно реализовать с: Это как? ПоCtrl+InsFull Name помощи формул Пример формулы:

Принцип такой. Создаем несколько способов.

откуда этот код я оч. далек: а для чего myTable.Cells(j, 4) And лишним поиском, так использовать то же затем скопировать (протянуть) сути, логическим продолжениемДалее добавим строку с помощью ручного выделения
каким параметрам? в– что предпочитаете).и затем нажмитеОбъединяем столбцы при помощи=A2&» «&B2 дополнительный столбец, вПервый способ. ( ) есть от этого. это нужно? myTable.Cells(i, 15) = как файл изначально имя (Люди), что вниз на нижестоящие

предыдущего. Если списки кодом начала нового отдельных групп одинаковых каких столбцах?Запустите Блокнот:Ctrl+Shift+Стрелка вниз Блокнота.

ячейках которого будемСамый простой способ
такой пост:Guest

Объединяем два столбца при помощи формул

я бы не myTable.Cells(j, 15) And отсортирован. и в предыдущем ячейки с запасом. не очень большого цикла, который будет значений и объединенияЦитатаStart

.Скопируйте содержимое в буферДопустим, у Вас естьВыделите ячейку, в которую писать название дубля, – это использоватья бы сделал: Нужно так, строка советовал так делать myTable.Cells(i, 19) =китин запросе.В английской версии Excel размера и заранее проходить по другим их ячеек, воспользовавшись
YYYS, 12.10.2013 в(Пуск) > обмена ( таблица с данными
вы хотите вставить
который нужно сложить.

 функцию так, чтоб не одна Прикрепленные файлыслэн myTable.Cells(j, 19) Then: а вот походуВ окне Excel на эта формула выглядит известно предельное количество столбцам выделенного диапазона: инструментом: «ГЛАВНАЯ»-«Выравнивание»-«Объединить и 18:08, в сообщенииAll ProgramsCtrl+C о клиентах, и объединенные данные. Мы будем писать«Консолидация» в Excel писать новый макрос:

post_304320.PNG (3.78 КБ): что-то в этом myTable.Cells(i, 1) = вам в помощь вкладке как: элементов в нихFor j = 1 поместить в центре».
№ 1200?’200px’:»+(this.scrollHeight+5)+’px’);»>причем так(Все программы) >или Вы хотите объединитьВведите выражение дубли в ячейках. У нас такая1. левее суммPetro роде: myTable.Cells(i, 1) &
СцепитеслиДанные (Data)=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1), (например, не больше To Selection.Columns.Count Но если таблица чтобы строки тожеAccessoriesCtrl+Ins столбцы с именем= СЦЕП( С2, С3, С4, таблица создал столбец, куда
: Этот код яSub Макрос1() «, » &или ТЫЦили на вкладке 0)), INDEX(Список2, MATCH(0, 10), то можноПосле конца, ранее созданного содержит тысячи таких объединялисьВсю строку?(Стандартные) >– что предпочитаете), (First Name) и. С5.. собрал данные из так понимаю объединяет’ myTable.Cells(j, 1) myTable.Cells(i,и вообще ПОИСКPower Query COUNTIF($E$1:E1, Список2), 0))), объединить две таблицы (внутреннего) цикла добавляем групп, да ещеМожет вам нужноNotepad затем кликните правой фамилией (Last Name)Выберите первую ячейку, которуюА в следующемНам нужно сложить все
первых трёх через строки в первомDim i&, n&, = myTable.Cells(i, РУЛИТвыбираем «»)
- в одну прямыми инструкцию Next для с разным количеством просто две таблицы(Блокнот) кнопкой мыши по в один столбец нужно объединить. столбце напишем формулу,
- числа в столбце разделитель формулой столбце, а как arr, rn& + myTable.Cells(j,о что получилось:Получить данные — ОбъединитьМинус у такого подхода ссылками, добавить справа конца нового (внешнего) повторяющихся ячеек, тогда свести в одну?Вставьте данные из буфера любой ячейке в

с полным именемДля разделения объединяемых ячеек которая будет искать В по каждому=B1&»|»&C1&»|»&D1 сделать чтобы воn = Cells(Rows.Count,

 myTable.Cells(i, 9)Mikez запросы — Добавить в том, что столбец с единичками цикла . И

Объединяем столбцы при помощи Блокнота

рационально написать макрос.Если да, то обмена в Блокнот том же столбце (Full Name). используйте запятые, а дублирующие данные в дублю из столбца

это я в всех объединял или 1).End(xlUp).Row = myTable.Cells(i, 9): Не осилю. С

(Get Data - формулы массива ощутимо и построить по соответственно сделаем код Он сам быстро на форуме полно (нажмите (Full Name) иДобавьте в таблицу новый для добавления пробелов, первом столбце и А – отдельно А собирал, но в выделенном?
arr = Cells(1, + myTable.Cells(j, 9) макросами у меня Merge Queries - замедляют работу с получившейся таблице сводную:
более читабельным с и автоматически выполнит подобных тем.Ctrl+V в контекстном меню столбец. Для этого запятых и другого складывать цифры по все ячейки с
не принципиально.И еще для 1).Resize(n) myTable.Cells(i, 10) = плохо. А формулу Append) файлом, если в
Как известно, сводная помощью отступов табуляции. всю работу заYYYSили выберите наведите указатель мыши текста — кавычки. дублям из второго цифрой 1, отдельно
2. макросом получил полного счастья -rn = 1 myTable.Cells(i, 10) + использовать — не: исходных таблицах большое таблица игнорирует повторы, Кроме того, для Вас.: Это одна таблицаShift+InsPaste Special на заголовок столбцаВведите закрывающую скобку в столбца. все ячейки с сводную типа когда он объединяетFor i = myTable.Cells(j, 10) myTable.Cells(i, то. Там вВ появившемся диалоговом окне (несколько сотен и поэтому на выходе всех экземпляров объекта
Откройте редактор Visual Basic там где то)(Специальная вставка). Отметьте (в нашем случае конце формулы иВ ячейке D2 цифрой 2, т.д.
44550|225|40724 11,16 выскакивает окно что 2 To n 14) = myTable.Cells(i, итоговой таблице тысячи выбираем наши запросы более) количество элементов. мы получим объединенный
Cells во втором (ALT+F11): 800 наименований, вСкопируйте в буфер обмена вариант это столбец D), нажмите клавишу ВВОД.

пишем такую формулу. Мы не знаем,3. Инструментом «текст объединение приведет кIf arr(i, 1) 14) + myTable.Cells(j, строк.

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

И создайте новый модуль

office-guru.ru

Как объединить повторные ячейки? (Формулы)

 первой ячейке есть символ табуляции. ДляValues кликните по нему Пример формулы: =СУММЕСЛИ(A2:A9;C2;B2:B9) сколько и какие

 по столбцам» опять потере всех данных <> arr(i - 14) Range(myTable.Cells(j, 1),
Может кто заВ итоге получим новый списках большое количество Вспомогательный столбец с 1 введем переменную с помощью инструмента:
повторения (это id этого нажмите клавишу(Значения) и нажмите
правой кнопкой мыши=CONCAT(A2, » Семья»)В ячейку D3

 у нас дубли получил столбцы. как от него 1, 1) Then myTable.Cells(j, myTable.Columns.Count)).Delete Shift:=xlUp плату сделает от запрос, где два элементов, например, по 1 нужен только j (например, Selection.Cells(i, «Insert»-«Module». А потом товара). Повторения изTabОК и в контекстном. пишем такую формулу. в большой таблице.Это и Вам избавиться?With Range(Cells(rn, 1), j = j начала до конца? списка будут соединены несколько сотен или

 потому, что Excel j)). Новая версия

 запишите в него за того, что
в Блокноте, затем.
меню выберите пункт

Из этой короткой статьи =СУММЕСЛИ(A3:A9;C3;B3:B9)На новом листе подойдёт (чтоб неLow

 Cells(i — 1, — 1 Endкитин

 друг под другом. тысяч, то вместо умеет строить сводные измененного кода макроса VBA-код макроса:

 у товара ест сочетаниеУдалите столбцыInsert Вы узнаете, какПо такому принципу делаем активной ячейку. писать новый макрос: Всем доброго. 1)) If Next j: это в раздел Осталось удалить дубликаты медленной формулы массива
по таблицам, содержащим, выглядит следующим образом:

excelworld.ru

Макрос для объединения одинаковых ячеек в таблице Excel

Sub JoinDoubles() разные размеры иCtrl+Shift+Стрелка влевоFirst Name(Вставить). Давайте назовём в Excel объединить написать формулы в

Как объединить одинаковые ячейки в столбце используя макрос

Мы, в нашем )Есть файл вида.HorizontalAlignment = xlGeneral Next i Application.ScreenUpdating фриланс.ждите модератора кнопкой лучше использовать принципиально по крайней мере,Sub JoinDoubles()

Dim i As количество а ID, а затеми новый столбец несколько столбцов в других ячейках столбца примере, сделаем активной-=85558=- адрес, дом, квартира,.VerticalAlignment = xlTop = True Endя же вамУдалить строки — Удалить другой подход, а два столбца.Dim i As Long одинаковый. Мне нужноCtrl+XLast Name

Full Name один, не потеряв

D. Получилось так. ячейку Е2 наLow сумма и тд..WrapText = False

Subвот здесь адрес в файлике показал, дубликаты (Delete Rows именно - инструменты При изменении исходных списков LongApplication.DisplayAlerts = False объединить строки с., они нам больше . при этом данные. Эту же формулу можно этом же листе. : Видимо я не в котором все .Orientation = 0

твоей таблицы: что полулачается с — Delete Duplicates) надстройки Power Query. новые данные поDim j AsFor i = одинаковым ID, и

Замените в Блокноте символы не нужны. ДляВ ячейкеУ Вас есть таблица написать, вызвав диалоговое На закладке «Данные» совсем ясно описал строки повторяются с.AddIndent = Falseaddress = «A1:AI12″ этой UDF .

: Эта надстройка по прямым ссылкам попадут Long Selection.Rows.Count To 2 чтобы ячейка размерTab этого:D2 в Excel, и окно функции «СУММЕСЛИ». в разделе «Работа

проблему либо не разницей в поле.IndentLevel = 0а вообще лучше надо только выбратьГотовый запрос можно переименовать умолчанию встроена в в объединенную таблицу,Application.DisplayAlerts = False Step -1

добавлялась и была

Как объединить все одинаковые ячейки в любой таблице

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

запишите вот такую Вы хотите объединить Она расположена на с данными» нажимаем понимаю идею объединения сумма. Необходимо объединить

.ShrinkToFit = False преобразовать твою табличку уникальные справа на панели Excel 2016. Если но сводную придется

For j =

If Selection.Cells(i, 1) через запятую, а которые Вы хотитеB формулу:

два столбца в закладке «Формулы» в

на кнопку функции значений в один повторяющиеся строки между.ReadingOrder = xlContext в табличку екселяMikez параметров, дав ему у вас Excel обновить уже вручную 1 To Selection.Columns.Count = Selection.Cells(i - строка количество суммировалась. использовать в качестве, затем, нажав и=CONCATENATE(B2,» «,C2) один построчно. Например, разделе «Библиотека функций»

«Консолидация». Выйдет диалоговое столбец. Ваш макрос собой и сложить .MergeCells = True и задать имя. : Большое спасибо, Игорь. вменяемое имя (это 2010 или 2013, (правой кнопкой мышиFor i = 1, 1) Then В двух файлах разделителей.Для этого нажмите удерживая клавишу =СЦЕПИТЬ(B2;" ";C2) необходимо объединить столбец нажимаем функцию «Математические», окно. замечательно работает и значения полей сумма. End With тогда нужно будет

Но я глянул будет имя таблицы-результата то ее можно -

Selection.Rows.Count To 2Range(Selection.Cells(i — 1, я сделал пример

Ctrl+HCtrlB2 с именем и выбираем «СУММЕСЛИ». Заполним

exceltable.com

Слияние двух списков без дубликатов

Заполнили его так: суммирует значения в Нашел примерчик, ноrn = i эти строки: — там выше по факту) и отдельно скачать иОбновить Step -1 1), Selection.Cells(i, 1)).Merge как было и, чтобы открыть диалоговое

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

Способ 1. Удаление дубликатов

увы, знаний какEnd Ifaddress = «A1:AI12″ моего уровня. А все и можно установить (бесплатно).). Если не нуженIf Selection.Cells(i -End If как должно быть! окно столбца

C2 в один, илиВ строке «Диапазон» указываем выбрали функцию «Сумма». моя проблема в его доработать подNext

Способ 1а. Сводная таблица

Set myTable = чтобы дотянуться мне все выгружать наАлгоритм действий следующий: пересчет «на лету», 1, j) =Next Если, кто поможетReplaceC– это адреса объединить несколько столбцов, диапазон столбца А Можно выбрать другие том, что кроме

свои нужды неEnd Sub Range(address)заменить на: время надо, которого лист командойОткрываем отдельную вкладку установленной то лучше воспользоваться Selection.Cells(i, j) ThenSelection.VerticalAlignment = xlVAlignCenter в долгу не(Заменить). В поле.

ячеек из столбцов таких как улица, с дублями. действия. столбца с не хватает. Главная проблема,Eternal-SmokeSet myTable = нет. Да иЗакрыть и загрузить (Close&Load) надстройки другими вариантами.Range(Selection.Cells(i — 1,

Способ 2. Формула массива

Application.DisplayAlerts = True останусь)Find what(другой способ) выберите любуюFirst Name город, индекс иВ строке «Критерий»В строке «Ссылка» уникальными значениями и не могу понять: Спасибо, буду пробовать Range(«MyTableName[#All]»)ну и конечно смысла нет -:Power QueryМожно решить проблему формулами. j), Selection.Cells(i, j)).MergeEnd SubDV

(Что) вставьте из ячейку в столбцеи

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

End If

Теперь если нам необходимо: Формулами можно так: буфера обмена символBLast Name столбец с адресом,
С, в которой таблицы. У нас выводить остальные столбцы, бы после сравнения
А нужно это, я задавал тоже раз в год изменениях или дополнениях Excel 2010-2013) или пересчет и обновлениеNext

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

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

в исходных списках, просто идем на результатов будет происходитьNext

одинаковыми значениями, то: Или макросом:Replace withCtrl+Space в формуле между чтобы в дальнейшем дубля. диапазона с абсолютными

Способ 3. Power Query

адресе есть повтор записывались все столбцы, всё симпатично выглядело, если ты поменяешьВо «Фриланс» переносить достаточно будет лишь вкладку автоматически и мгновенно,Selection.VerticalAlignment = xlVAlignCenter выделите диапазон A1:A18ЗЫ: Использована функция(Чем) введите Ваш(Пробел), чтобы выделить ними стоит символ распечатывать адреса наВ строке «Диапазон ссылками и названием и суммируется значение

а не только

начальство это любит. местами или удалишь/добавишь или удовлетворитесь уже правой кнопкой мышиДанные сразу после измененийApplication.DisplayAlerts = True и запустите макрос «сцепитьесли» с excel-vba.ru разделитель, например, пробел,
весь столбец пробела, заключенный в конвертах. суммирования» указываем диапазон столбцов. Можно написать из полей «сумма» первый и последний
слэн новые колонки в имеющимися ответами? обновить таблицу результатов.(если у вас в исходных списках.End Sub
выбрав инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«JoinDoubles»-«Выполнить».Источник запятую и т.д.B кавычки. Это разделитель,К сожалению, в Microsoft столбца В, числа диапазон с относительными строк дубликатов, и из выделенного диапазона..: Вот так еще середину — работатьPelenaMikez Excel 2016). Для удобства иЧтобы увидеть результат действия Результат действия макроса
YYYS Нажмите кнопку, затем нажмите который будет вставлен Excel нет встроенного из ячеек которого ссылками.
строка переносится вOption Explicit лучше: тоже перестанет. Вариант: Не совсем так,: Доброго дня.Выделяем первый список и
краткости давайте дадим новой версии кода, отображен на рисунке:: Спасибо огромное Вам!Replace AllCtrl+Shift+Стрелка вправо между соединёнными частями инструмента для выполнения
нужно сложить.Подробнее о видах одном экземпляре с’Option Compare Textn = Cells(Rows.Count, решения — в как Вы хотелиПомогите, пожалуйста, с жмем кнопку нашим спискам имена выделяем всю таблицуВ начале кода мы
С меня причитается)(Заменить все), а, чтобы добавить к
имени. В качестве таких задач. Конечно,В строке формул ссылок читайте в новым значением поляSub UniqSummUniversal() 1).End(xlUp).Row+1 макросе пройтись по — результат получается макросом начинающему.
Из таблицы/диапазона (From Range/Table)Список1 и запускаем макрос: декларируем переменную для DV не вижу затем выделению столбец разделителя Вы можете есть команды сразу видна получившаяся статье «Относительные и

сумма.Dim a(), oDictEternal-Smoke заголовкам таблицы и в последней ячейкеВо вложении образец

planetaexcel.ru

Объединить дубликаты строк, сцепив значения одной колонки (Макросы/Sub)

. На вопрос прои
Читайте также: как объединить хранения показателей счетчика
твои поощрительные реквизиты))Cancel
C использовать любой другойMerge Cells формула. абсолютные ссылки вKukLP, спасибо, пытаюсь
As Object, i: немного не так определить номера нужных.
 из группы, зато
для экспериментов. создание из нашегоСписок2 одинаковые ячейки в цикла. В циклеSerge_007(Отмена), чтобы закрыть. символ, например, запятую.
(Объединить ячейки),Третий способ. Excel» здесь. разобраться. As Long, temp всё-равно получается.
тогда будет работать формуламиЛист «Данные» содержит
списка «умной таблицы», используя строках таблицы. проверяем значения соседних: СцепитьЕсли есть и диалоговое окно.
После этого кликните правойТочно так же ВыMerge & CenterСоздать таблицу сПоставили галочки уHugo As Stringмакрос отлично работает максимально динамично.Mikez компании. Одна компания — соглашаемся:Диспетчер именКак видно на рисунке

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

: Смысл такой -Dim ind As на обычных ячейках,namexls: Вот это сила может быть представленаОткрывается окно редактора запросов,на вкладке
теперь макрос автоматически низа выделенного диапазона.SkyPro

Ctrl+A любому из выделенных из нескольких ячеек,
в центре) и В установить автосумму.«подписи верхней строки» объединяем данные строки Long но не объединяет

: Спасибо. Затестил, работает мысли! ))) Спасибо! несколькими строчками. Отличаются где будет видноФормулы (Formulas — Name объединяет одинаковые значения Если ячейка содержит: Видел, но не, чтобы выделить весь столбцов и в используя при этом прочие, но если
Выбирать фильтром нужные – это название в одну ячейку,

’ind = InputBox(«Sum уже объединенные ячейки все супер какnamexls строчки значением одной загруженные данные и Manager — Create)

 сразу в двух такое же значение пробовал. А эта

excelworld.ru

Объединить повторяющиеся строки excel с суммированием нужных столбцов

 текст в Блокноте, контекстном меню нажмите любой разделитель по Вы выберите 2 дубли. Как сделать столбцов; по этому столбцу Column Number?») (( надо, но только: Здраствуйте, не могу ячейки. имя запроса: столбцах. значит она будет на ядиске уже затемDelete Вашему вкусу. Например, смежные ячейки с сводную таблицу, читайте

«значения левого столбца» и суммам отрабатываетa = Selection.Valueтерпеть не могу
на 5 строках, разобраться в кодеВсё отсортировано иТаблица1После именования, нужная намКлассическая ситуация: у вас объединена. лежала в заначках.Ctrl+C(Удалить). Вы можете соединить целью объединить их, в статье «Сводные — это значит, макрос (а поind = UBound(a, эти объединения, подскажите как формулу заставить макроса, не в строчки одной компании(можно поменять на формула будет выглядеть есть два списка,Дополнительно в начале кодаDAV2014, чтобы скопировать егоОтлично, мы объединили имена адреса из 3 как показано на таблицы Excel». что искать дубли какому столбцу Вы 2) как реализовать? работать до самого моих силах. Помогите идут подряд. свое, если хотите). следующим образом: которые надо слить макроса устанавливаем свойство: Здравствуйте! Нужна помощь в буфер обмена.
из двух столбцов
столбцов в один: снимке экрана ниже:Но мы сделаем в левом первом определяете уникальные -Set oDict =
Guest низа так как плиз с готовым
Задача:Делаем двойной щелчок вНа первый взгляд выглядит в один. Причем «False» для объекта в объединении одинаковыхВернитесь к листу Excel в один!Скопируйте эту формулу воВам будет показано сообщение проще и быстрее. столбце. только по фамилии?), CreateObject(«Scripting.Dictionary»): сначала разобъедините весь строк очень много?

 решением. Пример файла1. Суммировать одну заголовок таблицы (слово жутковато, но, на в исходных списках «DisplayAlerts», чтобы предотвратить ячеек из поля (нажмитеХоть это и все остальные ячейки с предупреждением: Выделяем таблицу, нажимаемНажимаем «ОК». Поучилось он выгружает такие
oDict.CompareMode = 1 диапазон Столбцы местами меняться

CyberForum.ru

Объединение ячеек с одинаковым содержанием

 залил + там колонку всех дублей:Список1 самом деле, все

могут быть как появления предупреждающего сообщение ТОВАР, причем такAlt+Tab потребовало немало усилий столбцав Excel 2013: сочетание клавиш Ctrl так. объединённые данные иFor i =
Eternal-Smoke не будут и же лист как в первую строчку
) и переименовываем на не так страшно.

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

Full NameMerging cells only keeps + T (английская
Если таблицу консолидации делаем суммы в новую

 1 To UBound(a): Если разъединить, то добавляться не будут

должно получиться. Суть
компании, если у
любой другой (например Давайте я разложу
и совпадающие (и непустые ячейки в
одинаковых строк поляB1
Этот способ быстрее, чем
. Пошаговую инструкцию как the upper-left cell
на любой раскладке). на другом листе, книгу.
If IsNumeric(a(i, ind)) получаться пустые ячейки, новые, добавляются только
— нужно объеденить
неё есть повторяющиеся
Люди
эту формулу на
между списками и
программе Excel. В
ТОВАР осталась всегда
и вставьте текст
предыдущий, и не
это делается Вы
value, and discards
Получилась такая сводная
то получится такая
Теперь суммы копируем

 Then которые в дальнейшем строки.

повторяющиеся значения по строчки, в ячейку). Каки именно назвать несколько строк, используя

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

 можете найти в the other values таблица с фильтрами.
таблица. правее (чтоб неIf Not IsEmpty(a(i, не попадут подВсе разобрался. Установил
столбцу C,D,O,S, а «Категория» добавить категории — не важно,

 сочетание клавиш Alt+Enter выходе нужно получить макроса обратно возвращаем

 уникальным значением, а в таблицу. Но он подходит статье Как вставить(В объединённой ячейке Внизу столбца В

Когда мы делали консолидацию затёрлись), разбиваем объёдинённое ind)) Then объединение address = «A1:AI2500″ по столбцам H, из строк-дублей. Через но придуманное название и отступы пробелами, список без дубликатов свойство «True» для
в поле соответсвенно

Переименуйте столбец только для объединения

 одинаковую формулу сразу сохраняется только значение установили автосумму. на другом листе, назад по ячейкам.temp = Trim(a(i,

Guest и все.
I, J, N разделитель |. нужно запомнить, т.к. как мы делали, (повторений):
объекта «DisplayAlerts». Кол-во и СуммаB

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

 то в диалоговомЕсли нужно всё 1)): Это ваше умозаключениеEternal-Smoke суммировать и по2. Пометить строчки-дубли: его придется использовать
например тут:Давайте традиционно рассмотрим несколькоОбратите внимание! Если перед автоматически пересчитывались значения!!!в использовании одинакового разделителя ячейки. ячейки диапазона. Остальные

planetaexcel.ru

Объединение повторов и удаление дубликатов в excel

 Получилось так. окне консолидации в
сделать макросом -If Not oDict.Exists(temp) или «практический опыт»?: Добрый день! я столбцу A произвести у всех строчек потом еще разЛогика тут следующая: способов решения такой выполнением макроса выделить Исходный файл прилагаю!Full Name между ними.Итак, мы объединили имена значения будут потеряны).Как написать данные одновременно строке «создавать связи можно попытаться записать Then Правила гласят, что снова к вам сцепление через запятую компании кроме первой

при импорте второй
Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает

распространенной задачи -

более одного столбца,Заранее благодарю заи удалите столбецВот пример: мы хотим
из двух столбцовв Excel 2010 и
в нескольких ячейках, с исходными данными»
этот процесс рекордером
oDict.Add temp, CStr(a(i, желателен пример реальных
:( без пробелов можно.
в столбец I
таблицы. Объединить две все уникальные элементы
от примитивных «в то в результате
помощь.Last name
объединить все те в один, но
2007: смотрите в статье
поставили галочку. Теперь, :)
ind))
форматов данных вИмеется большой массив
Кол-во строк свыше
прописать текст «удалить»
таблицы в дальнейшем
из первого списка.

лоб» до более будут объединены одинаковые
В данном примере напишем
.
же два столбца
это по-прежнему формула.
The selection contains multiple «Как заполнить ячейки

если в столбце

LowElse

их реальной структуре… данных в одном

 2х тысяч и или «дубль». получится только если Как только они сложных, но изящных.

значения только в код макроса, которыйВ описании этого способа
с именем и Если мы удалим data values. Merging в Excel одновременно». В изменятся цифры,
: Hugo, спасибо огромное!))
oDict.Item(temp) = CStr(—oDict.Item(temp) А как вам столбце, эти данные
периодически добавляются.В картинках: Из
заголовки их столбцов
заканчиваются — начинаетМожно решить задачу самым первом столбце. Чтобы

сможет автоматически найти больше шагов, чем фамилией в один. столбец

into one cell

Данные из нескольких ячеек то и в Вот что называется + a(i, ind)) вариант объединить от повторяются только последовательноhamin вот этого должно совпадают. выдавать ошибку #Н/Д: простым путем - расширить поле действия и объединить все в предыдущем варианте,Выделяем оба столбца, которыеFirst name will keep the можно объединить в этой таблице данные «слона-то я иEnd If непустой до непустой, (т.е. несколько одинаковых: получиться вот это.

Разворачиваем выпадающий список вФормула ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список2); 0)) аналогичным

 руками скопировать элементы макроса следует немного одинаковые ячейки в но поверьте мне необходимо объединить. Дляили upper-left most data одну с помощью пересчитаются автоматически. Проверяем не приметил»! ОченьEnd If предварительно разъединенных?.. значений друг заnamexls
Формулами не знаю левом верхнем углу образом извлекает уникальные обоих списков в
изменить его код. таблице Excel любой или проверьте сами этого выделяем ячейкуLast name

 only символа «амперсанд» (&) – изменим цифру выручили, все работает))End IfZ.

planetaexcel.ru

другом, в других

Источник

bulbashman

Пользователь

Сообщений: 34
Регистрация: 04.05.2013

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

Файл см. ниже [МОДЕРАТОР]

Изменено: bulbashman — 09.05.2013 16:55:42

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	bulbashman, вот скажите мне — зачем в примере 2 200 строк с данными? Вполне хватило бы 20-30.

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	то что надо спасибо, а как это делается-то?

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Читается условие задачи, подбирается алгоритм, а затем пишется макрос, который всё это и сделает))

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Да не обращайте внимания — сам с собой )) А какое из слов не понравилось — макрос? Это такая программа. Самодельная))

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	у меня куча подобных документов некоторые из них по 7-10 листов по 50000 и более строк. как мне такую кнопочку делать, извеняюсь за неграмотность в excel просто до этого времени надобности не было, а тут прижало вот.

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Не имея навыков — Вам будет сложно это сделать самостоятельно. Выход из положения: копируем на первый лист Ваши данные и нажимаем кнопочку.

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

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Ничего не нужно выделять и сортировать — макрос всё сам сделает. Не волнуйтесь — не перепутается))

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	работает. спасибо огромное, выручили.

bulbashman

Пользователь

Сообщений: 34
Регистрация: 04.05.2013

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

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Как понять «абсолютно идентичные»? Они и так располагаются друг за другом.

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	просмотрел 400 строк из них абсолютно одинаковых 4, вариантов то восьмизначное число. а одинаковых немного и располагаются они по всему списку

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	по два, по три, но по всему списку

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Не понимаю… Они и будут по всему списку: одинаковые рядом.

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	вот пример, это уже обработанный макросом, на листе 6 одинаковые строки найденные в первых 400, но их еще найти надо

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Правильно ли я понимаю: после обработки макросом одного листа Вы обрабатываете другой и там ТОЖЕ есть такие строки.

bulbashman

Пользователь

Сообщений: 34
Регистрация: 04.05.2013

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

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	на примере видно, на листе 7 строки обработанные макросом, а на листе 6 одинаковые строки которые я скопировал с листа 7, и они находились в районе первых 400 строк, но искать то их всеравно приходится долго.

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Скопируйте на первый лист ВЕСЬ диапазон и запустите макрос — все идентичные должны собраться в кучу.

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

#24

04.05.2013 15:20:48

Цитата
bulbashman пишет: чтобы идентичные собирались в одном месте в верху листа или в низу

Они не могут быть внизу или вверху — они будут РЯДОМ, там, где их «застанет» сортировка. Ведь именно сортировка их и собирает в кучу.

bulbashman

Пользователь

Сообщений: 34
Регистрация: 04.05.2013

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

Newbie Пользователь Сообщений: 159 Регистрация: 22.12.2012	Извините, вмешаюсь. Юрий М, если я правильно понял ТС, то ему надо, чтобы все дубли были перенесены в верхнюю часть таблицы. Т.е. сначала сгруппированные повторяющиеся значения, а потом все одиночные.

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	да, вроде того. а кто такой ТС?

Пользователь

Сообщений: 6111
Регистрация: 21.12.2012

Win 10, MSO 2013 SP1

#28

04.05.2013 17:18:49

Цитата
bulbashman пишет: … а кто такой ТС?

А самому поискать?!.
—

http://ru.wiktionary.org/wiki/%F2%EE%EF%E8%EA%F1%F2%E0%F0%F2%E5%F0

«Ctrl+S» — достойное завершение ваших гениальных мыслей!..

bulbashman Пользователь Сообщений: 34 Регистрация: 04.05.2013	агромное спасибо. (никогда не увлекался изучением америниканизации русского языка) а на абревиатуру ТС при желании что угодно можно напридумывать.

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	#30 04.05.2013 18:51:14 bulbashman, данные на лист7 получены при помощи макроса? И они расположены неправильно? Тогда подскажите мне номера строк (1-2 пары), в которых находятся одинаковые значения, но при этом они НЕ РЯДОМ.

Источник

Как объединить ячейки с одинаковым значением в Excel

Как выделить одинаковые ячейки группами

Допустим мы имеем список поставщиков:

Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
В поле для ввода формулы вводим следующе значение: =$A2<>$A3
Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.

В результате получаем эффект как отображено на рисунке.

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

Слияние двух списков без дубликатов

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

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

Способ 1. Удаление дубликатов

Можно решить задачу самым простым путем — руками скопировать элементы обоих списков в один и применить потом к полученному набору инструмент Удалить дубликаты с вкладки Данные (Data — Remove Duplicates) :

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

Способ 1а. Сводная таблица

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

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

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

Способ 2. Формула массива

Можно решить проблему формулами. В этом случае пересчет и обновление результатов будет происходить автоматически и мгновенно, сразу после изменений в исходных списках. Для удобства и краткости давайте дадим нашим спискам имена Список1 и Список2, используя Диспетчер имен на вкладке Формулы (Formulas — Name Manager — Create) :

После именования, нужная нам формула будет выглядеть следующим образом:

На первый взгляд выглядит жутковато, но, на самом деле, все не так страшно. Давайте я разложу эту формулу на несколько строк, используя сочетание клавиш Alt+Enter и отступы пробелами, как мы делали, например тут:

Логика тут следующая:

Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает все уникальные элементы из первого списка. Как только они заканчиваются — начинает выдавать ошибку #Н/Д:

Формула ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список2); 0)) аналогичным образом извлекает уникальные элементы из второго списка.

Вложенные друг в друга две функции ЕСЛИОШИБКА реализуют вывод сначала уникальных из списка-1, а потом из списка-2 друг за другом.

Обратите внимание, что это формула массива, т.е. после набора ее нужно ввести в ячейку не обычным Enter , а сочетанием клавиш Ctrl + Shift + Enter и затем скопировать (протянуть) вниз на нижестоящие ячейки с запасом.

В английской версии Excel эта формула выглядит как:

=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1), 0)), INDEX(Список2, MATCH(0, COUNTIF($E$1:E1, Список2), 0))), «»)

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

Способ 3. Power Query

Если в ваших исходных списках большое количество элементов, например, по несколько сотен или тысяч, то вместо медленной формулы массива лучше использовать принципиально другой подход, а именно — инструменты надстройки Power Query. Эта надстройка по умолчанию встроена в Excel 2016. Если у вас Excel 2010 или 2013, то ее можно отдельно скачать и установить (бесплатно).

Алгоритм действий следующий:

Открываем отдельную вкладку установленной надстройки Power Query (если у вас Excel 2010-2013) или просто идем на вкладку Данные (если у вас Excel 2016).
Выделяем первый список и жмем кнопку Из таблицы/диапазона (From Range/Table) . На вопрос про создание из нашего списка «умной таблицы» — соглашаемся:

Открывается окно редактора запросов, где будет видно загруженные данные и имя запроса Таблица1 (можно поменять на свое, если хотите).

Делаем двойной щелчок в заголовок таблицы (слово Список1) и переименовываем на любой другой (например Люди). Каки именно назвать — не важно, но придуманное название нужно запомнить, т.к. его придется использовать потом еще раз при импорте второй таблицы. Объединить две таблицы в дальнейшем получится только если заголовки их столбцов совпадают.

Разворачиваем выпадающий список в левом верхнем углу Закрыть и загрузить и выбираем Закрыть и загрузить в. (Close&Load to. ) :

В следующем диалоговом окне (оно может выглядеть немного по-другому — не пугайтесь) выбираем Только создать подключение (Only create connection) :

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

В окне Excel на вкладке Данные (Data) или на вкладке Power Query выбираем Получить данные — Объединить запросы — Добавить (Get Data — Merge Queries — Append) :

В появившемся диалоговом окне выбираем наши запросы из выпадающих списков:

В итоге получим новый запрос, где два списка будут соединены друг под другом. Осталось удалить дубликаты кнопкой Удалить строки — Удалить дубликаты (Delete Rows — Delete Duplicates) :

Готовый запрос можно переименовать справа на панели параметров, дав ему вменяемое имя (это будет имя таблицы-результата по факту) и все и можно все выгружать на лист командой Закрыть и загрузить (Close&Load) :

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

Объединить по значению

Данная функция является частью надстройки MulTEx

Описание, установка, удаление и обновление
Полный список команд и функций MulTEx
Часто задаваемые вопросы по MulTEx
Скачать MulTEx

Вызов команды:
MulTEx -группа Ячейки/Диапазоны —Ячейки —Объединить по значению

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

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

Делать это руками будет долго и неинтересно, особенно, если в отчете более пары тысяч строк. Как раз для того, чтобы объединить несколько ячеек с одинаковыми значениями, идущими подряд, и предназначена команда «Объединить по значению». Для этого необходимо выделить диапазон ячеек для объединения и вызвать команду: MulTEx -группа Ячейки и Диапазон —Ячейки —Объединить по значению.

Можно объединить либо по строкам, либо по столбцам.
Направление:

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

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

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

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

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

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

1. Удаление повторяющихся значений в Excel (2007+)

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

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

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

2. Использование расширенного фильтра для удаления дубликатов

Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

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

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

3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

4. Использование сводных таблиц для определения повторяющихся значений

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

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

Как объединить две таблицы Excel по частичному совпадению ячеек

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

Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.

Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, “12345” и “12345-новый_суффикс“. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

И что совсем плохо – соответствия могут быть вовсе нечёткими, и “Некоторая компания” в одной таблице может превратиться в “ЗАО «Некоторая Компания»” в другой таблице, а “Новая Компания (бывшая Некоторая Компания)” и “Старая Компания” тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

Выход есть всегда, читайте далее и Вы узнаете решение!

Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP), ПОИСКПОЗ (MATCH), ГПР (HLOOKUP) и так далее.

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

Ключевой столбец в одной из таблиц содержит дополнительные символы

Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.

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

Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper:

Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:
Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):
Даём столбцу имя SKU helper.
Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:

Здесь A2 – это адрес ячейки, из которой мы будем извлекать символы, а 5 – количество символов, которое будет извлечено.

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

Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.

Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

Другие формулы

Извлечь первые Х символов справа: например, 6 символов справа из записи “DSFH-164900”. Формула будет выглядеть так:

=ПРАВСИМВ(A2;6)
=RIGHT(A2,6)
Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “PREFIX_0123_SUFF”. Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:

=ПСТР(A2;8;4)
=MID(A2,8,4)
Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет выглядеть так:

Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – свяжитесь с нами, мы сделаем всё возможное, чтобы помочь Вам.

Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY, где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.

Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.

В ячейке C2 запишем такую формулу:

Здесь A2 – это адрес ячейки, содержащей код группы; символ “–” – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.

Данные в ключевых столбцах не совпадают

Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

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

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

1. Создаём вспомогательную таблицу для поиска.

Создаём новый лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз :-).

В результате мы имеем вот такую таблицу:

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

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

Столбец Supp.SKU заполняется оригинальными кодами производителя.

Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU, соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика. После этого повторяем шаг 2.

3. Переносим данные из таблицы поиска в главную таблицу

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

При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1, используя для поиска соответствий столбец Supp.SKU.

Вот пример обновлённых данных в столбце Wholesale Price:

Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.

Источник

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

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

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

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

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

Объединение и суммирование данных с помощью опции консолидации

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

Другой метод — использовать сводную таблицу и суммировать данные (далее в этом руководстве).

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

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

Ниже приведены шаги для этого:

Скопируйте заголовки исходных данных и вставьте их туда, где вы хотите консолидировать данные.
Выберите ячейку под крайним левым заголовком
Перейдите на вкладку «Данные».
В группе «Инструменты для работы с данными» щелкните значок «Консолидировать».
В диалоговом окне «Консолидировать» выберите «Сумма» в раскрывающемся списке функций (если он еще не выбран по умолчанию).
Щелкните значок выбора диапазона в поле «Ссылка».
Выберите диапазон A2: B9 (данные без заголовков)
Установите флажок в левом столбце.
Нажмите ОК

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

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

Я решил получить СУММУ значений из каждой записи. Вы также можете выбрать другие параметры, такие как «Счетчик» или «Среднее» или «Макс. / Мин.».

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

Объедините и суммируйте данные с помощью сводных таблиц

Сводная таблица — это швейцарский армейский нож для нарезки и нарезки данных в Excel.

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

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

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

Ниже приведены шаги по созданию сводной таблицы:

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

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

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

Ниже приведены шаги для этого:

Щелкните в любом месте области сводной таблицы, и откроется панель сводной таблицы справа.
Перетащите поле Country в область Row.
Перетащите и поместите поле «Продажи» в область «Значения».

Вышеупомянутые шаги суммируют данные и дают вам сумму продаж по всем странам.

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

Это также поможет вам уменьшить размер вашей книги Excel.

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

Надеюсь, вы нашли этот урок полезным!

Источник

Содержание

1 Инструкция
2 Задаем название
3 Автоматическая структуризация
4 Как сортировать данные таблицы?
- 4.1 Цветовое деление
- 4.2 Объединение значений
- 4.3 Упрощаем большую таблицу
5 Настройка группировки
6 Группировка по строкам
7 Группировка по столбцам
8 Создание вложенных групп
9 Разгруппирование
- 9.1 Помогла ли вам эта статья?

Здравствуйте, друзья.

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

Инструкция

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

До встречи!

Рубрика Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Да Нет

Источник

На чтение 7 мин. Просмотров 150 Опубликовано 18.01.2021

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

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

Простой способ:

Создайте еще один столбец, который является объединением всех 12 столбцов, а затем выполните сортировку по этому столбцу.

Более сложный способ:

Используйте VBA, возможно, запускаемый нажатием кнопки.

Объедините 12 столбцов в строка, создавая массив из этих строк.

Сортировка всех внутри, отслеживая, какая строка каждая, а затем выводить так, как вы хотите.

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

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

ответил 18 мая 2011, 22:51

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

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

Пример: Имя Место Раджеш МаркапурРаджеш ВиджаявадаСуреш Миннеаполис Махеш Гунтур Махеш ЧеннаиБриджеш МаркапурСатиш БубанешварСатиш Маркапур pre>

И если вы хотите сгруппировать эти данные на основе имени, как показано ниже

  Excel col ABC Excelrow Имя группы Место 1 1 Раджеш Маркапур 2 1 Раджеш  Виджаявада 3 2 Суреш Миннеаполис 4 3 Махеш Гунтур 5 3 Махеш Ченнаи

Используйте эту формулу, = if (B2 = B3, A1, A2 + 1)

отредактировал 30 мая ’12 в 18:24

Jeremy W

3,5791717 серебряных значков 3535 бронзовых значков

ответ дан 30 мая ’12 в 16:51

Я думаю, OP хочет избежать вручную группируя их. Вы это предлагаете? – Engineer Toast 30 сен. ’15 в 20:29

добавить комментарий |

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

  Пример: Имя Место Раджеш Маркапур Раджеш Виджаявада Суреш Миннеаполис Махеш Гунтур Махеш Ченнаи Бриджеш Маркапур Сатиш Бубанешвар Сатиш Маркапур

И если вы хотите сгруппировать эти данные как ниже

  Excel col ABC Excelrow Имя группы Место 1 1 Раджеш Маркапур 2 1 Раджеш Виджаявада 3 2 Суреш Миннеаполис 4 3 Махеш Гунтур 5 3 Махеш Ченнаи

Используйте эту формулу, = if (B2 = B3, A1, A2 + 1)

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

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

ответ дан 19 мая ’11 в 0:05

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

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

ответил 19 мая 2011 в 7:05

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

Группировка строк в Excel с похожими значениями

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

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

Я пытался изменить макрос, который нашел по https ://superuser.com/questions/867796/excel-macro-to-group-rows-based-on-a-cell-value. Мой текущий макрос …

  Dim LastRow As IntegerLastRow = ActiveSheet.UsedRange.Rows.CountDim StartRow As IntegerStartRow = 8groupBegin = StartRow 'Для первой группы For i = StartRow To LastRow  Если Cells (i, 1) .Value  Cells (i + 1, 1) .Value Then groupEnd = i - 1 Rows (groupBegin & ":" & groupEnd) .Select Selection.Rows.Group groupBegin = i + 1 '  добавление одного, чтобы сохранить первую строку группы End IfNext iRows (groupBegin & ":" & LastRow) .SelectSelection.Rows.GroupActiveSheet.Outline.ShowLevels RowLevels: = 1 'Свернуть все группы

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

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

  Sub Group_Similar_Rows () Dim i As LongDim lRef_Number As LongDim lNumber As LongDim lCount As LongDim lStarting_Row As LongDim lDate_Column As LongDim wks As WorksheetlStarting_Row = 1 'Измените это на начальную строку  your datalDate_Column = 1 'Замените это на индекс столбца вашего набора данных wks = ThisWorkbook.ActiveSheetlRef_Number = wks.Cells (lStarting_Row, lDate_Column) lCount = -1For i = 0 До 100000', если ваша запись данных больше 100000, увеличьте это значение  Если wks.Cells (lStarting_Row + i, lDate_Column) = "" И lCount  lRef_Number = wksRef_Number  .Cells (lStarting_Row + i, lDate_Column) Если i> 1, то lCount = lCount - 1 Конец, если lCount> 0, то lCount = 1 + lCount wks.Rows (lStarting_Row + i - lCount & ":" & lStarting_Row + i - 2  ) .Group End If lCount = 0 End IfNext iEnd Sub

Ниже показано изображение, показывающее результат выполнения введите код:

отредактировано 7 октября ’20 в 13:17

TylerH

18.6k1313 золотых знаков6363 серебряных знака8484 бронзовых знака

ответил 24 августа 2018 в 17:52

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

  Sub Group_Similar_Rows () Dim i As LongDim lRef_Number As LongDim lNumber As LongDim lCount As LongDim  lStarting_Row As LongDim lDate_Column As LongDim wks As WorksheetlStarting_Row = 1 'Измените это на начальную строку вашего datalDate_Column = 1' Замените это на индекс столбца вашего набора данных wks = ThisWorkbook.ActiveSheetlumnRef_Number = wks.Celling_s (lStarting_s (lStarting_Row))  1 Для i = 0 до 100000 ', если количество введенных вами данных превышает 100 000, увеличьте это значение Если wks.Cells (lStarting_Row + i, lDate_Column) = "" И lCount  lRef_Number Тогда lRef_Number = wks.Cells (lStarting_Row + i, lDate_Column) Если i> 1, то lCount = lCount - 1 Конец Если lCount> 0, то lCount = 1  wks.Rows (lStarting_Row + i - lCount & ":" & lStarting_Row + i - 2). Группа End If lCount = 0 End IfNext iEnd Sub

На рисунке ниже показан результат выполнения кода:

Пример моего комментария

  dim i as long, j as long for i = 10 to 1 Step -1, если не ячейки (i,  1) .value = cells (i-1,1) .value, затем rows (i) .insertnext ifor j = от 1 до 10, если cells (j, 1) .value  "", то rows (j) .groupnext j

не тестировалось, но должен дать соответствующий пример.

ответил 24 августа 2018 в 17:11

Пример моего комментария

  dim i as long, j as long for i =  От 10 до 1 Шаг -1, если не ячейки (i, 1) .value = cells (i-1,1) .value, то строки (i) .insertnext ifor j = от 1 до 10, если ячейки (j, 1) .value  "" затем rows (j) .groupnext j

непроверено, но должен дать соответствующий пример.

Источник

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

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

Организовать группировку достаточно просто. Выделите несколько строк и нажмите кнопку Группировать
на вкладке Данные
(см. рис. 1).

Рисунок 1 – Кнопка группировки

Затем укажите тип группировки – по строкам
(см. рис. 2).

Рисунок 2 – Выбор типа группировки

В итоге мы получаем… не то, что нам нужно. Строки товаров объединились в группу, указанную под ними (см. рис. 3). В каталогах обычно сначала идёт заголовок, а потом содержимое.

Рисунок 3 – Группировка строк «вниз»

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

Чтобы группировать строки «вверх» нужно изменить одну настройку. На вкладке Данные
нажмите на маленькую стрелочку в нижнем правом углу раздела Структура
(см. рис. 4).

Рисунок 4 – Кнопка, отвечающая за вывод окна настроек структуры

В открывшемся окне настроек снимите флажок с пункта Итоги в строках под данными
(см. рис. 5) и нажмите кнопку ОК
.

Рисунок 5 – Окно настроек структуры

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

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

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

Рисунок 6 – Многоуровневая структура каталога в Excel

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

Чтобы вывести строки на более высокий уровень иерархии, воспользуйтесь кнопкой Разгруппировать
вкладки Данные
. Полностью избавиться от группировки можно при помощи пункта меню Удалить структуру
(см. рис. 7). Будьте внимательны, отменить действие невозможно!

Рисунок 7 – Снимаем группировку строк

Закрепление областей листа

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

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

Рисунок 8 – Закрепляем строку или столбец

Чтобы снять закрепление, выберите в том же меню пункт Снять закрепление областей
(пункт заменяет собой строку Закрепить области
, если на странице применена «заморозка»).

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

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

Рисунок 9 – Закрепляем область из строк и столбцов

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

Поворот листа (замена строк на столбцы и наоборот)

Представьте такую ситуацию: вы несколько часов работали над набором таблицы в Excel и вдруг поняли, что неверно спроектировали структуру – заголовки столбцов следовало бы расписать по строкам или строки по столбцам (это не имеет значения). Набирать всё вручную заново? Ни за что! В Excel предусмотрена функция, позволяющая осуществить «поворот» листа на 90 градусов, переместив таким образом содержимое строк в столбцы.

Рисунок 10 – Исходная таблица

Итак, мы имеем некоторую таблицу, которую нужно «повернуть» (см. рис. 10).

Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
Копируем их в буфер обмена сочетанием клавиш или любым другим способом.
Переходим к пустому листу или свободному пространству текущего листа. Важное замечание:
вставлять поверх текущих данных нельзя!
Вставляем данные комбинацией клавиш и в меню параметров вставки выбираем вариант Транспонировать
(см. рис. 11). Как вариант, вы можете использовать меню Вставить
со вкладки Главная
(см. рис. 12).

Рисунок 11 – Вставка с транспонированием

Рисунок 12 – Транспонирование из главного меню

Вот и всё, поворот таблицы выполнен (см. рис. 13). При этом форматирование сохраняется, а формулы изменяются в соответствии с новым положением ячеек – никакой рутинной работы не потребуется.

Рисунок 13 – Результат после поворота

Показ формул

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

Нажмите кнопку Показать формулы
на вкладке Формулы
(см. рис. 14), чтобы изменить представление данных на листе (см. рис. 15).

Рисунок 14 – Кнопка «Показать формулы»

Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта

Если вам трудно сориентироваться по адресам ячеек, отображаемым в строке формул, нажмите кнопку Влияющие ячейки
со вкладки Формулы
(см. рис. 14). Зависимости будут показаны стрелками (см. рис. 16). Для использования этой функции сначала следует выделить одну
ячейку.

Рисунок 16 – Зависимости ячейки показаны стрелками

Скрываются зависимости нажатием кнопки Убрать стрелки
.

Перенос строк в ячейках

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

Рисунок 17 – Надписи не помещаются в ячейки

Выделите ячейки с длинными надписями и нажмите кнопку Перенос текста
на Главной
вкладке (см. рис. 18), чтобы перейти к многострочному отображению (см. рис. 19).

Рисунок 18 – Кнопка «Перенос текста»

Рисунок 19 – Многострочное отображение текста

Поворот текста в ячейке

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

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

Сначала создать надпись, а потом её повернуть.
Настроить поворот надписи в ячейке, а потом ввести текст.

Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре
на Главной
вкладке (см. рис. 20) и ввёл обобщающую надпись (см. рис. 21).

Рисунок 20 – Кнопка объединения ячеек

Рисунок 21 – Сначала создаём горизонтальную подпись

Рисунок 22 – Кнопка поворота текста

Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!

Рисунок 23 – Вертикальный текст ячейки

Если есть такое желание, угол поворота текста вы можете задать вручную. В том же списке (см. рис. 22) выберите пункт Формат выравнивания ячейки
и в открывшемся окошке задайте произвольный угол и выравнивание (см. рис. 24).

Рисунок 24 – Задаём произвольный угол поворота текста

Форматирование ячеек по условию

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

Первое, что нужно сделать – выделить ячейки. Далее, на Главной
вкладке нажмите кнопку Условное форматирование
и выберите одну из заготовок (см. рис. 25). Результат будет отображаться на листе сразу, поэтому вам не придётся долго перебирать варианты.

Рисунок 25 – Выбираем заготовку условного форматирования

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

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

Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым

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

Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств

Чтобы удалить условное форматирование ячеек, выделите их и в меню условного форматирования выберите пункт Удалить правила из выделенных ячеек
(см. рис. 28).

Рисунок 28 – Удаляем правила условного форматирования

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

Использование фильтров

Фильтры позволяют быстро находить нужную информацию в большой таблице и представлять её в компактном виде. Например, из длинного списка книг можно выбрать произведения Гоголя, а из прайс-листа компьютерного магазина – процессоры Intel.

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

После того, как ячейки выделены, на вкладке Главная
нажмите кнопку Сортировка и фильтр
и выберите пункт Фильтр
(см. рис. 29).

Рисунок 29 – Создаём фильтры

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

Рисунок 30 – Создаём текстовый фильтр

Рисунок 31 – Создаём фильтр по слову

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

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

Рисунок 32 – Числовые фильтры

Фильтры Excel предоставляют достаточно богатые возможности, сравнимые с выборкой запросом SELECT в системах управления базами данных (СУБД).

Отображение информационных кривых

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

Рисунок 33 – Инфокривая Excel 2010

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

Рисунок 34 – Вставка инфокривой

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

Заключение

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

Графики и диаграммы (5)
Работа с VB проектом (11)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (62)
Разное (37)

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

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

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

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

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ

При группировке элементов Excel задает имена групп по умолчанию, например Группа1(Group1)

для выбранных элементов или Кв-л1(Qtr1)

для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

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

РАЗГРУППИРОВКА ЭЛЕМЕНТОВ

Выделить группу элементов, которые требуется разгруппировать;
На вкладке Параметры(Options)
в группе Группировать(Group)

нажать кнопку Разгруппировать(Ungroup)

(или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать(Ungroup)

).

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

Примечания:

Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.

При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры
— Сервис
— Средства OLAP
) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.

Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

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

Видеоуроки

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

Итак, приступим. Нам понадобиться MS Excel практически любой версии. Я буду использовать MS Excel 2013.

Открываем или создаем новую книгу Excel. Заполняем нашими данными. Затем выделяем нужные строки или столбцы. Находим в верхнем ряду вкладку Данные
. Чуть ниже в поле Структура
нажимаем Группировать
.

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

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

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

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

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

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

Наверно вы заметили слева цифры 1 и 2 – это уровни вложенности. Эти кнопочки служат для быстрого перехода между уровнями. Так как Excel поддерживает группировку данных внутри самой группы, конечно же это очень удобно!

Тем, кто дочитал этот документ до конца, я напоминаю, что — наша профессия. И если вам понадобится современный, функциональный интернет-магазин на 1С-Битрикс, то мы обязательно для вас его сделаем.

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

Рисунок 1 – Кнопка группировки

Затем укажите тип группировки – по строкам
(см. рис. 2).

Рисунок 2 – Выбор типа группировки

Рисунок 3 – Группировка строк «вниз»

Рисунок 4 – Кнопка, отвечающая за вывод окна настроек структуры

Рисунок 5 – Окно настроек структуры

Рисунок 6 – Многоуровневая структура каталога в Excel

Рисунок 7 – Снимаем группировку строк

Закрепление областей листа

Рисунок 8 – Закрепляем строку или столбец

Рисунок 9 – Закрепляем область из строк и столбцов

Поворот листа (замена строк на столбцы и наоборот)

Рисунок 10 – Исходная таблица

Итак, мы имеем некоторую таблицу, которую нужно «повернуть» (см. рис. 10).

Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
Копируем их в буфер обмена сочетанием клавиш или любым другим способом.
Переходим к пустому листу или свободному пространству текущего листа. Важное замечание:
вставлять поверх текущих данных нельзя!
Вставляем данные комбинацией клавиш и в меню параметров вставки выбираем вариант Транспонировать
(см. рис. 11). Как вариант, вы можете использовать меню Вставить
со вкладки Главная
(см. рис. 12).

Рисунок 11 – Вставка с транспонированием

Рисунок 12 – Транспонирование из главного меню

Рисунок 13 – Результат после поворота

Показ формул

Рисунок 14 – Кнопка «Показать формулы»

Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта

Рисунок 16 – Зависимости ячейки показаны стрелками

Скрываются зависимости нажатием кнопки Убрать стрелки
.

Перенос строк в ячейках

Рисунок 17 – Надписи не помещаются в ячейки

Рисунок 18 – Кнопка «Перенос текста»

Рисунок 19 – Многострочное отображение текста

Поворот текста в ячейке

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

Сначала создать надпись, а потом её повернуть.
Настроить поворот надписи в ячейке, а потом ввести текст.

Рисунок 20 – Кнопка объединения ячеек

Рисунок 21 – Сначала создаём горизонтальную подпись

Рисунок 22 – Кнопка поворота текста

Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!

Рисунок 23 – Вертикальный текст ячейки

Рисунок 24 – Задаём произвольный угол поворота текста

Форматирование ячеек по условию

Рисунок 25 – Выбираем заготовку условного форматирования

Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым

Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств

Рисунок 28 – Удаляем правила условного форматирования

Использование фильтров

Рисунок 29 – Создаём фильтры

Рисунок 30 – Создаём текстовый фильтр

Рисунок 31 – Создаём фильтр по слову

Рисунок 32 – Числовые фильтры

Отображение информационных кривых

Рисунок 33 – Инфокривая Excel 2010

Рисунок 34 – Вставка инфокривой

Заключение

Создание структуры листа с помощью групп

Подготовка данных к структурированию

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

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

Располагайте итоговые строки выше или ниже каждой группы детальных строк. Для этого после каждой группы вставьте по пустой строке и в них используйте итоговые функции, например СУММ
или СРЗНАЧ
. Для получения наилучших результатов итоговые строки должны содержать формулы, ссылающиеся на ячейки во всех строках. В показанном на рисунке 8.8 примере имеется строка под сведениями каждой категории товара. Например, данные по продажам лекарств в строках с 5 по 9 суммируются в строке 10. Также можно структурировать данные, в которых итоговые строки содержат описательный текст и иные данные.

Рис. 8.8. Фрагмент окна со структурированными данными

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

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

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

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

Если данные на листе обобщены формулами, которые используют функции, например СУММА, Microsoft Excel позволяет автоматически структурировать данные. Итоговые данные должны располагаться рядом с подробными данными.

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

Создание структуры вручную

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

Для создания структуры листа вручную, необходимо выделить строки или столбцы, которые нужно объединить в одну группу, и дать команду меню Данные =>
Группы и структура =>
Группировать
(рис.8.9).
После этого рядом с детальными данными Одежда
появиться символ структуры. Продолжать выделение и группировку строк или столбцов сведений и выполнение команды Группировать
нужно до тех пор, пока не будут созданы все необходимые уровни структуры.

Рис. 8.9. Фрагмент окна для создания группы Одежда

Настройка структурирования со стилями

Автоматические стили можно применить непосредственно при структурировании или после него. Для структурированных строк Microsoft Excel использует такие стили, как «Уровеньстрока_1» и «Уровеньстрока_2». Для структурированных столбцов Microsoft Excel использует такие стили как «Уровеньстолб_1» и «Уровеньстолб_2». Стили используют полужирное, курсивное и другое форматирование текста, чтобы облегчить различение итоговых строк в данных.

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

Если требуется задать стили структуры для автоматического применения к новой структуре, нужно дать команду меню Данные
=> Группа и структура
=> Настройка
и установить флажок Автоматические стили
. Если при создании структуры не требуется применять автоматические стили, их можно применить после создания структуры. Для этого необходимо выделить данные, к которыми применяются стили, дать команду меню Данные
=> Группа и структура
=> Настройка
в открывшемся диалоговом окне Настройка
(рис. 8.10) установить флажок Автоматические стили
и нажать кнопку Применить стили
. Кнопка Создать
этого диалогового окна создает структуру листа. В результате к итоговым строкам будет применено форматирование (рис.8.11)

Рис.8.10. Применение стилей к структуре

Рис. 8.11. Результат применения автоматических стилей к структуре

Удаление структуры

При удалении структуры никакие данные не удаляются. Для выполнения удаления структуры на нужном листе необходимо дать команду меню Данные =>
Группа и структура
=> Удалить структуру.
Если строки или столбцы все равно останутся скрытыми, то можно просто отобразить скрытые строки или столбцы средствами Excel (выделить видимые заголовки и дать команду меню Формат =>
Строка
или Столбец =>
Отобразить)
.

Выделенные элементы структуры также можно разгруппировать без удаления всей структуры. Для этого, удерживая нажатой SHIFT щелкнуть кнопку или группы, а затем дать команду меню Данные => Группа и структура =>
Разгруппировать
. На рис. 8.12 и 8.13 приведен пример удаления структуры для группы Иванова.

Рис. 8.12 . Выделение элемента структуры Иванова
и выбор команды для разгруппирования

Рис. 8.13. . Вид окна при разгруппированной группе Иванова

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

Источник

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

Настраиваем параметры функции
Группируем данные по строкам
Группируем столбцы
Создаем многоуровневую группировку
Разгруппировываем данные
Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Источник