Как формулы в excel: Обзор формул — Служба поддержки Майкрософт
Содержание
sql — Есть ли в Excel функция, подобная объединению?
спросил
Изменено
11 месяцев назад
Просмотрено
126 тысяч раз
Мне нужно заполнить ячейку первой непустой записью в наборе столбцов (слева направо) в той же строке — аналогично объединению() в SQL.
В следующем примере листа
--------------------------------------- | | А | Б | С | Д | --------------------------------------- | 1 | | х | у | г | --------------------------------------- | 2 | | | у | | --------------------------------------- | 3 | | | | г | ---------------------------------------
Я хочу поместить функцию ячейки в каждую ячейку строки A, чтобы получить:
------------------------------------------------------ | | А | Б | С | Д | --------------------------------------- | 1 | х | х | у | г | --------------------------------------- | 2 | у | | у | | --------------------------------------- | 3 | г | | | г | ---------------------------------------
Я знаю, что могу сделать это с помощью каскада функций ЕСЛИ, но на моем реальном листе у меня есть 30 столбцов для выбора, поэтому я был бы счастлив, если бы существовал более простой способ.
- sql
- excel
- xls
- объединение
=ИНДЕКС(B2:D2,СООТВЕТСТВИЕ(ЛОЖЬ,ПУСТО(B2:D2),ЛОЖЬ))
Это формула массива. После ввода формулы нажмите CTRL + Shift + Введите , чтобы Excel оценил ее как формулу массива. Это возвращает первое непустое значение заданного диапазона ячеек. Для вашего примера формула вводится в столбец с заголовком «а»
A B C D 1 х х у г 2 года 3 з з
4
Я использовал:
= ЕСЛИ (ПУСТО (A1), B1, A1)
Это проверяет, если первое поле, которое вы хотите использовать, пустое, используйте другое. Вы можете использовать «вложенные если», когда у вас есть несколько полей.
1
Или, если вы хотите сравнить отдельные ячейки, вы можете создать функцию Coalesce в VBA:
Публичная функция Coalesce(ParamArray Fields() As Variant) As Variant
Dim v как вариант
Для каждого v в полях
Если "" & v <> "" Тогда
Слияние = v
Выход из функции
Конец, если
Следующий
Слияние = ""
Конечная функция
А потом вызвать в Excel.
В вашем примере формула в A1 будет выглядеть так:
= Объединение (B1, C1, D1)
4
Сделав еще один шаг в подходе VBA, я переписал его так, чтобы он позволял комбинировать как отдельные ячейки, так и диапазоны ячеек:
Тусклая ячейка как вариант
Dim SubCell как вариант
Для каждой ячейки в ячейках
Если VarType(Cell) > vbArray Тогда
Для каждой подячейки в ячейке
Если VarType(SubCell) <> vbEmpty Тогда
Объединение = субячейка
Выход из функции
Конец, если
Следующий
Еще
Если VarType(Cell) <> vbEmpty Тогда
Объединение = Ячейка
Выход из функции
Конец, если
Конец, если
Следующий
Слияние = «»
Конечная функция
Итак, теперь в Excel вы можете использовать любую из следующих формул в A1:
= Объединение (B1, C1, D1) = Объединение (B1, C1: D1) = Объединение (B1: C1, D1) = Объединение (B1: D1)
0
Если вы знаете, что столбцы не будут перекрываться, или хотите, чтобы они перекрывались, то это довольно быстрый способ решения проблемы объединения.
Приведенная ниже формула применяется не к вашим значениям и столбцам, а скорее к моему макету, поэтому вам нужно будет настроить ее, чтобы сделать ее актуальной.
=ЛЕВО(ОТРЕЗАТЬ(СЦЕПИТЬ(Q38,R38,S38,T38,U38,V38,W38,X38,Y38)),1)
С обновленной функцией IFS в Excel вам не нужно вкладывать. Вы можете попробовать что-то вроде
- Создайте пустую ячейку справа
Затем введите.
=ЕСЛИ(ЕСПУСТО(A1),B1,ПУСТО(A1),C1,ПУСТО(A1),D1)
Выделите столбец и при необходимости вставьте.
Если вы хотите объединиться только с 0, что является очень распространенным вариантом использования, вы можете просто использовать функцию SUM() вокруг одного значения. Для удобства все пробелы обрабатываются как ноль, и это очень удобно, поскольку они такие короткие.
Не универсальное решение, как другие ответы, а полезный ярлык во многих случаях, когда это именно то, что вам нужно.
Внутри массива введите недопустимые переменные.
Объединение функций (ParamArray Fields() As Variant) As Variant
Dim v как вариант
Для каждого v в полях
Если IsError(Application.Match(v, Array("", " ", 0), False)) Тогда
Слияние = v
Выход из функции
Конец, если
Следующий
Слияние = ""
Конечная функция
В зависимости от того, сколько ячеек вы хотите проверить, вы можете объединить несколько проверок ISBLANK.
Например, при проверке столбцов A, B, затем C:
=ЕСЛИ(ПУСТО(A1),ЕСЛИ(ПУСТО(B1),C1,B1),A1)
Для столбцов A, B, C и D:
= ЕСЛИ (ПУСТО (A1), ЕСЛИ (ПУСТО (B1), ЕСЛИ (ПУСТО (C1), D1, C1), B1), A1)
… и так далее.
Опоздав на вечеринку и используя ответ @AndyMC, вы можете использовать следующее для оценки vlookups, index(match()) и т. д., чтобы объединить ваши операторы формулы.
Объединение открытых функций (ParamArray Fields() As Variant) As Variant
Dim v как вариант
Для каждого v в полях
Если Не ЯвляетсяОшибкой(v), Тогда
Слияние = v
Выход из функции
Конец, если
Следующий
Объединение = CVERr(xlErrNA)
Конечная функция
и используйте его в своем рабочем листе следующим образом: = Объединение (ИНДЕКС (ЛИСТ1! ЛИСТ1!$1:$1,0)),ИНДЕКС(ЛИСТ2!A:CR,ПОИСКПОЗ(Основной!$Q36,ЛИСТ2!$M:$M,0),ПОИСКПОЗ("ЧТО-ТО",ЛИСТ2!$1:$1,0) ))
Для первого оператора, который не возвращает #N/A , он вернет фактическое совпадающее значение.
Зарегистрируйтесь или войдите в систему
Зарегистрируйтесь с помощью Google
Зарегистрироваться через Facebook
Зарегистрируйтесь, используя электронную почту и пароль
Опубликовать как гость
Электронная почта
Обязательно, но не отображается
Опубликовать как гость
Электронная почта
Требуется, но не отображается
Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.
Создание условных формул — Служба поддержки Майкрософт
Проверка истинности или ложности условий и выполнение логических сравнений между выражениями характерны для многих задач.
Вы можете использовать функции И , ИЛИ , НЕ и ЕСЛИ для создания условных формул.
Например, функция IF использует следующие аргументы.
Формула, использующая функцию ЕСЛИ
logical_test: условие, которое вы хотите проверить.
value_if_true: возвращаемое значение, если условие истинно.
value_if_false: возвращаемое значение, если условие имеет значение False.
Дополнительные сведения о создании формул см. в разделе Создание или удаление формулы.
Что ты хочешь сделать?
Создайте условную формулу, результатом которой является логическое значение (ИСТИНА или ЛОЖЬ).
Создайте условную формулу, которая приводит к другому вычислению или значениям, отличным от ИСТИНА или ЛОЖЬ
Создать условную формулу, результатом которой будет логическое значение (ИСТИНА или ЛОЖЬ)
Для выполнения этой задачи используйте функции и операторы И , ИЛИ и НЕ , как показано в следующем примере.
Пример
Пример будет легче понять, если вы скопируете его на пустой лист.
Как скопировать пример?
Выберите пример из этой статьи.
Выбор примера из справки
Нажмите CTRL+C.
В Excel создайте пустую книгу или лист.
org/ListItem»>На листе выберите ячейку A1 и нажмите CTRL+V.
Важно: Чтобы пример работал правильно, его необходимо вставить в ячейку A1 рабочего листа.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (высокий ударение) или на Формулы в группе Аудит формул нажмите кнопку Показать формулы .
После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.
|
(ЛОЖЬ)
(ЛОЖЬ)Дополнительные сведения об использовании этих функций см. в разделе Функция И, Функция ИЛИ и Функция НЕ.
Верх страницы
Создайте условную формулу, которая приводит к другому вычислению или значениям, отличным от ИСТИНА или ЛОЖЬ
Для выполнения этой задачи используйте функции и операторы IF , И и ИЛИ , как показано в следующем примере.
Пример
Пример будет легче понять, если вы скопируете его на пустой лист.
Как скопировать пример?
Выберите пример из этой статьи.
Важно: Не выбирайте заголовки строк или столбцов.
Выбор примера из справки
Нажмите CTRL+C.
В Excel создайте пустую книгу или лист.
На листе выберите ячейку A1 и нажмите CTRL+V.
Важно: Чтобы пример работал правильно, его необходимо вставить в ячейку A1 рабочего листа.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (ударение) или на вкладке Формулы в группе Аудит формул нажмите кнопку Показать формулы .
После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.
|
В противном случае верните «Не в порядке». (ОК)
В противном случае верните «Не в порядке». (Не в порядке)
