000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как объединить столбцы с помощью Alt + Enter в Excel?

Преобразовать «столбцы в строки» в Excel через VBA

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

«Столбцы в строки» с помощью VBA

Для тех, кто не сталкивался с такой «необходимостью», я приведу небольшой пример с двумя таблицами:

Столбцы в строки - пример

Левая таблица — исходная. В ней все данные хранятся в столбцах. Из такой таблицы крайне неудобно будет строить сводную: Excel каждый столбец будет рассматривать как отдельное поле, которое по отдельности надо будет перетаскивать при разработки структуры сводной таблицы. Это не будет большой проблемой, если таких столбцов несколько. А если 12, как в примере? Или 50, или еще больше? Вот в таком случае, максимально удобно будет использовать таблицу, у которой столбцы преобразованы в строки.

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

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

«Столбцы в строки»: разработка формы

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

Выделять диапазоны (исходный и конечный) мы будем с помощью элемента управления RefEdit (если данный элемент управления отсутствует на панели инструментов, то его необходимо добавить: щелкните по рабочей форме, затем нажмите «Tools» — «Additional Controls» и поставьте галочку рядом с «RefEdit.Ctrl»). Вводить необходимое количество обрабатываемых столбцов мы будем через элемент TextBox (его переименуем в Clmn_Count для удобства). Также добавляем CommandButton, то есть кнопку, для запуска самого основного макроса. Получается что-то вроде этого:

Столбцы в строки: рабочая форма

По нажатию на кнопку «Выполнить преобразование» будет выполняться следующий код:

Переменным s1 и s2 присваиваем значения полей RefEdit1 и RefEdit2 соответственно. Затем эти переменные проверяются: если они пустые, то уведомляем пользователя и выходим из процедуры, а если с ними все в порядке — преобразовываем их в диапазоны r1 и r2 соответственно. Проверяем второй диапазон на количество столбцов и строк: если этот диапазон не является ячейкой (т.е. строк и столбцов больше 1) — тоже уведомляем пользователя и завершаем процедуру.

Читайте так же:
Как найти повторяющиеся или уникальные значения в двух столбцах на двух листах?

Если все проверки пройдены — запускаем поочередно три процедуры:

  1. Prepare — активация кода оптимизации
  2. Collapse Clmn_Count.Value, r1, r2 — запуск основной обработки, преобразующей столбцы в строки
  3. Ended — деактивация кода оптимизации

Давайте посмотрим на каждый из них.

Prepare

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

Collapse Clmn_Count.Value, r1, r2

Главный макрос, который выполняет только нужную нам функцию: преобразует столбцы в строки. Он имеет три аргумента: количество столбцов для преобразования (через TextBox, который мы называем Clmn_Count), и два диапазона (исходный массив и целевая ячейка). Сам макрос довольно сложен для понимания, но я постараюсь кратко объяснить, как он работает, на небольшом примере:

Столбцы в строки: таблица для примера

  1. Сначала копируем заголовки, которые у нас статичны (в нашем примере это заголовок «Клиент» и «SKU»)
  2. Затем берем новую строку исходной таблицы и выполняем определенный алгоритм 3 раза (то есть столько раз, сколько у нас столбцов):
    • Копируем строку с наименованиями «Клиента» и «SKU»
    • Затем делаем сдвиг на Z столбцов вправо (изначально Z = 1 и наращивается, в нашем случае, до 3)
    • Копируем заголовок данного столбца (сначала это «Январь»), а затем соответствующее значение
    • Наращиваем Z (для сдвига на следующий месяц), и повторяем все сначала
  3. Затем спускаемся на новую строку исходной таблицы и повторяем пункт 2 снова, до тех пор, пока не переберем все строки исходной таблицы:

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

Ended

Включение функций Excel, которые мы отключили с помощью макроса Подготовка

Заключение

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

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

Переместить столбцы в Excel — стандартный путь

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

  1. Щелкните правой кнопкой мыши заголовок столбца, в котором вы хотите разместить столбец, и выберите Вставить. Это создаст пустой столбец, куда можно поместить столбец, который вы хотите переместить.
Читайте так же:
Как отключить контекстное меню на указанном листе или всей книге в Excel?

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

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

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

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

Как вставлять строки в разных местах

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

  1. Удерживая клавишу «CTRL», выберите нужные строки по одной (например, шестая, тридцатая, сотая). Теперь выполните способ 5: перейдите в меню и выберите Вставить строки. При вставке автоматически определяются абсолютные и относительные ссылки.

Несмежные строки: выделение

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

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

Также очень легко удалить неправильно вставленные строки. Выделите строку полностью (наведя курсор на номер), а затем выберите опцию «Удалить».

C TRL+Z можно использовать для отмены предыдущих действий. Как вы можете видеть, строки таблицы заполнены разными цветами. Выберите эти номера, затем удерживайте клавишу Ctrl при добавлении новых строк:

В примере ниже появились три новые линии; обратите внимание, что выделенные линии движутся вниз, а новые линии будут над ними. На этом у меня все! Удачи!

Для начала давайте объясню о чем вообще идёт речь. Что вообще значит закрепление столбцов, строк и областей в Excel? Для чего это может понадобиться?
Представьте огромную таблицу в несколько десятков, а может и сотен строк и столбцов. При прокрутке таких длинных таблиц часто пользователь забывает названия первых столбцов или строк, шапки таблицы. В таких ситуациях и помогает закрепление строк и столбцов.
Закреплённые строки и столбцы при прокрутке всегда остаются на экране, а остальные строки и столбцы по прежнему будут прокручиваться и скрываться («заезжать»). Для примера посмотрите на меню данного сайта, которое при прокрутке вниз не скрывается, а цепляется за верхний край. Подобным принципом работает эта функция и в Excel’e.
Теперь давайте перейдём непосредственно к инструкции.

Читайте так же:
Как начать нумерацию строк с 0 вместо 1 в Excel?

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

  1. В меню Excel перейдите на вкладку Вид.
  2. В разделе Окно нажмите на пункт Закрепить области.
  3. Для закрепления только верхней строки нажмите на пункт Закрепить верхнюю строку. alt=»закрепление первой строки в Excel» width=»300″ height=»83″ />
  4. Для закрепления нескольких строк нужно выделить (кликнуть по порядковому номеру строки слева ) следующую за ними строку и выбрать в меню пункт Закрепить области. Например: для закрепления первых 5 строк, нужно выделить шестую строку и потом нажать на пункт Закрепить области. alt=»закрепление нескольких строк» width=»300″ height=»77″ />

Как закрепить столбец

закрепление нескольких столбцов

  1. Перейдите на вкладку Вид в меню Excel. Если вы хотите пристыковать только первый столбец, нажмите Пристыковать первый столбец.
  2. Если вы хотите состыковать больше столбцов, выберите (щелкните букву столбца вверху) следующий столбец и выберите в меню пункт Dock Ranges. Например: Если вы хотите пристыковать первые два столбца, выберите третий столбец, а затем нажмите кнопку Пристыковать области.

Как закрепить область в excel

Для крепления секции из нескольких столбцов и строк требуется:

  1. Щелкните левой кнопкой мыши, чтобы выбрать ячейку, которая должна быть первой ячейкой для движущейся (прокручивающейся) части таблицы.
  2. Перейдите на вкладку Показать в меню Excel.
  3. Нажмите кнопку Исправить области в разделе Окно и выберите Исправить области в подменю.

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

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

Как открепить?

снятие закрепления

  1. Перейдите на вкладку Вид в меню Excel.
  2. В разделе Окно нажмите Корректировка диапазонов.
  3. В открывшемся меню нажмите Удалить диапазоны.

Обратите внимание, что «Unmount Zones» появляется только в том случае, если в открытом документе имеется хотя бы одна фиксированная зона.

Дельта-символ в Excel

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

  1. Вставка дельты из параметра вставки
  2. Вставка дельты с помощью сочетания клавиш
  3. Вставка дельты путем изменения названия шрифта
  4. Вставка дельты с помощью формулы CHAR
  5. Вставка дельты с помощью функции автоматического преобразования Excel
  6. Индивидуальное форматирование чисел с использованием символа дельты.
Читайте так же:
Как ограничить количество десятичных знаков в формуле в Excel?

Символ дельты в Excel

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

Метод №1 — Вставить дельту из параметра вставки

Excel имеет в своем арсенале множество символов. Нажав кнопку Вставить, мы можем быстро вставить символ дельты. Вставьте символ DELTA с помощью вкладки «Вставка» Excel.

  • Шаг 1: Перейдите на вкладку «Вставка». На вкладке «Вставка» найдите «Символы» и «Символ».

Дельта-символ Пример 1

  • Шаг 2: Теперь мы увидим окно с символами.

Дельта-символ Пример 1-1

  • Шаг 3: Выберите «Греческий и коптский» из выпадающего списка «Подгруппа форм» в Excel. При выборе этой опции вы увидите символ Delta.

Дельта-символ Пример 1-2

Шаг 4: Затем нажмите «Insert», чтобы вставить символ дельты.

Дельта-символ Пример 1-3

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

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

Дельта-символ Пример 1-4

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

Дельта-символ Пример 1-5

Метод № 2 — Вставить дельту с помощью сочетания клавиш

Быстрый способ вставить символ Delta с помощью сочетания клавиш — использовать сочетание клавиш в excel. Чтобы вставить второй символ дельты, то есть «треугольник с залитым цветом», нам нужно использовать следующие ключи.

  • Выберите ячейку Excel, в которую нужно вставить символ дельты. Удерживая нажатой клавишу ALT, нажмите 30 на цифровой клавиатуре.

Метод №3 — Вставить дельту, изменив имя шрифта

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

Дельта-символ Пример 3

Просто измените название шрифта на «Symbol», чтобы получить дельта-символ без центральной заливки.

Дельта-символ Пример 1-6

Таким образом, мы получили символ дельты. В результате значение «D» является символом дельты в имени шрифта «Symbol».

Метод №4 — Вставить дельту по формуле CHAR

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

Затем нужно открыть функцию Char в целевой ячейке.

Дельта-символ Пример 4

Введите число 114 и нажмите Enter, чтобы получить символ «r».

Дельта-символ Пример 4-2

Если шрифт заменен на «Wings 3», результатом будет дельта-символ без заливки.

Дельта-символ Пример 4-3

Вы можете получить символ с дельта-заполнением, изменив число Char со 114 на 112.

Дельта-символ Пример 4-4

Итак, число 112 в функции Char — это дельта с заливкой.

Метод № 5 — Вставить дельту с помощью функции автозамены Excel

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

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

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

Шаг 1: Скопируйте отсюда символ дельта Δ

Шаг 2: Теперь в Excel щелкните Файл >>> Параметры.

Дельта-символ Пример 5

Шаг 3: В следующем окне перейдите к опциям «Орфография» и «Автокорректура».

Символ дельты Пример 5-1

Шаг 4: Теперь, в окне ниже, вставьте скопированный символ дельты в поле «C».

Символ дельты Пример 5-2

Шаг 5: В поле «Заменить» введите желаемый текст.

Символ дельты Пример 5-3

Шаг 6: Теперь нажмите кнопку «Добавить», чтобы добавить параметр «Автозаполнение», затем нажмите «OK» и закройте окно.

Пример дельты 5-4

Давайте вернемся на рабочий лист и напечатаем «ABCD» заглавными буквами, затем нажмем Enter и посмотрим, какое значение мы получим в ячейке А1.

В отличие от «ABCD», теперь мы имеем символ дельта.

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

Метод № 6 — Пользовательское форматирование чисел с помощью символа дельты

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

Пример дельты 6

Найдите дисперсионный столбец для этого набора данных.

Шаг 1: Чтобы найти столбец дисперсии, вставьте формулу как Отклонение = Фактическое — Целевое

Пример дельты 5-5

Нажмите Ctrl + 1, чтобы открыть опции для данных по отклонению.

Пример дельты 5-6

Шаг 3: В окне формата перейдите в Настроить и введите Общие Δ.

Пример дельты 6-1

Шаг 4: Нажмите OK, и мы получим символ дельты с цифрами.

Пример дельты 6-2

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

Шаг 5: Выберите столбец отклонений и откройте диалог форматирования, затем выберите «Пользовательский» и примените форматирование, как показано на рисунке ниже

Пример дельты 6-3

Шаг 6: Нажмите OK, чтобы применить форматирование. Теперь мы увидим увеличивающийся символ дельты для положительной дисперсии и уменьшающийся символ дельты для отрицательной дисперсии.

Пример дельты 6-4

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector