Несколько формул в одной ячейке Excel

Обновлено: 20.11.2024

Эта страница представляет собой поддерживаемый рекламодателями отрывок из книги Power Excel 2010–2013 от MrExcel — 567 разгаданных тайн Excel. Если вам нравится эта тема, подумайте о покупке всей электронной книги.

Проблема: когда мне нужно построить сложный расчет, мне иногда нужно построить несколько промежуточных формул, чтобы разобраться в проблеме. Когда все эти формулы заработают, можно ли объединить логику промежуточных формул в одну формулу?

Стратегия. Использование временных подформул — отличный способ вычислить довольно сложную формулу. Есть относительно простой способ вернуть все эти формулы в одну формулу.

В предыдущем примере формула в ячейке J7 =ИНДЕКС(B2:G20,J5,J6) ссылается на две другие ячейки J5 и J6. Каждая из этих ячеек содержит формулу.

Стратегия: Вы можете выбирать символы в строке формул и копировать их в буфер обмена с помощью Ctrl+C. Когда вы копируете целую ячейку, вы создаете много сложностей, в том числе проблему, заключающуюся в том, что вы не можете вставить эту ячейку в середину формулы или в диалоговое окно «Заменить». Вместо этого, скопировав символы из строки формул, вы получите обычный текст в буфере обмена и сможете вставить его в другую формулу или в диалоговое окно «Заменить». Вот что вы делаете:

Выберите ячейку J5. В строке формул щелкните мышью после знака равенства и перетащите в конец формулы. Нажмите Ctrl+C, чтобы скопировать эти символы в буфер обмена. Выйдите из режима редактирования, нажав клавишу Esc.
Выберите ячейку J7. В строке формул выделите ссылку на J5
Нажмите Ctrl+V, чтобы вставить формулу из J5, чтобы заменить ссылку на J5.
Выберите ячейку J6. Нажмите F2, Ctrl+Shift+Home, Shift+Стрелка вправо, Ctrl+C, Esc. Эти сочетания клавиш редактируют ячейку, а затем выбирают все, кроме знака равенства.
Выберите ячейку J7. Выберите ссылку на J6
Нажмите Ctrl+V, чтобы вставить формулу из J6, чтобы заменить ссылку на J6.

В результате получается одна формула, которая заменяет три формулы.

Результат. Ваши коллеги будут поражены вашей способностью создавать масштабные формулы.

Альтернативный метод: вместо того, чтобы следовать только что описанным шагам, вы можете использовать диалоговое окно «Заменить», чтобы объединить промежуточные формулы в мегаформулы. Выполните следующие действия:

Выберите ячейку J5. В строке формул используйте мышь, чтобы выбрать все, начиная сразу после знака равенства и заканчивая концом формулы. Нажмите Ctrl+C, чтобы скопировать эти символы в буфер обмена. Нажмите клавишу Esc, чтобы выйти из строки формул.
Выберите ячейки J7:J8. Подсказка: убедитесь, что этот выбор содержит две ячейки, даже если вы работаете только с одной формулой! Если вы выберете две или более ячеек, команда «Заменить все» будет работать только в пределах выделения. Если вы выберете только одну ячейку, команда «Заменить все» распространится на все 17 миллиардов ячеек на листе.
Выберите «Главная», «Найти и выбрать», «Заменить» или Ctrl+H.
В поле "Найти" введите J5.
Перейдите к полю "Заменить на". Нажмите Ctrl+V. Excel скопирует символы из формулы J5 в диалоговое окно.
Нажмите кнопку "Параметры".
Убедитесь, что в раскрывающемся списке "Искать в" выбрано значение "Формулы". Убедитесь, что флажок «Совпадать со всем содержимым ячейки» снят. (Если вы начнете новый сеанс Excel, обе эти настройки будут правильными. Однако диалоговое окно запоминает настройки из последнего поиска и замены, которые вы выполняли ранее в текущем сеансе, поэтому всегда стоит потратить время, чтобы нажать кнопку «Параметры». чтобы убедиться, что эти настройки верны.)
Нажмите «Заменить все». Excel удалит ссылку на J5 из выбранных ячеек и заменит ее символами из J5.
Повторите шаг 1 для ячейки J6.
Повторите шаги 2–8.

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

Подсказка: следите за тем, чтобы ваши целевые формулы не содержали ссылок, содержащих другие формы B2 и C2, например B20 или C210909. Если ваши формулы содержат такие ссылки, при замене B2 Excel вслепую поместит формулу B2, где символы B2 появляются в B20.

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

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

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

Лучший способ продемонстрировать это, и мой первый опыт работы с ним, — работа с текстом в Excel.

Допустим, у вас есть приведенный ниже пример:

Мы хотим получить первые два символа из каждого номера детали, поэтому мы используем функцию =LEFT() в Excel, чтобы получить это:

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

Обратите внимание, что для первого номера детали возвращается только "4", когда мы хотим "4z". Это связано с пробелом перед номером детали в ячейке B3. Чтобы решить эту проблему, мы можем вложить функции; мы можем поместить другую функцию внутрь функции LEFT(), и это так же просто, как ввести вторую функцию везде, где нам нужно, чтобы она работала.

Поскольку мы хотим избавиться от лишних пробелов вокруг некоторых номеров деталей, мы хотим использовать функцию TRIM() и обрезать номера деталей до того, как функция LEFT() извлечет два символа слева. текста. Таким образом, мы помещаем функцию TRIM() вокруг ссылки на ячейку для номеров деталей следующим образом:

Вы можете видеть, как функция TRIM() полностью размещена внутри функции LEFT(). Вот и все.

Это часто используется с функцией/оператором IF() в Excel для создания более мощных логических операторов.

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

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

Зарегистрированный пользователь Регистрация 13.09.2018 Местоположение Клируотер, Флорида MS-Off Ver 365 Сообщений 4

Добавление нескольких формул в одну ячейку

Я поместил несколько формул в одну ячейку, и она отлично работает Теперь я пытаюсь получить результаты для каждой формулы, чтобы сложить их вместе для получения одного результата. Ниже приведена формула, которую я использую, она ищет соответствие двум ячейкам, и если они действительно соответствуют ее данным извлечения из нескольких ячеек, мне нужно, чтобы все эти данные были сложены вместе для единой суммы (я добавляю валюту США). Кто-нибудь знает, как сделать это?

Почему знаки + не делают того, что вам нужно?

Прикрепите образец книги (не изображение и не вставленную копию). Убедитесь, что данных достаточно, чтобы продемонстрировать ваши потребности. Включите листы ДО и ПОСЛЕ в книгу, если это необходимо, чтобы показать процесс, который вы пытаетесь завершить или автоматизировать. Убедитесь, что желаемые результаты отображаются, при необходимости смоделируйте их вручную.

<р>1. Убедитесь, что ваши образцы данных ПРЕДСТАВЛЯЮТ ваши реальные данные. Использование нерепрезентативных данных очень неприятно и может привести к длительным задержкам в поиске решения.

<р>2. Убедитесь, что желаемые результаты также отображаются (макетируйте результаты вручную).

<р>3. Сначала убедитесь, что все конфиденциальные данные удалены или заменены фиктивными данными (например, именами, адресами, адресами электронной почты и т. д.).

<р>4. Старайтесь не использовать объединенные ячейки, так как они вызывают много проблем.

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

Пожалуйста, обратите особое внимание на пункт 2 (выше): без представления о ваших предполагаемых результатах часто очень трудно дать надлежащий совет.

Али

Увлеченный самоучка, пользователь MS Excel, который постоянно учится!

Правила форума (обновлены в сентябре 2018 г.): ознакомьтесь с ними здесь.
Как использовать полученный код Power Query: помощь здесь. Подробнее о Power Suite здесь.
Не забудьте сказать "спасибо" тем, кто помог вам в вашей теме. Если хотите, вы также можете вознаградить их, нажав на звезду их репутации внизу слева.

Уважаемый участник форума Дата регистрации 05 мая 2018 г. Местоположение Калифорния, США MS-Off Ver 2016 Сообщений 667

Без просмотра ваших фактических данных и отсутствия подробного описания того, чего вы пытаетесь достичь, и, как вы описали, вы добавляете валюту (а это значит, что они должны быть числами), это просто попытка исправить существующую формулу, попробуйте:

Несколько формул в одной ячейке

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

Мне нужно, чтобы ячейка D4 отображала "A", когда что-то вставляется в ячейку E4. Я справился с этой частью, но затем я хочу, чтобы ячейка D4 отображала «C», когда что-то вставляется в ячейку J4

Пока я использую =IF(E4<>"","A","")

Также у меня есть следующая формула в ячейке H4

Мне нужно это, чтобы перестать использовать сегодняшнюю дату и использовать дату в ячейке J4, как только она будет введена

Forum Expert Дата регистрации 23 марта 2004 г. Местоположение Лондон, Англия MS-Off Ver Excel 2019 Сообщений 7038

в D4
=IF(E4<>"","A","")&IF(J4<>"","C","")
отображает как A, так и C (вы не указали, имеет ли какая-либо формула преимущество перед другой)

Если первая формула имеет приоритет, попробуйте это (поменяйте местами, если другая формула должна иметь прецедент)
=IF(E4<>"","A",IF(J4<>"","C ",""))

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

Гуру форума Дата регистрации 31 декабря 2011 г. Местоположение Уоррингтон, Англия MS-Off Ver Office 2019 (все еще учится) Сообщений 22 352

В зависимости от приоритета того, хотите ли вы видеть A или C, если и E4, и J4 не пусты, вы можете сделать это в D4:

Что касается H4, вы можете сделать это:

Надеюсь, это поможет.

Большое спасибо вам обоим за помощь в этом, комбинация обоих ответов сработала как мечта. У меня есть последний запрос к этой таблице;

Я прикрепил обновленную таблицу Service Charges.xlsx

Forum Expert Дата регистрации 01.09.2012 Расположение Норвегия MS-Off Ver Office 365 Сообщений 2,593

Формула:

Если вам помогли мои сообщения, вы можете сказать "Спасибо", щелкнув звездочку внизу слева

Если проблема решена, завершите тему, нажав РЕШЕНО в разделе Инструменты темы.
Я не хочу оставлять вас без ответа, но иногда пропускаю сообщения. Если вы чувствуете, что я забыл о вас, напомните мне в личку или просто поднимите тему.

Если это решает ваш первоначальный вопрос, выберите Инструменты темы в меню над первым сообщением и отметьте эту тему как РЕШЕННУЮ.

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

Читайте также: