В этой статье описан метод объединения нескольких промежуточных формул для создания одной длинной формулы (мегаформулы). Если вы уже поработали в Excel определенное время, то могли видеть длинные формулы, которые были практически непонятны. Здесь вы узнаете, как они были созданы.

Поставим цель создать одну формулу, которая удаляет отчество (например, Иван Павлович Петров становится Иваном Петровым). На рис. 125.1 показан лист с некоторыми именами и шесть столбцов промежуточных формул, которые выполняют задачу. Обратите внимание, что формулы несовершенны: они не могут обрабатывать имя из одного слова.

Рис. 125.1. Удаление средних имен и инициалов требует указания шести промежуточных формул или одной мегаформулы

Формулы, введенные в строке 2, приведены в таблице ниже.

Затратив немного времени, вы можете исключить все промежуточные формулы и заменить их одной мегаформулой. Это можно сделать путем создания всех промежуточных формул и последующего редактирования окончательной формулы (в данном случае формулы в столбце Н) с заменой каждой ссылки на ячейку копией формулы в этой ячейке. К счастью, вы можете использовать буфер обмена для копирования и вставки формул, а не вводить их заново. Продолжайте этот процесс, пока ячейка H1 не станет содержать ничего, кроме ссылки на ячейку А1. В конечном итоге у вас получится следующая мегаформула в одной ячейке:

=ЛЕВСИМВ(СЖПРОБЕЛЫ(A2);НАЙТИ(" ";СЖПРОБЕЛЫ(A2);1))&ПРАВСИМВ(СЖПРОБЕЛЫ(A2);ДЛСТР(СЖПРОБЕЛЫ(A2))-ЕСЛИОШИБКА(НАЙТИ(" ";СЖПРОБЕЛЫ(A2);НАЙТИ(" ";СЖПРОБЕЛЫ(A2);1)+1);НАЙТИ(" ";СЖПРОБЕЛЫ(A2);1)))

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

Если вам все еще не ясен описанный процесс, выполните его пошагово.

1. Проверьте формулу в Н2. Она содержит две ссылки на ячейки (F2 и 62): =F2&G2
2. Активизируйте ячейку G2 и скопируйте содержимое формулы (без знака равенства) в буфер обмена.
3. Выберите ячейку Н2 и замените ссылку на ячейку G2 содержимым из буфера обмена. Теперь ячейка Н2 содержит следующую формулу:
   =F2&ПРАВСИМВ(B2; ДЛСТР(B2)-E2)
4. Активизируйте ячейку F2 и скопируйте содержимое формулы (без знака равенства) в буфер обмена.
5. Выберите ячейку Н2 и замените ссылку на ячейку F2 содержимым из буфера обмена. Теперь формула в ячейке Н2 следующая:
   =ЛЕВСИМВ(B2;C2)&ПРАВСИМВ(B2;ДЛСТР(B2)-E2)
6. Ячейка Н2 на данный момент содержит ссылки на три ячейки (В2, С2 и Е2). Формулы в них заменяют каждую из ссылок на эти ячейки.
7. Замените ссылку на ячейку Е2 формулой в Е2. В результате получаем:
   =ЛЕВСИМВ(B2;C2)&ПРАВСИМВ(B2;ДЛСТР(B2)-ЕСЛИОШИБКА(D2;C2))
8. Скопируйте формулу из D2 и замените ссылки на ячейку D2.

=ЛЕВСИМВ(B2;C2)&ПРАВСИМВ(B2;ДЛСТР(B2)-ПРАВСИМВ(НАЙТИ(" ";B2;B2+1);C2))
В формуле есть три ссылки на ячейку С2.

9. Замените каждую из этих ссылок формулой, содержащейся в ячейке С2. Формула в ячейке Н2:
   =ЛЕВСИМВ(B2;НАЙТИ(" ";B2;1))&ПРАВСИМВ(B2;ДЛСТР(B2)-ЕСЛИОШИБКА(НАЙТИ(" ";B2;НАЙТИ(" ";B2;1);НАЙТИ(" ";B2;1)))
10. И, наконец, замените семь ссылок на ячейку В2 формулой в ячейке В2. В результате получаем:
    =ЛЕВСИМВ(СЖПРОБЕЛЫ(A2);НАЙТИ(" ";СЖПРОБЕЛЫ(A2);1))&ПРАВСИМВ(СЖПРОБЕЛЫ(A2);ДЛСТР(СЖПРОБЕЛЫ(A2))-ЕСЛИОШИБКА(НАЙТИ(" ";СЖПРОБЕЛЫ(A2);НАЙТИ(" ";СЖПРОБЕЛЫ(A2);1)+1);НАЙТИ(" ";СЖПРОБЕЛЫ(A2);1)))

Обратите внимание, что формула в ячейке Н2 теперь содержит ссылки только на ячейку А2. Мегаформула завершена и выполняет те же самые задачи, что и промежуточные формулы (которые теперь можно удалить). Вы можете, конечно, адаптировать этот метод для собственных нужд. Приятной особенностью является то, что одна мегаформула часто рассчитывается быстрее, чем несколько промежуточных формул.

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