Подстановка значений в Microsoft Excel подразумевает замену одной части текста на другую с выводом результата в отдельной ячейке. Это избавит вас от необходимости самостоятельно вносить одни и те же коррективы в большое количество полей. Осуществляется подобная манипуляция с использованием всего одной функции, но далее я расскажу еще и о ее модернизации для тех, кто исправляет ошибочный импорт, переводя текстовые значения в числовые.
Когда это может понадобиться?
Отвечу на самый главный вопрос по теме: «Когда может понадобиться подстановка значений?». Выполнение этой операции в первую очередь подразумевает замену любых символов. Например, вы импортировали диапазон значений из другой программы, где для разделения дробной части вместо запятой используется точка. Соответственно, Excel подумает, что это текстовые значения, и откажется использовать их при создании функций.
Вы можете исправить все это вручную, удаляя старые знаки и добавляя новые, после чего проверить настройки формата каждой ячейки. Однако это делается долго и неудобно. Проще создать новый столбец с использованием функций. ПОДСТАВИТЬ и ЗНАЧЕН. Так вы создадите столбец с правильными данными, которые можно использовать для любых целей, включая копирование и удаление всего лишнего.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Использование функции ПОДСТАВИТЬ
Для начала разберемся с тем, как работает синтаксис основной функции – ПОДСТАВИТЬ. Объявим ее и рассмотрим каждый аргумент, чтобы при дальнейшем использовании у вас не возникло никаких проблем.
-
Для начала выберите необходимую клетку, нажмите по ней левой кнопкой мыши для активации и объявите функцию =ПОДСТАВИТЬ().
-
В скобках напишите номер ячейки, с которой нужно взять исходный текст.
-
Поставьте точку с запятой для отделения аргументов, в кавычках напишите символ или несколько символов, от которых хотите избавиться.
-
Снова отделите аргумент и в кавычках напишите новый символ для замены.
-
Это вся формула, поэтому можете нажать Enter и посмотреть, что получилось в итоге.
Для наглядности оставляю используемую мной формулу целиком, чтобы вы могли редактировать ее под себя и не вводить каждый символ вручную. Это поможет значительно сэкономить время.
=ПОДСТАВИТЬ(A2;".";",")
Добавление функции ЗНАЧЕН
Теперь модернизируем формулу, чтобы в итоге получить числовое значение, а не текст, как в исходном варианте. Для этого понадобится функция ЗНАЧЕН, которую просто нужно вписать в строку, поставив дополнительные кавычки.
-
Выполните редактирование, чтобы в итоге получить примерно такой результат: =ЗНАЧЕН(ПОДСТАВИТЬ(A2;».»;»,»)).
-
Нажмите Enter для применения изменений и обратите внимание на то, что теперь числа отображаются справа в ячейке, что говорит об успешной смене формата с текстового на числовой.
-
Остается только растянуть формулу на все остальные клетки, чтобы завершить замену в массиве данных. Зажмите правый нижний угол ячейки и потяните вниз.
-
Посмотрите на таблицу и удалите лишнюю информацию, которая после преобразования больше не понадобится.
Работа с массивами данных в Microsoft Excel часто заставляет юзера вносить правки в текст, что особенно актуально при импорте дробных чисел. Теперь вы узнали, что решить проблему можно при помощи всего одной формулы, состоящей из двух функций. Используйте это, чтобы оптимизировать взаимодействие с электронной таблицей.
Предположим, есть ячейка C3 со значением:
123
И есть ячейка D3 со значением:
789.15
Необходимо в ячейке A3 получить строку:
Количество 123, Цена 789.15 руб.
Решение:
В Excel прописать составную строку в ячейку можно только через формулу. Поэтому в ячейке A3 первым символом должен идти знак равенства =.
Далее просто пишутся строковые константы в кавычках, и объединяются (конкатенируются) со значениями из других ячеек через знак &:
=»Количество » & C3 & «, Цена » & C4 & » руб.»
Если необходимо сделать перевод строки, можно воспользоваться функцией СИМВОЛ(10), вот так:
=»Количество » & C3 & «, » & СИМВОЛ(10) & «Цена » & C4 & » руб.»
В следующем подходе используется обходной путь, описанный здесь и здесь, чтобы включить функцию рабочего листа, определенную в VBA, для установки значения другой ячейки.
Пользовательская функция хранит в глобальных переменных адрес целевой ячейки и значение, в которое должна быть установлена эта ячейка. Затем макрос, запускаемый при пересчете листа, считывает глобальные переменные и устанавливает целевую ячейку на указанное значение.
Использовать пользовательскую функцию просто:
=SetCellValue(target_cell, value)
где target_cell — это строковая ссылка на ячейку на рабочем листе (например, «A1») или выражение, которое оценивает такую ссылку. Это включает в себя выражение, такое как =B14 где значение B14 равно «A1». Функция может использоваться в любом допустимом выражении.
SetCellValue возвращает 1, если значение успешно записано в целевую ячейку, и 0 в противном случае. Любое предыдущее содержимое целевой ячейки перезаписывается.
Необходимы три куска кода:
- код, определяющий сам
SetCellValue - макрос, который запускается событием вычисления листа; а также
- служебная функция
IsCellAddressчтобы убедиться, чтоtarget_cellявляется действительным адресом ячейки.
Код для функции SetCellValue
Этот код необходимо вставить в стандартный модуль, вставленный в рабочую книгу. Модуль можно вставить через меню редактора Visual Basic, к которому можно перейти, выбрав Visual Basic на вкладке « Developer » на ленте.
Option Explicit
Public triggerIt As Boolean
Public theTarget As String
Public theValue As Variant
Function SetCellValue(aCellAddress As String, aValue As Variant) As Long
If (IsCellAddress(aCellAddress)) And _
(Replace(Application.Caller.Address, "$", "") <> _
Replace(UCase(aCellAddress), "$", "")) Then
triggerIt = True
theTarget = aCellAddress
theValue = aValue
SetCellValue = 1
Else
triggerIt = False
SetCellValue = 0
End If
End Function
Worksheet_Calculate Macro Code
Этот код должен быть включен в код, специфичный для рабочего листа, в котором вы будете использовать SetCellValue . Самый простой способ сделать это — щелкнуть правой кнопкой мыши вкладку листа в Home представлении, выбрать « View Code , а затем вставить код в появившуюся панель редактора.
Private Sub Worksheet_Calculate()
If Not triggerIt Then
Exit Sub
End If
triggerIt = False
On Error GoTo CleanUp
Application.EnableEvents = False
Range(theTarget).Value = theValue
CleanUp:
Application.EnableEvents = True
Application.Calculate
End Sub
Код для функции IsCellAddress
Этот код может быть вставлен в тот же модуль, что и код SetCellValue .
Function IsCellAddress(aValue As Variant) As Boolean
IsCellAddress = False
Dim rng As Range ' Input is valid cell reference if it can be
On Error GoTo GetOut ' assigned to range variable
Set rng = Range(aValue)
On Error GoTo 0
Dim colonPos As Long 'convert single cell "range" address to
colonPos = InStr(aValue, ":") 'single cell reference ("A1:A1" -> "A1")
If (colonPos <> 0) Then
If (Left(aValue, colonPos - 1) = _
Right(aValue, Len(aValue) - colonPos)) Then
aValue = Left(aValue, colonPos - 1)
End If
End If
If (rng.Rows.Count = 1) And _
(rng.Columns.Count = 1) And _
(InStr(aValue, "!") = 0) And _
(InStr(aValue, ":") = 0) Then
IsCellAddress = True
End If 'must be single cell address in this worksheet
Exit Function
GetOut:
End Function
|
Здравствуйте, форумчане. |
|
|
заархивировал файл, а то больше 100 |
|
|
Юрий М  Модератор Сообщений: 60570 Контакты см. в профиле |
Алексей, а для чего вообще такой большой файл в качестве примера? Ведь достаточно на примере всего трёх ячеек показать. Посмотрите в сторону =ЕСЛИ() |
|
В сторону ЕСЛИ смотрел, но формула, коротрую я написал не работает. |
|
|
Serge  Пользователь Сообщений: 11308 |
{quote}{login=Алексей.Ю.}{date=24.07.2010 11:23}{thema=}{post}В сторону ЕСЛИ смотрел, но формула, коротрую я написал не работает.{/post}{/quote}Хреново. |
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
Так покажите нам этот маленький файл с тремя цветным ячейками и формулой. Не нужно весь табель. |
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
Неверное, проблемы с созданием маленького файла |
|
все не так. В том и смысл, что в 3й яч. с результатом, не должно быть формул, она должна быть открыта для редактирования. Я8 — никогда не пичется так. В файле-примере четко видно, что «Я» в одной яч., а «8» под ней в другой яч. — это тоже должна делать формула, т.е. записывать результат в обе яч. |
|
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
Читаем: «Т.Е. если в зелёной ячейке ИСТИНА, то формула в синей ячейке запишет результат (Я8) в ораньжевые ячейки. Если ЛОЖЬ, то в ораньжевых ячейках пусто». |
|
Это я писал. (Я8) — это же условное написание. Смотрите на листе «Х». Поэтому и дал в качестве примера табель, что бы не возникало лишних воросов. |
|
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
Послушайте, Алексей, уже два человека просили Вас показать маленький файл-пример, чтобы показать принцип построения формулы с условием ЕСЛИ. Тянуть клещами из Вас этот файл я больше не буду. До свидания. |
|
Serge Пользователь Сообщений: 11308 |
|
|
ZVI  Пользователь Сообщений: 4328 |
{quote}{login=Алексей.Ю.}{date=24.07.2010 08:09}{thema=ФОРМУЛА для записи результата в ‘третью’ ячейку.}{post}…Не знаю возможно ли это, задумка…{/post}{/quote} |
|
{quote}{login=ZVI}{date=25.07.2010 12:35}{thema=Re: ФОРМУЛА для записи результата в ‘третью’ ячейку.}{post}{quote}{login=Алексей.Ю.}{date=24.07.2010 08:09}{thema=ФОРМУЛА для записи результата в ‘третью’ ячейку.}{post}…Не знаю возможно ли это, задумка…{/post}{/quote} Спасибо за ответ. А то я уже все пальцы об клаву стер. Жаль что Эксель этого не умеет, а в макросах я ни бум-бум. |
|
|
Добрый вечер, У ZVI Фотография!! |
|
|
ZVI Пользователь Сообщений: 4328 |
{quote}{login=Саша. }{date=25.07.2010 12:49}{thema=}{post}Добрый вечер, У ZVI Фотография!! http://www.planetaexcel.ru/forum.php?thread_id=17262&page_forum=2 Присоединяйтесь! |
|
ZVI Пользователь Сообщений: 4328 |
{quote}{login=Алексей.Ю.}{date=25.07.2010 12:49}{thema=Re: ZVI}{post}Жаль что Эксель этого не умеет, а в макросах я ни бум-бум.{/post}{/quote} См. комментарии ячеек в приложенном примере. В пример можно скопировать любые другие листы, при этом механизм копирования будет работать и в них, если придерживаться описанного выше правила создания имен. Имена задаются из меню: Вставка — Имя — Присвоить В макросах разбираться в таком случае не потребуется, но посмотреть код можно, нажав на кнопку «VBA-код» или с помощью Alt-F11. |
|
{quote}{login=ZVI}{date=25.07.2010 02:52}{thema=}{post}{quote}{login=Алексей.Ю.}{date=25.07.2010 12:49}{thema=Re: ZVI}{post}Жаль что Эксель этого не умеет, а в макросах я ни бум-бум.{/post}{/quote} Изучил. Применил к табелю. Выявил много недостатковнеудобств такого макроса. НО всеже при заполнении табеля с десятком человек это будет не плохо. (для бухгалтеров) Файл в качестве примера с описаниями. |
|
|
ZVI Пользователь Сообщений: 4328 |
Алексей, хорошо, что потрудились, но если бы Вы сразу написали, что по пересчету листа необходимо автоматически скопировались значения: |
|
А не могли бы вы сделать так, чтобы атоматический пересчет не касался этих диапазонов D18:R19,D20:R21,T18:AI19,T20:AI21.Но в Экселе он будет включен, для пересчета остальных ячеек. Цены бы не было макросу. |
|
|
{quote}{login=Алексей.Ю.}{date=27.07.2010 09:21}{thema=RE: ZVI}{post}А не могли бы вы сделать так, чтобы атоматический пересчет не касался этих диапазонов D18:R19,D20:R21,T18:AI19,T20:AI21.Но в Экселе он будет включен, для пересчета остальных ячеек. Цены бы не было макросу.{/post}{/quote} Я хотел сказать, что для этих диапазонов он будет вручную. |
|
|
ZVI Пользователь Сообщений: 4328 |
По нажатию кнопки подойдет? |
|
{quote}{login=ZVI}{date=27.07.2010 11:33}{thema=}{post}По нажатию кнопки подойдет?{/post}{/quote} то, что доктор прописал:). Спасибо. |
|
|
Есть один нюанс, если я вас ещё не замучил. Можно ли сделать так, чтобы копирование из скрытых ячеек не производилось. |
|
|
Юрий М Модератор Сообщений: 60570 Контакты см. в профиле |
По моим данным закончил работу и подбирается к дому. |
|
Igor67  Пользователь Сообщений: 3726 |
#30 28.07.2010 22:40:44 Попробуйте добавить Ваш_Диапазон.SpecialCells(xlCellTypeVisible).Copy |
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование ПОДСТАВИТЬ в Microsoft Excel.
Описание
Подставляет значение аргумента «нов_текст» вместо значения аргумента «стар_текст» в текстовой строке. Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ используется, когда нужно заменить любой текст начиная с определенной позиции.
Синтаксис
ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])
Аргументы функции ПОДСТАВИТЬ описаны ниже.
-
Текст Обязательный. Текст или ссылка на ячейку, содержащую текст, в котором подставляются знаки.
-
Стар_текст Обязательный. Заменяемый текст.
-
Нов_текст Обязательный. Текст, на который заменяется «стар_текст».
-
Номер_вхождения Необязательный. Определяет, какое вхождение фрагмента «стар_текст» нужно заменить фрагментом «нов_текст». Если этот аргумент определен, то заменяется только заданное вхождение фрагмента «стар_текст». В противном случае все вхождения фрагмента «стар_текст» в тексте заменяются фрагментом «нов_текст».
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Данные |
||
|---|---|---|
|
Сведения о продажах |
< |
|
|
Квартал 1, 2008 г. |
< |
|
|
Квартал 1, 2011 г. |
< |
|
|
Формула |
Описание (результат) |
Результат |
|
=ПОДСТАВИТЬ(A2; «продажах»; «ценах») |
Замена «ценах» на «продажах» («Сведения о ценах») |
Сведения о ценах |
|
=ПОДСТАВИТЬ(A3; «1»; «2»; 1) |
Замена первого экземпляра «1» на «2» («Квартал 2, 2008 г.») |
Квартал 2, 2008 г. |
|
=ПОДСТАВИТЬ(A4; «1»; «2»; 3) |
Замена третьего экземпляра «1» на «2» («Квартал 1, 2012 г.») |
Квартал 1, 2012 г. |
См. также
Функция ЗАМЕНИТЬ, ЗАМЕНИТЬБ
Функция СЖПРОБЕЛЫ
Нужна дополнительная помощь?
Перемещение и копирование ячеек и их содержимого
Используйте команды Вырезать, Копировать и Вставить, чтобы перемещать или копировать содержимое ячеек. Так можно копировать определенную часть содержимого или свойств ячеек. Например, скопировать вычисленное по формуле значение, не копируя саму формулу, или скопировать только формулу.
Ячейки в Excel перемещаются и копируются вместе с формулами, итоговыми значениями, форматом и примечаниями.
В Excel ячейки можно перемещать путем перетаскивания или с помощью команд Вырезать и Вставить.
Перемещение ячеек путем перетаскивания
-
Выделите ячейки или диапазон ячеек, которые требуется переместить или скопировать.
-
Наведите указатель мыши на границу выделенной области.
-
Когда указатель примет вид указателя перемещения
, перетащите ячейку или диапазон ячеек в другое место.
, перетащите ячейку или диапазон ячеек в другое место.Перемещение ячеек с помощью команд «Вырезать» и «Вставить»
-
Выделите ячейку или диапазон ячеек.
-
На вкладке Главная нажмите кнопку Вырезать
или нажмите клавиши CTRL+X. -
Выделите ячейку, в которую вы хотите переместить данные.
-
На вкладке Главная нажмите кнопку Вставить
или нажмите клавиши CTRL+V.
или нажмите клавиши CTRL+X.
или нажмите клавиши CTRL+V.Копирование ячеек с помощью копирования и вставки
-
Выделите ячейку или диапазон ячеек.
-
Нажмите кнопку Копировать или клавиши CTRL+C.
-
Нажмите кнопку Вставить или клавиши CTRL+V.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Перемещение или копирование ячеек, строк и столбцов
Нужна дополнительная помощь?
Таблица в Excel – это сложный массив с множеством параметров. Он может состоять из значений, текстовых ячеек, формул и быть отформатирован разными способами (ячейки могут иметь определенное выравнивание, цвет, направление текста, специальные пометки и т.п.).
При копировании таблицы иногда требуется перенести не все ее элементы, а только некоторые. Рассмотрим, как это можно сделать.
Специальная вставка
Очень удобно выполнять перенос данных таблицы с помощью специальной вставки. Она позволяет выбрать только те параметры, которые нам необходимы при копировании. Рассмотрим пример.
Имеем таблицу с показателями по наличию макарон определенных марок на складе магазина. Наглядно видно, сколько килограмм было в начале месяца, сколько докуплено и продано, а также остаток на конец месяца. Два важных столбца выделены разными цветами. Остаток на конец месяца рассчитан по элементарной формуле.
Попробуем воспользоваться командой СПЕЦИАЛЬНАЯ ВСТАВКА и скопировать все данные.
Сначала мы выделяем имеющуюся таблицу, правой кнопкой вызываем меню и жмем КОПИРОВАТЬ.
В свободной ячейке снова вызываем меню правой кнопкой и нажимаем СПЕЦИАЛЬНАЯ ВСТАВКА.
Если мы оставим все как есть по умолчанию и просто нажмем ОК, то таблица вставится полностью, со всеми ее параметрами.
Попробуем поэкспериментировать. В СПЕЦИАЛЬНОЙ ВСТАВКЕ выберем другой пункт, например, ФОРМУЛЫ. Получили уже неформатированную таблицу, но с работающими формулами.
Теперь вставим не формулы, а только ЗНАЧЕНИЯ результатов их вычислений.
Чтобы новая таблица со значениями получила внешний вид аналогичный образцу выделим ее и вставим ФОРМАТЫ с помощью специальной вставки.
Теперь попробуем выбрать пункт БЕЗ РАМКИ. Получили полную таблицу, но только без выделенных границ.
Полезный совет! Чтобы перенести формат вместе с размерами столбцов нужно перед копированием выделить не диапазон исходной таблицы, а столбцы целиком (в данном случаи это диапазон A:F).
Аналогично можно поэкспериментировать с каждым пунктом СПЕЦИАЛЬНОЙ ВСТАВКИ, чтобы наглядно увидеть, как она работает.
Перенос данных на другой лист
Перенос данных на другие листы книги Excel позволяет связывать несколько таблиц. Это удобно тем, что при замене какого-то значения на одном листе, меняются значения на всех остальных. При создании годовых отчетов это незаменимая вещь.
Рассмотрим, как это работает. Для начала переименуем листы Excel в месяцы. Затем с помощью уже знакомой нам СПЕЦИАЛЬНОЙ ВСТАВКИ перенесем таблицу на февраль и удалим значения из трех столбов:
- На начало месяца.
- Приход.
- Расход.
Столбец «На конец месяца» у нас задан формулой, поэтому при удалении значений из предыдущих столбцов, он автоматически обнуляется.
Перенесем данные по остатку макарон каждой марки с января на февраль. Это делается буквально в пару нажатий.
- На листе ФЕВРАЛЬ ставим курсор в ячейку, показывающую количество макарон марки А на начало месяца. Можно посмотреть на рисунок выше – это будет ячейка D3.
- Ставим в этой ячейке знак РАВНО.
- Переходим на лист ЯНВАРЬ и кликаем на ячейку, показывающую количество макарон марки А на конец месяца (в нашем случае это ячейка F2 на листе «январь»).
Получаем следующее: в ячейке C2 образовалась формула, которая отсылает нас на ячейку F2 листа ЯНВАРЬ. Протянем формулу вниз, чтобы знать количество макарон каждой марки на начало февраля.
Аналогично можно перенести данные на все месяцы и получить наглядный годовой отчет.
Перенос данных в другой файл
Аналогично можно переносить данные из одного файла в другой. Данная книга в нашем примере так и называется EXCEL. Создадим еще одну и назовем ее ПРИМЕР.
Примечание. Создавать новые файлы Excel можно даже в разных папках. Программа будет автоматически искать указанную книгу, независимо от того, в какой папке и на каком диске компьютера она находится.
Скопируем в книгу ПРИМЕР таблицу с помощью все той же СПЕЦИАЛЬНОЙ ВСТАВКИ. И опять удалим значения из трех столбцов. Проведем те же действия, что и в предыдущем параграфе, но переходить уже будем не на другой лист, а на другую книгу.
Получили новую формулу, которая показывает, что ячейка ссылается на книгу EXCEL. Причем видим, что ячейка F2 выглядит как $F$2, т.е. она зафиксирована. И если мы хотим протянуть формулу на остальные марки макарон, сначала нужно удалить значки доллара, чтобы снять фиксацию.
Теперь вы знаете, как грамотно переносить данные из таблиц в рамках одного листа, с одного листа на другой, а также с одного файла в другой.
Автоматический перенос данных из одной таблицы в другую в программе Excel.
Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.
Первый, самый простой и примитивный способ связи двух таблиц на разных листах документа -вставка данных при помощи опции специальной вставки.
Рассмотрим, как соединить две таблицы по шагам.
Первый шаг.
Необходимо выделить ту таблицу, из которой будут транслироваться данные во вторую таблицу.
Второй шаг.
Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»
Третий шаг.
Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.
Четвертый шаг.
Поставить курсор в первую (левую верхнюю) ячейку таблицы и выбрать в меню «Вставка» пункт «Вставить связь». В некоторых версиях программы «Excel» этот пункт находится в меню «Специальная вставка»
После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.
Результат вставки связи
Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».
При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.
Как обновить сводную таблицу
При клике правой кнопкой мыши по сводной таблице и нажатии на пункт «Обновить» сводная таблица автоматически перенесет все данные из связанного массива информации («таблицы донора»).
О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:
Как делать сводные таблицы в программе «Excel» и для чего они нужны.
Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».
Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».
Смысл способа в следующем:
Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».
Из таблицы -Power Query
Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок».
Источник данных для запроса Power Query
После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.
После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»
Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»
Обновление запроса в PowerQuery