Советы и лайфхаки по работе с Excel

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

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

Нам нужно объединить данные из 2-ух колонок в одну (к примеру, для последующих расчетов и т.п.). Мы можем начать мыслить про разные формулы, макросы, но существует обычный и стильный метод.

Изберите ячейки во 2-ой колонке и нажмите по правой кнопочке мыши, изберите команду Копировать (либо нажмите Ctrl + C ).

Изберите ячейки в первой колонке, щелкните правой клавишей мыши и изберите команду Особая вставка . Вы сможете также употреблять «резвые» клавиши Ctrl + Alt + C . В открывшемся окне характеристик Особая вставка изберите галочку Пропускать пустые ячейки и нажмите ОК

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

Как снять пароль в Excel? Три рабочих метода снятия пароля.

Метод 1. (Используем программку) Отыскиваем в поисковике и загружаем программку

Как собрать несколько книжек Excel в одну?

К примеру, мы имеем много рабочих книжек Excel, и мы желаем

Что такое макрос и куда его вставлять в Excel?

Нам в работе время от времени не хватает обычных способностей Эксель и приходится напрягать

Как добавить абзац в ячейке Excel?

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

Как посчитать количество неповторимых значений в колонке Excel?

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

Отсортировать неповторимые значения в Excel

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

Как создать и добавить выпадающий перечень в Excel?

В Excel есть одна увлекательная изюминка, а конкретно возможность вводить

Как стремительно перейти в подходящий лист книжки Excel?

Почти все сталкивались с файлами Ексель, в которых сотворено большущее количество

Три метода, как объединить столбцы в Excel

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

Методы объединения

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

Интересно почитать: Выровнять ширину столбцов в excel

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

Метод 1: через контекстное меню

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

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

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

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

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

Метод 2: при помощи инструмента на панели

2-ой способ, как объединить столбцы в Excel, производится на порядок резвее, потому что действий нужно провести намного меньше:

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

Интересно почитать: Excel группировка столбцов

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

Метод 3: с помощью специальной функции

На данный момент будет поведано, как объединить столбцы в Excel без потери данных. А выполняться это при помощи функции «Сцепить»:

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

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

Выделите объединенные данные.
Установите курсор в нижнем правом углу ячейки.
Зажмите ЛКМ и потяните вниз.
Все другие строчки также слились.
Выделите приобретенные результаты.
Скопируйте его.
Выделите часть таблицы, которую желаете поменять.
Вставьте приобретенные данные.

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

Объединение 3-х и наиболее списков в EXCEL

Пусть дано 5 списков и они все разной длины (см. Файл примера ).

Задачка

Объединим все значения из 5 списков в один. Задачка объединения 2-х списков решена в одноименной статье .

Решение1 (Обычное)

Объединенный спискок будем строить на базе функции СМЕЩ()

=СМЕЩ(заголовок первого перечня;Номер элемента в перечне;Номер списка-1)

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

Эта таблица дозволит нам сравнить каждой позиции объединенного перечня Номер начального перечня:

выражение СТРОКА()-СТРОКА($H$11) генерирует последовательность 1; 2; 3; 4.
функция ГПР() — горизонтальный аналог ВПР() выбирает по позиции первого элемента всякого перечня в объединенном перечне номер начального перечня.

Интересно почитать: Сравнение двух столбцов в excel и вывод несовпавших данных

Номер перечня является смещением по столбцам в формуле на базе СМЕЩ()

Подробности можно поглядеть в файле примера на листе Пример2.

Решение2 (сложное, с формулами массива и именованными формулами)

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

выделите, спектр A2:E10 ;
на вкладке Формулы в группе Определенные имена изберите команду Присвоить имя ;
в поле Имя введите: Диапазон_Списков ;
удостоверьтесь, что в поле Спектр введена формула =пример!$A$2:$E$10
нажмите ОК.

Для вывода всех значений из 5 списков в один столбец будем употреблять функцию ИНДЕКС() . Эта функция будет поочередно выводить значения из всех ячеек спектра Диапазон_Списков на основании их номера столбца и номера строчки. Осталось лишь найти адреса не пустых ячеек.

Сделаем массив пар (номер столбца; номер строчки) для всех ячеек спектра. Для этого применим трюк: значения пары будем хранить в виде обыденного числа, но сформировывать его будем по определенному правилу: правая часть числа будет содержать номер строчки (для этого выделяется два разряда, т.е. наибольшая длина перечня быть может 99), а левая часть числа будет содержать номер столбца. К примеру, число 512 будет означать: 5-й столбец, 12-ая строчка. Естественно, по мере необходимости можно прирастить разрядность для хранения номеров строк (формула из файла примера дозволяет столбцам иметь до 9999 строк).

На техническом уровне осуществим это так. Поначалу определим номер столбца и строчки левого верхнего угла нашего Диапазона_Списков . Для этого сделайте две именованные формулы =МИН(СТОЛБЕЦ(Диапазон_Списков)) и =МИН(СТРОКА(Диапазон_Списков))

Сделайте еще одну именованную формулу Адреса: =ЕСЛИ(ЕПУСТО(Диапазон_Списков);»»; —((СТОЛБЕЦ(Диапазон_Списков)-Мин_Столбец+1)&ВЫБОР(ДЛСТР(СТРОКА(Диапазон_Списков)-Мин_Строка+1);»0″;»»)&СТРОКА(Диапазон_Списков)-Мин_Строка+1))

Эта формула возвратит массив адресов из нашего спектра <101;201;301;401;501: 102;202;302;"";502: 103;203;303;"";503: 10…>. Заместо адресов пустых ячеек в массиве содержатся значения Пустой текст («»). Номера столбцов и строк отсчитываются от левой верхней ячейки Диапазона_список .

Заключительный шаг. Формируем объединенный перечень. Запишем в ячейке последующую формулу: =ЕСЛИОШИБКА(ИНДЕКС(Диапазон_Списков; —ПРАВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));2);—ЛЕВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)); ДЛСТР(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)))-2));»»)

Функция НАИМЕНЬШИЙ() будет поочередно извлекать все числа, содержащие адреса ячеек. Функция ПРАВСИМВ() будет извлекать из этих чисел номер строчки, а функция ЛЕВСИМВ() – номер столбца. Эти две функции возвращают текстовые значения, потому применим двойное отрицание (—), чтоб конвертировать текст в число (см. статью Преобразование чисел из текстового формата в числовой (часть 1) ).