Найти и заменить числа в Excel?

Содержание

Вопрос или проблема
Ответ или решение
Теория
Пример
Применение
Опции и советы

Вопрос или проблема

У меня есть таблица с примерно 4000 ячеек, содержащих сумму, например, =439+23+678+1 (данные фондового рынка). Каждое число в формуле может варьироваться от 1 до 5 цифр. Мне нужно заменить каждую ячейку на сумму только первых двух чисел.

Операция “Найти и заменить” была бы идеальной, но подстановочные знаки не работают для чисел. Я знаю, что, вероятно, есть какая-то сложная инструкция VBA, которая может сработать, но я не разбираюсь в VBA, поэтому, если кто-то может помочь в этом, пожалуйста, не предполагайте наличие предварительных знаний.

Когда вы говорите, что подстановочные знаки не работают для чисел, что вы имеете в виду? У вас выбрана опция “Использовать подстановочные знаки”? Имеет ли каждая ячейка одно и то же количество полей? Если так, вы должны быть в состоянии найти это:

(=<*>+<*>)+<*>+<*>

Если нет, и нет десятичных дробей, вы можете искать это:

(=[0-9]{1,}+[0-9]{1,})+<*>

В обоих случаях замените на \1

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

Ответ или решение

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

Теория

Когда вы работаете с формулами в Microsoft Excel, использование стандартной функции "Найти и заменить" часто ограничивается текстовыми значениями, так как Excel не поддерживает полноценную функциональность работы с регулярными выражениями для чисел в своем интерфейсе поиска. Следовательно, если ваша задача предполагает изменение частей числового выражения, вам потребуется использовать более мощные средства — такие как VBA (Visual Basic for Applications). VBA — это встроенный язык программирования, который позволяет пользователям автоматизировать задачи в Excel, создавая макросы, которые могут выполнять сложные операции, которые нельзя выполнить вручную или с помощью стандартных функций Excel.

Пример

Допустим, у вас есть следующие формулы в ваших ячейках:

=439+23+678+1
=1204+56+7890
=25+89+1011

Цель заключается в том, чтобы оставить в каждой ячейке только сумму первых двух чисел. Например, первая формула должна быть изменена на =439+23, вторая — на =1204+56, и так далее.

Применение

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

Открытие редактора VBA:
- Нажмите ALT + F11, чтобы открыть редактор VBA.
- В редакторе выберите Insert -> Module, чтобы добавить новый модуль для кода.

Ввод кода VBA:

Вставьте следующий код в открывшееся окно модуля:

Sub ReplaceWithFirstTwoNumbers()
   Dim ws As Worksheet
   Dim cell As Range
   Dim formula As String
   Dim firstPlus As Long
   Dim secondPlus As Long

   ' Set the worksheet to work on
   Set ws = ThisWorkbook.Sheets("Sheet1") ' Используйте название вашего листа

   ' Loop through each cell containing a formula in the sheet
   For Each cell In ws.UsedRange.Cells
       If cell.HasFormula Then
           formula = cell.Formula

           ' Найдите первую и вторую постпозицию плюса 
           firstPlus = InStr(2, formula, "+")
           If firstPlus > 0 Then
               secondPlus = InStr(firstPlus + 1, formula, "+")
               If secondPlus > 0 Then
                   ' Новый вариант формулы только с двумя первыми элементами
                   formula = Left(formula, secondPlus - 1)
                   cell.Formula = formula
               End If
           End If
       End If
   Next cell
End Sub

Запуск макроса:
- В редакторе VBA выберите Run (или нажмите F5), чтобы выполнить макрос.

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

Опции и советы

Убедитесь, что вы предварительно сохранили ваш файл, так как макросы вносят изменения, которые могут быть необратимыми, если что-то пойдёт не так.
Измените строку Set ws = ThisWorkbook.Sheets("Sheet1") под ваше конкретное название листа, если оно отличается от "Sheet1".
Если вам нужно обработать только конкретный диапазон, вы можете заменить ws.UsedRange.Cells на ws.Range("A1:D4000") или любой другой диапазон, который вас интересует.

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