Как пропустить строку в excel
Здравствуйте, профессионалы!
Помогите, пожалуйста, решить одну проблему.
Нужно, чтобы из одного столбца(в котором есть и пустые строчки, и строчки с текстом) перенести все текстовые значения(значения всех непустых ячеек) в другой, рядом стоящий столбец.
Простыми словами — в другом столбце расположить все значения, пропуская пустые строчки.
(я в примере в третьем столбце сам напечатал желаемый результат — для наглядности желаемого результата)
Заранее огромное Вам спасибо!
Прикрепленные файлы
- вопрос.xlsx (10.16 КБ)
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
20.02.2014 04:42:06
Проблема ещё заключается в том, что ячейки эти(строки) будут не совсем пустые. Они будут с формулами.
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
20.02.2014 04:45:02
можно присвоить (по формуле) этим ячейкам(строкам) значение 0.
тогда нужно будет пропускать все строки с 0.
Как тогда это сделать?
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
20.02.2014 04:54:58
формула эта(вышеприведенная) не моя. но насколько я смог её понять — надо в ней заменить ЕПУСТО на ЕСЛИ <>0.
потому что, формула эта рабочая. проверил на другой таблице.
просто надо немного изменить условия(исключить все ячейки с 0). для того чтобы это сделать — моих мозгов гуманитарных не хватит.
Пользователь
Сообщений: 2443 Регистрация: 21.12.2012
20.02.2014 05:03:57
Здравствуйте.
В примере нет ячеек с формулами.
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
20.02.2014 05:09:14
Это просто таблица сделанная для образца.
я не могу скинуть оригинал — там книга из 12 листов — боюсь придется очень долго объяснять что и где.
для удобства сделал табличку отдельно.
вот ещё раз — уже измененная.
ПОЛУЧАЕТСЯ ЧТО НУЖНО ПРОПУСКАТЬ НЕ ПУСТЫЕ ЯЧЕЙКИ, А ТЕ, ЗНАЧЕНИЕ КОТОРЫХ = 0.
Прикрепленные файлы
- вопрос.xlsx (12.06 КБ)
Пользователь
Сообщений: 2443 Регистрация: 21.12.2012
20.02.2014 05:09:25
Прикрепленные файлы
- Ответ.xls (39 КБ)
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
20.02.2014 05:12:06
Да, вы правильно меня поняли! спасибо Вам за это решение!
А можно этот процесс автоматизировать? чтобы всё происходило само? (как по формулам)
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
20.02.2014 05:13:18
просто мне нужно будет эту формулу знать, чтобы я мог её переносить в другую — уже рабочую таблицу книги.
Пользователь
Сообщений: 2443 Регистрация: 21.12.2012
20.02.2014 05:19:38
| Цитата |
|---|
| А можно этот процесс автоматизировать? |
По какому событию автоматизировать?
| Цитата |
|---|
| нужно будет эту формулу знать |
Нет там формулы. Правая кнопка мыши на ярлыке листа — Исходный текст — там макрос проживает Sub www().
P.S. Какая боль, какая боль, Макрос — Формула — 1 :0 ! (ковер на песню)
Изменено: k61 — 20.02.2014 05:23:33 ( Добавил песню. )
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
21.02.2014 00:56:21
мне нужно чтобы всё считалось само — без кнопок.
<=ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);"";СТРОКА($A$2:$A$14));СТРОКА(A2)));"" >
эта формула работает. в ней знающему человеку нужно изменить условие с ПУСТОЙ СТРОКИ НА 0.
Помогите, Пожалуйста.
Пользователь
Сообщений: 3289 Регистрация: 22.12.2012
Win7 MSO 2013
21.02.2014 01:01:26
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$73;НАИМЕНЬШИЙ(ЕСЛИ(ЕТЕКСТ($A$2:$A$73);СТРОКА($A$2:$A$73)-1);СТРОКА(C1)));"")
формула массива. формулы массива очень массивные! чем Вас макрос не устроил?
второй файл ВПР + доп.столбец
Прикрепленные файлы
- 18999.xlsx (13.13 КБ)
- 19201.xlsx (12.19 КБ)
Изменено: Nic70y — 21.02.2014 01:08:37 ( 2-й файл )
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
21.02.2014 04:15:06
Nic70y ,СПАСИБО ВАМ.
Да, это именно то, что мне нужно. все считается само, и без моего вмешательства (пусть и путем сложных, длинных и нелепых формул)!
Спасибо огромное! Вы мне очень помогли!
Приятно иметь дело с профессионалами!
И огромное спасибо этому прекрасному сайту! Это просто мечта для человека, несведущего в этих сложностях!
Пользователь
Сообщений: 2443 Регистрация: 21.12.2012
21.02.2014 04:23:48
| Цитата |
|---|
| А можно этот процесс автоматизировать? |
Вопрос остался.
Пользователь
Сообщений: 18 Регистрация: 16.02.2014
21.02.2014 07:03:18
k61 ,почему? формула считает всё, даже при изменении значений в ячейках.
никаких действий от пользователя не нужно.
| Цитата |
|---|
| dmitryalexeevich пишет: все считается само, и без моего вмешательства |
процесс автоматизирован.
Пользователь
Сообщений: 6111 Регистрация: 21.12.2012
Win 10, MSO 2013 SP1
21.02.2014 07:39:10
Off — Улыбнуло:
| Цитата |
|---|
| пусть и путем сложных, длинных и нелепых формул! |
«Ctrl+S» — достойное завершение ваших гениальных мыслей. 😉
Пользователь
Сообщений: 75 Регистрация: 04.08.2016
05.08.2016 23:02:16
Доброго времени суток, а как сделать так чтобы было тоже самое только сразу было 3 столбца. то есть примере 19201 были еще данные в других столбцах, данные параллельные,если есть с столбце A то в этой же строке есть и в B, создавать ошибку пож каждый столбец не вариант, я хочу уменьшить размер файла а строк 56 000, из них основная масса бесполезных нулей, если бы взять написать макрос, не обязательно должен быть автоматизированным, проще нажать кнопку обновить. у меня имеется целая куча однотипных диапазонов на разных листах, из них практически все пустые. так как формула рассчитывает там ноль. даже не ноль а пусто ***** «» *****, проще говоря мне из A1xA5600 на разных листах, причем на одном A25xA5600, на другом и CW1xCW15600? выбрать не пустые ячейки и выбросить их все на одном листе
Пользователь
Сообщений: 10514 Регистрация: 21.12.2012
06.08.2016 00:41:15
| Цитата |
|---|
| greatsky написал: а как сделать так чтобы было тоже самое |
Для начала создать свой пример, что есть, что надо и желательно как можно ближе к оригиналу.
Ну и, возможно, создать новую тему (впрочем, ваш вопрос пока сходен с темой, может новую тему и не надо)
Пользователь
Сообщений: 75 Регистрация: 04.08.2016
06.08.2016 10:51:36
Это не коммерческий проект, делаю для себя расчет, есть таблицы Данные1. ДанныеN (около сорока) на разных листах и разбросаны хаотично, но все данные одинаковые (по шапке) , имеются две задачи, первая собрать данные из таблиц (Данные1. ДанныеN) и вывести на лист «результат», поочередно, пропускай пустые строки (пустые они условно, там и нули и пусто и «», так как они тоже рассчитываются экселем, выводя результат то 0, то пусто) вторая задача стоит тоже самое, но выполнить группировку по Крепежная фурнитура, чтобы считало итоговое количество, но можно было развернуть и посмотреть в каких ящиках, самая главная задача это первая, так как таблиц много, и результат простого копирования Лист1!B1=Листс2!B3 является большой вес файлом и я жду минут пять покуда он откроется, можно макросом, я создам кнопку обновить, да и самое важное, что данные могут меняться, поэтому просто удалять из тех огромных таблиц, данные не вариант
Прикрепленные файлы
- Пример.xlsx (97.43 КБ)
Изменено: greatsky — 06.08.2016 10:54:21
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
06.08.2016 12:27:04
greatsky, а в чём сложность? Пишите макрос. Алгоритм следующий:
1) цикл по листам книги
2) если имя листа начинается с «Данные», то обрабатываем его
3) поиск ячейки, которая содержит фразу «Крепежная фурнитура»
4) к найденной ячейке применяем команду «выделить область»
5) цикл по строкам, со второй строки, т.к. это шапка
6) если яч. в первом столбце диапазона не равна нулю и длина больше нуля, то эту строку диапазона копируем на лист «Результат», ниже последней заполненной ячейки
Всё.
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 75 Регистрация: 04.08.2016
06.08.2016 12:52:48
Такой сложный макрос я не осилю никогда. все что я могу готовый макрос чуть подредактировать, по логике — это максимум
Изменено: greatsky — 06.08.2016 12:55:31
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
06.08.2016 12:58:19
greatsky, уверяю Вас — осилите. На форуме примеров масса, по каждому пункту можете в любом поисковике задать вопрос «вба как . «, где . — Ваш вопрос. Если лень самостоятельно разобраться, тогда Вам помогут в ветке «работа», либо обождите того у кого настроение волонтёра. (у меня отнюдь не волонтёрское настроение на данный момент)
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 75 Регистрация: 04.08.2016
06.08.2016 13:00:41
Да, конечно, вы не чем мне не обязаны) подождем, я пробовал — мои познания в VB 0 я пробовал и чуть попроще и без помощи не обошелся
Изменено: greatsky — 06.08.2016 13:02:23
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
06.08.2016 13:26:50
Скрытый текст
Sub jjj_consolidata() Dim sh As Worksheet Dim Rng As Range Dim rws As Range Dim rngResult As Range Dim lCnt As Long With ActiveWorkbook With .Sheets("Результат") Set rngResult = .Cells(.Rows.Count, 2).End(xlUp) End With ' .Sheets("Результат") lCnt = 0 For Each sh In .Sheets If sh.Name Like "Данные*" Then Set Rng = sh.Cells.Find(What:="Крепежная фурнитура", After:=ActiveCell, LookIn:= _ xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False, SearchFormat:=False) If Not Rng Is Nothing Then Set Rng = Rng.CurrentRegion If Rng.Rows.Count > 1 Then Set Rng = Rng.Resize(Rng.Rows.Count - 1).Offset(1) For Each rws In Rng.Rows If rws.Cells(1, 1).Value <> 0 And Len(rws.Cells(1, 1).Value) > 0 Then lCnt = lCnt + 1 rws.Copy rngResult.Offset(lCnt).Resize(, rws.Columns.Count) End If Next rws End If End If End If Next sh End With ' ActiveWorkbook End Sub
Для критиков:
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 75 Регистрация: 04.08.2016
06.08.2016 13:30:48
Спасибо, сейчас буду пробовать, отпишусь как смогу встроить, так как я и встраивать буду целый день)
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
06.08.2016 13:31:47
greatsky, погодите. Ошибка в макросе. Исправляю.
ЗЫ Не, показалось.
Изменено: JayBhagavan — 06.08.2016 13:32:24
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 75 Регистрация: 04.08.2016
06.08.2016 13:33:43
| Цитата |
|---|
| JayBhagavan написал: End With ‘ ActiveWorkbook |
предпоследняя строчка туда скопировать другой лист? (как бы цикл)
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
06.08.2016 13:35:04
greatsky, With к циклам не имеет никакого отношения.
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 75 Регистрация: 04.08.2016
06.08.2016 13:48:38
В примере работает круто, но в примере я все упростил, если на листе еще будут другие данные. он их не подтянет? и как еще ограничить ему, к примеру на листе идут
| Крепежная фурнитура | Кол. | Един. изм. | № ящика |
Примечания |
заполненные и пустые ячейки
| Другая фурнитура | Кол. | Един. изм. | № ящика |
Примечания |
заполненные и пустые ячейки
они параллельны. как сделать так чтобы не брал другую фурнитуру
И еще вопрос, я так понял все листы должны быть одного имени, ну или начинаться с одного имени можно так ДанныеВН1, ДанныеВК5, он все после звездочки воспримет правильно? верно?
Изменено: greatsky — 06.08.2016 13:53:23
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
06.08.2016 13:56:52
| Цитата |
|---|
| 2.3. Приложите файл(ы) с примером (общим весом не более 100 Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе. |
Вставка пустых строк между заполненными ячейками
Здравствуйте! Я не знаю VBA, из примеров в интернете пытался собрать макрос. Мне нужно между заполненными ячейками вставить пустую строку. В моем примере попытка вставить пустые ячейки. Выдает ошибку, что функция не определена. Вот код. Подскажите пожалуйста в чем ошибка. Спасибо!
1 2 3 4 5 6 7
Sub Макрос2() Cell(1, 1).Select Do While IsEmpty(ActiveCell) = False Range(ActiveCell).Insert SendKeys "" Loop End Sub
Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Вставка пустых строк
Всем доброго дня! Сразу скажу, что я профан и требуется помощь специалистов! Имею следующую задачу.
Вставка пустых строк при запросе во временную таблицу
Добрый день. Подскажите как этот код можно переписать на postgresql Вставляться должны пустые.
Вставка пустых ячеек (строк) после ячейки с определённым значением
Здравствуйте! Хотелось бы попросить помощи по моей проблеме. Есть таблица, в ней всё в один.
Подсчитать количество пустых строк в текстовом файле. Сформировать новый файл, в котором пустых строк нет
Задано произвольный текстовый файл. Подсчитать количество пустых строк в этом файле. Сформировать.
Как закрасить линии строк между ячейками stringgring?
Вот как-то так должно выглядеть.
1184 / 473 / 203
Регистрация: 30.01.2020
Сообщений: 1,169
Сообщение от ram876 
Подскажите пожалуйста в чем ошибка.
Для того чтобы подсказать, глядя на неработающий макрос, что вам нужно сделать, чтобы он заработал так, как вам надо, надо знать что же вам надо. Фраза
Сообщение от ram876
Мне нужно между заполненными ячейками вставить пустую строку.
не говорит ващениачём. Поскольку надо знать -пытаетесь ли вы вставить эту пустую строку таким макросом в процессе заполнения листа. Тогда лучше вставить в ваш макрос просто увеличение номера текущей строки. Либо у вас уже есть кем-то сторонним заполненный лист, и вам надо вставить ровно через строчку. Тогда надо перебирать строки с шагом 2. Либо вы перебираете строчки, ищете информацию и найдя пытаетесь вставить перед/после найденной строки? Тогда лучше считать их в массив, перебирать строки массива, и сдвигать их к хвосту, увеличивая размерность. А затем весь массив слить обратно на лист. Могут быть и ещё варианты, которые здесь не перечислены, и в каждом можно подсказать что лучше использовать, чобы и не тормозить процесс, и облегчить решение. Нужно только более полно описывать собственно задачу, а не невнятный неработающий кусок с просьбой сделать так, чтобы он работал так как надо, не говоря, как именно надо.
А данный макрос — просто бред.
5 челленджей, справиться с которыми поможет Excel
Вызубрили теорию, а она так и не пригодилась — с такой ситуацией регулярно сталкиваются все студенты. Но это не относится к выпускникам курса Changellenge >> ToolKit Plus, считает Полина Вериго, одна из его участниц. Спустя пару недель после прохождения курса Полина попала на стажировку в Mars и сразу столкнулась с несколькими челленджами. В статье она делится функциями Excel, которые помогли ей их решить.
Полина Вериго — студентка МГИМО МИД РФ, выпускница Школы Changellenge >> и онлайн-курса Changellenge >> ToolKit 2018.
Челлендж № 1: привести таблицу в порядок
Любой проект начинается с того, что в Excel нужно перенести данные. Но сразу после импорта работать с таблицами невозможно: с числами копируются лишние пробелы, точки, и программа не считывает информацию. Решить проблему помогут функции TRIM и SUBSTITUTE.
TRIM (СЖПРОБЕЛЫ) убирает все пробелы в текстовой строке кроме одиночных пробелов между строками. Синтаксис элементарен:
=TRIM(ссылка на ячейку, из которой нужно удалить ненужные пробелы)
SUBSTITITE (ПОДСТАВИТЬ) заменяет любой старый текст на новый. Работает эта функция так:
=SUBSTITUTE(ячейка;“старый текст“;“новый текст”)
Если ваш Excel с русскими настройками, то точку нужно заменить на запятую, чтобы программа распознала десятичный разделитель.
Для экономии времени две функции можно объединить в одну. Мы вводим в новую ячейку =TRIM(SUBSTITUTE(A1;”.”;”,”)) и растягиваем ее на весь столбец. Теперь перед нами аккуратные числа, готовые к дальнейшей работе.
Челлендж № 2: построить точечную диаграмму (scatter plot) на основе случайного массива данных
«Для презентации отдела нужно срочно создать точечный график. Данные рандомные, главное, в промежутке от 60 до 100. И чтобы их было больше 100. У тебя пара минут», — такое задание я получила от своего руководителя. Чтобы выполнить его, пришлось вспомнить функцию RAND (СЛЧИС). Она позволяет выполнить эту задачу за двадцать секунд и выглядит очень просто:
=RAND() (да, никаких аргументов).
Эта функция всегда возвращает случайное число, большее или равное 0, но меньше 1. Если нужно задать какой-либо диапазон, то формула выглядит так:
=RAND()*(верхняя граница-нижняя граница)+нижняя граница
В моем случае =RAND()*(100-60)+60. Протягиваем ее на 100+ строчек вниз и вставляем график.
Эту же задачу можно выполнить еще быстрее, воспользовавшись формулой RANDBETWEEN (СЛУЧМЕЖДУ):
=RANDBETWEEN(нижняя граница; верхняя граница)
В отличие от обычного RAND эта формула возвращает лишь целые числа, что не всегда подходит по условиям задачи.
Челлендж № 3: найти сумму чисел в столбце со скрытыми строками и ошибками
Иногда в большом массиве данных попадаются скрытые строки и ошибки. Из-за объема найти их вручную практически невозможно. В таком случае стандартная функция SUM, которая ищет сумму диапазона ячеек, работать не будет.
В этом случае можно использовать AGGREGATE (АГРЕГАТ). Она помогает применять любые функции, не боясь скрытых строк и ошибок. Ее синтаксис выглядит так:
=AGGREGATE([номер функции];[параметр (номер, который определяет, какие значения следует пропускать)];[массив, для которого надо вычислить значение];[второй массив, который требуется для определенных функций (необязательный)])
Чтобы узнать номер функции и параметр, при вводе AGGREGATE нажмите на ее название: на экране появится справка Excel со всей необходимой информацией.
Вернемся к задаче. Нам нужна сумма значений в столбце А, и мы хотим пропустить все скрытые строки и значения ошибок. Формула будет выглядеть так:
=AGGREGATE(9;7;A:А), где 9 – номер функции СУММ, а 7 – номер параметра «Пропуск скрытых строк и значений ошибок».
Челлендж № 4: спрогнозировать число пользователей, привлеченных на страницу, исходя из заданного линейного тренда
Передо мной стояла задача: спрогнозировать число пользователей, привлеченных на сайт компании в условиях сохранения текущих настроек рекламы. У меня были данные по количеству посетителей за последние 4 месяца, а требовалось найти их число на конец мая.
Здесь на помощь пришла функция FORECAST.LINEAR (ПРЕДСКАЗ.ЛИНЕЙН). Она предсказывает значение y по известному значению х. Синтаксис у нее такой:
=FORECAST.LINEAR([x (то значение, по которому делаем прогноз)]; [известные значения y]; [известные значения x])
В нашем кейсе х=5 (месяц май, на который нужно сделать прогноз), известные значения х — все предыдущие месяцы, по которым мы знаем количество привлеченных пользователей, а известные значения y — количество пользователей за каждый из прошлых месяцев. Вводим формулу:
=FORECAST.LINEAR(5;C2:C5;B2:B5) и получаем результат. Маховик времени в действии!
Челлендж № 5: найти и перенести в новую таблицу нужные данные из другой таблицы
Обычно для этой задачи используют функцию VLOOKUP (ВПР). Она довольна проста, но у нее есть ряд ограничений. Например, столбец, по которому мы ищем данные, должен быть всегда левее, чем столбец с исходными данными. Вдобавок эту формулу нельзя скопировать в соседний столбец или добавить новый столбец в исходный массив — формула сломается. А при работе с большими массивами риск слишком велик.
Поэтому стоит познакомиться с функцией INDEX(MATCH) (ИНДЕКС(ПОИСКПОЗ)). Она состоит из двух отдельных функций:
- INDEX возвращает нужное значение по заданным параметрам поиска: массив, где искать, номер строки и номер столбца. Синтаксис у нее такой:
=INDEX([массив, где искать];[номер строки];[номер столбца])
- MATCH сообщает порядковый номер нужного нам значения в выбранном столбце или строке. Работает она так:
=MATCH([что искать (ссылка на ячейку или значение в кавычках)]; [где искать (строка или столбец, в котором ищем это значение)]; [как искать (0, 1 или -1)]
0 означает точное совпадение, 1 – поиск первого значения, которое больше искомого или равно ему (значения должны быть отсортированы по возрастанию), -1 – поиск первого значения, которое меньше искомого или равно ему (значения должны быть отсортированы по убыванию).
Эти функции хороши сами по себе. Однако если их совместить, получится максимально полезный и универсальный инструмент для поиска данных.
=INDEX(столбец в исходной таблице, откуда нужно извлечь нужное значение); MATCH([другое значение в новой таблице, по которому мы ищем нужное нам значение];[столбец в исходной таблице, где находится это другое значение];0))
Разберем ее на примере. Нам даны имена продавцов и объем их продаж за три месяца. Предположим, нужно найти данные по работе только пяти продавцов: Б, Ж, К, О, С. В таблице может быть несколько тысяч строк, поэтому сделать все вручную не получится. Чтобы найти, сколько продавец Б продал в январе, вводим в соответствующую ячейку:
=INDEX(C3:C26;MATCH(H4;B3:B26;0)), где
- C3:C26 – столбец в исходной таблице со всеми продажами в январе;
- Н4 – ячейка в новой таблице, по которой мы ищем продажи, то есть продавец Б;
- В3:В26 – столбец в исходной таблице, в котором указаны все продавцы;
- 0 – параметр, означающий поиск точного значения.
Протягиваем формулу вниз и вправо и получаем готовую таблицу.
После стажировки, тесно связанной с работой в Excel, я уверена, что эти пять функций пригодятся вам в работе.
Теги
Получите карьерную поддержку
Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.
Подписаться на карьерную рассылку
Подписывайтесь на рассылку и получайте карьерные советы — от выбора индустрии и компании до лайфхаков по самоорганизации и развитию коммуникативных навыков.
Коммуникационный дизайнер
Разработчик технологий
Стажер в отдел права
Стажер-маркетолог
Стажер группы ценообразования
Менеджер по повышению эффективности
Стажер в команду по охране труда
Стажер продюсер
Объединяйте ячейки без потери данных: в столбцах, в строках или в одну ячейку
Объединение столбцов, строк или нескольких ячеек в Excel можно выполнить разными способами. Команда «Объединить и поместить в центре» (Merge and Center) полезна для создания аккуратных и легко читаемых таблиц, но она сохраняет содержимое только верхней левой ячейки, а остальные значения удаляются. Использование оператора «&» или функции СЦЕПИТЬ (CONCATENATE) помогает объединить содержимое ячеек в одну строку, но не объединяет ячейки и может занять немало времени.
Инструмент «Объединить данные» помогает объединить ячейки в больших таблицах без потери данных:
Объединение содержимого ячеек одновременно с объединением ячеек
Объединение ячеек по столбцам, по строкам или в одну ячейку
Любой разделитель значений: пробел, новая строка, вертикальная черта, т.д.
Объединение данных с применением разных разделителей в одной цепочке
Перед началом работы добавьте инструмент «Объединить данные» в Excel
«Объединить данные» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Объединить данные и объединить ячейки в Excel – в чём разница
При объединении нескольких ячеек (merge), вы создаёте одну большую ячейку на их месте.
При объединении данных нескольких ячеек (combine), вы соединяете, сцепляете значения этих ячеек в одну текстовую строку.
XLTools позволяет объединить как сами ячейки, так и данные в этих ячейках — другими словами, надстройка позволяет объединить ячейки без потери данных.
Внимание: значения в объединённой цепочке сохраняют формат ячеек, напр. 10%, 12-мар-2015, $40, т.д. Если объединяемые ячейки содержат ссылки или формулы, то в объединённой цепочке будет использован результат их вычислений.
Как объединить ячейки в одну ячейку без потери данных
Выберете диапазон смежных ячеек для объединения Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: в одну ячейку .
Задайте подходящий разделитель.
Установите флажок Объединить ячейки после слияния данных .
Пропустить пустые ячейки , если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
Переносить текст по словам , чтобы переносить текст в ячейке.
Нажмите ОК Готово. Диапазон объединён в одну ячейку, все значения объединены в этой ячейке.
Как объединить ячейки в строках без потери данных (или объединить столбцы)
Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: по строкам .
Задайте подходящий разделитель.
Установите флажок Объединить ячейки после слияния данных .
Пропустить пустые ячейки , если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
Переносить текст по словам , чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все ячейки и их значения объединены по строкам.
Как объединить ячейки в столбцах без потери данных (или объединить строки)
Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: по столбцам .
Задайте подходящий разделитель.
Установите флажок Объединить ячейки после слияния данных .
Пропустить пустые ячейки , если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённых строках.
Переносить текст по словам , чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все ячейки и их значения объединены по столбцам.
Как собрать данные ячеек в одну ячейку (без объединения ячеек)
Иногда может быть необходимо собрать значения нескольких ячеек в одной ячейке, без объединения самих ячеек. По сути, вам нужно скопировать значения из каждой ячейки диапазона и собрать их в единую текстовую цепочку в одной ячейке. С надстройкой вы можете сделать это в секунды:
Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: в одну ячейку .
Задайте подходящий разделитель.
Очистить содержимое ячеек после слияния , чтобы убрать значения из исходных ячеек. Сравните на скриншоте результат с применением этой опции и без.
Пропустить пустые ячейки , если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
Переносить текст по словам , чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все значения объединены в одной ячейке.
Как объединить данные Excel по строкам (или собрать столбцы в один)
Иногда бывает нужно собрать несколько столбцов в один, без объединения самих ячеек. По сути, вам необходимо объединить значения из этих столбцов по строкам. Это легко можно выполнить с надстройкой:
Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: по строкам .
Задайте подходящий разделитель.
Очистить содержимое ячеек после слияния , чтобы убрать значения из исходных ячеек. Сравните на скриншоте результат с применением этой опции и без.
Пропустить пустые ячейки , если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
Переносить текст по словам , чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все значения объединены по строкам в ячейках слева.
Как объединить данные Excel по столбцам (или собрать строки в одну)
Иногда бывает нужно собрать несколько строк в одну, без объединения самих ячеек. По сути, вам необходимо объединить значения из этих строк по столбцам. Это легко можно выполнить с надстройкой:
Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: по столбцам .
Задайте подходящий разделитель.
Очистить содержимое ячеек после слияния , чтобы убрать значения из исходных ячеек. Сравните на скриншоте результат с применением этой опции и без.
Пропустить пустые ячейки , если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
Переносить текст по словам , чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все значения объединены по столбцам в ячейках сверху.
Для чего пропускать пустые ячейки при объединении ячеек и их данных
Если ваши исходные данные содержат пустые ячейки, рекомендуем не учитывать их при объединении ячеек и их данных. Это помогает избежать пустых значений в объединённых текстовых строках. Сравните результат с применением опции «Пропустить пустые ячейки» и без неё:
Как задать свой знак разделителя при объединении ячеек и их данных
Надстройка XLTools «Объединить данные» позволяет задать любой разделитель меужду значениями в объединённой текстовой строке:
Запятая и пробел
Точка с запятой
Точка с запятой и пробел
Новая строка
Или выберите Другой и задайте свой знак разделителя, напр., вертикальную черту
Как применить несколько разных разделителей в одной объединённой цепочке
Предположим, у вас есть несколько столбцов, и вам нужно объединить ячейки, используя разные разделители. Просто разбейте эту задачу на шаги:
Выделите столбцы Нажмите кнопку Объединить данные .
Укажите Объединить данные: по строкам .
Задайте запятую в качестве разделителя.
Отметьте опции Объединить ячейки… , Очистить содержимое… , Пропустить пустые… .
ОК Готово, имена и фамилии объединены в цепочки типа «John Smith».
Выделите столбцы Нажмите кнопку Объединить данные .
Укажите Объединить данные: по строкам .
Задайте Другой разделитель и напечатайте «: order #».
Отметьте опции Объединить ячейки… , Очистить содержимое… , Пропустить пустые… .
ОК Готово, все данные объединены в цепочки типа «John Smith: order #123» .
Any questions or suggestions?
Екатерина Здравствуйте! Возникла проблема при пользовании программой. При проверке обновлений выдало ошибку: Исключение чтения манифеста: возможно манифест неправильный или файл не может быть открыт. Попробовала переустановить программу, повторилось тоже самое ((((((((((((((( Подскажите что делать? Очень нужна программа.
Reply Ноябрь 26, 2015 at 11:32
Maria Balobanova Екатерина, при загрузке надстройки возникла временная проблема с сертификатом. Мы её уже устранили. Попробуйте, пожалуйста, переустановить XLTools. Детали отправила по email. Спасибо, что пользуетесь нашей надстройкой =)