Excel несколько диапазонов в формуле

Обновлено: 21.11.2024

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

Области диапазона

Набор (возможно, несмежных) диапазонов представлен объектом RangeAreas. Он имеет свойства и методы, аналогичные типу Range (многие из них имеют одинаковые или похожие имена), но были внесены следующие изменения:

Типы данных для свойств и поведение методов установки и получения.
Типы данных параметров метода и поведение метода.
Типы данных возвращаемых значений метода.

RangeAreas имеет свойство адреса, которое возвращает строку адресов диапазона с разделителями-запятыми, а не только один адрес, как в свойстве Range.address.
RangeAreas имеет свойство dataValidation, которое возвращает объект DataValidation, представляющий проверку данных всех диапазонов в RangeAreas, если они непротиворечивы. Свойство имеет значение null, если идентичные объекты DataValidation не применяются ко всем диапазонам в RangeAreas. Это общий, но не универсальный принцип для объекта RangeAreas: Если свойство не имеет согласованных значений во всех диапазонах в RangeAreas , тогда оно равно null . См. раздел Чтение свойств RangeAreas. для получения дополнительной информации и некоторых исключений.
RangeAreas.cellCount получает общее количество ячеек во всех диапазонах RangeAreas.
RangeAreas.calculate пересчитывает ячейки всех диапазонов в RangeAreas.
RangeAreas.getEntireColumn и RangeAreas.getEntireRow возвращают другой объект RangeAreas, представляющий все столбцы (или строки) во всех диапазонах RangeAreas . Например, если RangeAreas представляет "A1:C4" и "F14:L15", то RangeAreas.getEntireColumn возвращает объект RangeAreas, который представляет "A:C" и "F:L".
RangeAreas.copyFrom может принимать параметр Range или RangeAreas, представляющий исходный диапазон(ы) операции копирования.

Полный список элементов Range, которые также доступны в RangeAreas

Свойства

Ознакомьтесь со свойствами чтения RangeAreas, прежде чем писать код, считывающий любые перечисленные свойства. Есть нюансы в том, что возвращается.

адрес
адресМестный
количество ячеек
условные форматы
контекст
Проверка данных
формат
isEntireColumn
isEntireRow
стиль
рабочий лист

Методы

вычислить()
очистить()
преобразоватьТипДанныхВТекст()
convertToLinkedDataType()
копировать из()
getEntireColumn()
получитьEntireRow()
получитьИнтерсекцию()
получитьIntersectionOrNullObject()
getOffsetRange() (с именем getOffsetRangeAreas в объекте RangeAreas)
получитьSpecialCells()
получитьSpecialCellsOrNullObject()
получить таблицы()
getUsedRange() (с именем getUsedRangeAreas в объекте RangeAreas)
getUsedRangeOrNullObject() (с именем getUsedRangeAreasOrNullObject в объекте RangeAreas)
загрузить()
установить()
setDirty()
вJSON()
отслеживать()
отключить()

Свойства и методы, относящиеся к RangeArea

Тип RangeAreas имеет некоторые свойства и методы, которых нет в объекте Range. Ниже приведены некоторые из них.

areas : объект RangeCollection, который содержит все диапазоны, представленные объектом RangeAreas. Объект RangeCollection также является новым и похож на другие объекты коллекции Excel. У него есть свойство items, которое представляет собой массив объектов Range, представляющих диапазоны.
areaCount : общее количество диапазонов в RangeAreas .
getOffsetRangeAreas : работает так же, как Range.getOffsetRange, за исключением того, что возвращается RangeAreas и содержит диапазоны, каждый из которых смещен от одного из диапазонов в исходном RangeAreas .

Создать области диапазона

Вы можете создать объект RangeAreas двумя основными способами:

Вызовите метод Worksheet.getRanges() и передайте ему строку с адресами диапазонов, разделенными запятыми. Если какой-либо диапазон, который вы хотите включить, был включен в NamedItem, вы можете включить в строку имя вместо адреса.
Вызов Workbook.getSelectedRanges() . Этот метод возвращает RangeAreas, представляющий все диапазоны, выбранные на текущем активном листе.

После того как у вас есть объект RangeAreas, вы можете создавать другие, используя методы объекта, которые возвращают RangeAreas, такие как getOffsetRangeAreas и getIntersection .

Вы не можете напрямую добавлять дополнительные диапазоны к объекту RangeAreas. Например, коллекция в RangeAreas.areas не имеет метода добавления.

Не пытайтесь напрямую добавлять или удалять элементы массива RangeAreas.areas.items. Это приведет к нежелательному поведению в вашем коде. Например, можно поместить в массив дополнительный объект Range, но это вызовет ошибки, поскольку свойства и методы RangeAreas ведут себя так, как будто нового элемента там нет. Например, свойство areaCount не включает диапазоны, переданные таким образом, а RangeAreas.getItemAt(index) выдает ошибку, если index больше, чем areaCount-1 . Аналогично, удаление объекта Range в массиве RangeAreas.areas.items путем получения ссылки на него и вызова его метода Range.delete вызывает ошибки: хотя объект Range удаляется, свойства и методы объекта родительский объект RangeAreas ведет себя или пытается вести себя так, как если бы он все еще существовал. Например, если ваш код вызывает RangeAreas.calculate , Office попытается вычислить диапазон, но выдаст ошибку, поскольку объект диапазона отсутствует.

Установить свойства для нескольких диапазонов

Установка свойства для объекта RangeAreas устанавливает соответствующее свойство для всех диапазонов в коллекции RangeAreas.areas.

Ниже приведен пример установки свойства для нескольких диапазонов. Функция выделяет диапазоны F3:F5 и H3:H5.

Этот пример относится к сценариям, в которых вы можете жестко закодировать адреса диапазонов, которые вы передаете в getRanges, или легко вычислить их во время выполнения. Некоторые из сценариев, в которых это было бы верно, включают:

Код выполняется в контексте известного шаблона.
Код выполняется в контексте импортированных данных, схема которых известна.

Получить специальные ячейки из нескольких диапазонов

Методы getSpecialCells и getSpecialCellsOrNullObject для объекта RangeAreas работают аналогично одноименным методам для объекта Range. Эти методы возвращают ячейки с указанной характеристикой из всех диапазонов коллекции RangeAreas.areas. Дополнительные сведения о специальных ячейках см. в разделе Поиск специальных ячеек в диапазоне.

При вызове метода getSpecialCells или getSpecialCellsOrNullObject для объекта RangeAreas:

Если вы передадите Excel.SpecialCellType.sameConditionalFormat в качестве первого параметра, метод вернет все ячейки с тем же условным форматированием, что и самая верхняя левая ячейка в первом диапазоне в коллекции RangeAreas.areas.
Если вы передадите Excel.SpecialCellType.sameDataValidation в качестве первого параметра, метод вернет все ячейки с тем же правилом проверки данных, что и самая верхняя левая ячейка в первом диапазоне в коллекции RangeAreas.areas.

Чтение свойств RangeAreas

Все становится сложнее, когда согласованность невозможна. Поведение свойств RangeAreas следует этим трем принципам:

Логическое свойство объекта RangeAreas возвращает false, если свойство не истинно для всех диапазонов элементов.
Нелогические свойства, за исключением свойства адреса, возвращают значение null, если только соответствующее свойство во всех диапазонах членов не имеет одинакового значения.
Свойство address возвращает разделенную запятыми строку адресов диапазонов элементов.

Например, следующий код создает RangeAreas, в котором только один диапазон представляет собой целый столбец и только один из них заполнен розовым цветом. Консоль отобразит null для цвета заливки, false для свойства isEntireRow и «Sheet1!F3:F5, Sheet1!H:H» (при условии, что имя листа — «Sheet1») для свойства адреса.

Чтобы объединить диапазоны в Excel с формулой, вы можете использовать формулу, в основном основанную на функции ИНДЕКС и функции ПОСЛЕДОВАТЕЛЬНОСТЬ. В приведенном ниже примере формула в ячейке B5 выглядит следующим образом:

где r.1 (E5:F9) и r.2 (H5:I10) — именованные диапазоны. Формула добавляет второй диапазон к первому диапазону, и результат выливается в B5:C15. Это пример того, как именованные диапазоны могут сделать сложную формулу намного проще в использовании, поскольку диапазоны нужно определить только один раз. Однако функция LET также позволяет упростить формулу. Подробнее см. ниже.

Хотя вы можете легко комбинировать диапазоны в Excel с Power Query, традиционно это было сложно сделать со стандартными формулами Excel. Однако новые формулы динамического массива в Excel 365 делают это возможным без особых сложностей.

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

Аналогичным образом SEQUENCE также используется для создания «индекса столбца»:

Функция ЕСЛИ проверяет все значения в последовательности индексов строк с количеством строк для диапазона 1. Если значение индекса строк меньше или равно количеству строк в диапазоне 1 (5), функция ИНДЕКС срабатывает. используется для выборки строк из диапазона 1:

Когда значение индекса строки больше 5, используется ИНДЕКС для выборки строк из диапазона 2:

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

С функцией ПУСТЬ

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

Разрывы строк добавлены для удобства чтения. Обратите внимание, что первые пять строк формулы просто объявляют переменные и присваивают значения. Следующие четыре строки выполняют работу по объединению диапазонов с использованием переменных, созданных выше. Это значительно сокращает количество избыточного кода в формуле.

В приведенном выше коде показан прямой перевод исходной формулы на основе уже определенных именованных диапазонов. Однако именованные диапазоны не требуются. Версия ниже адаптирует формулу для принятия двух диапазонов в качестве первых двух переменных, теперь переименованных в диапазон1 и диапазон2:

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

Пользовательская функция с LAMBDA

Функция LAMBDA может использоваться для создания пользовательских функций. Формула на этой странице — хороший кандидат, поскольку она относительно сложна. При преобразовании в пользовательскую функцию LAMBDA ее намного проще вызывать:

Ячейки в диапазоне рабочих листов Excel являются смежными. Вы можете выбрать несколько диапазонов (несмежных) на листе Excel. Ниже приведены различные способы выбора нескольких диапазонов на листе Excel.

Прежде чем продолжить, щелкните ссылки ниже, чтобы узнать больше о диапазонах адресов.

1 — Нажмите и удерживайте клавишу «Ctrl», чтобы выбрать несколько диапазонов на одном листе

Обычно, когда вы выбираете диапазон, а затем нажимаете на другую ячейку, первый выбранный диапазон будет отменен. Чтобы выбрать несколько диапазонов на одном листе Excel, используйте клавишу «Ctrl». Сначала выберите диапазон, а затем нажмите и удерживайте клавишу «Ctrl» при выборе следующего диапазона на том же листе.

2 — Включите режим «Добавить или удалить выделение», чтобы выбрать несколько диапазонов на одном листе

Нажмите одновременно клавиши "Shift" и "F8", чтобы выбрать несколько диапазонов на одном листе. При одновременном нажатии клавиш «Shift» и «F8» включается режим «Добавить или удалить выделение». Вы можете посмотреть, включен ли режим «Добавить или удалить выделение», в строке состояния Excel, как показано ниже.

3 – введите адреса диапазона, разделенные запятой, в поле "Имя ячейки" на панели формул

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

Например: введите B2:C4,D6:E8,F10:G12 в поле «Имя ячейки» и нажмите клавишу «Ввод», чтобы получить множественный выбор, как показано ниже.

4 — Введите диапазон адресов через запятую в диалоговом окне "Перейти к"

Введите диапазон адресов, которые вы хотите выбрать, через запятую в диалоговом окне «Перейти». В этом примере три диапазона адресов объединяются вместе, чтобы сформировать B2:C4,D6:E8,F10:G12. Нажмите кнопку "ОК".

Совет

Диалоговое окно «Перейти» можно открыть из вкладки «Главная» ленты Excel > группы «Редактирование» > кнопки меню «Найти и выбрать»

Нажмите одновременно клавиши "Ctrl" и "G".

Нажмите "F5"

Несколько диапазонов выбираются в соответствии с адресами диапазонов (B2:C4,D6:E8,F10:G12), введенными в диалоговом окне «Перейти к», как показано на рисунке ниже.

Часто при работе в Excel нам приходится иметь дело с ЕСЛИ Excel между несколькими диапазонами.Сегодня я покажу, как вы можете работать с функцией ЕСЛИ между несколькими диапазонами в Excel.

Загрузить практическую рабочую тетрадь

4 подхода к использованию Excel IF между несколькими диапазонами

Здесь у нас есть набор данных с именами некоторых учеников и их оценками по физике и химии в школе под названием "Подсолнечный детский сад".

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

1. Используйте функции ЕСЛИ и ИЛИ Excel для критериев типа ИЛИ между несколькими диапазонами

Вы можете использовать комбинацию функции ЕСЛИ и функции ИЛИ Excel для обработки критериев типа ИЛИ между несколькими диапазонами.

Например, давайте попробуем решить для каждого из студентов, провалился ли он/она на экзамене или нет.

Критерии отказа просты. Вы не пройдёте, если не пройдёте хотя бы по одному предмету (получите оценки менее 40).

Поэтому это условие типа ИЛИ между несколькими диапазонами.

Выберите первую ячейку нового столбца и введите следующую формулу:

Затем перетащите маркер заполнения, чтобы скопировать эту формулу в остальные ячейки.

Посмотрите, учащиеся, которые не прошли хотя бы по одному предмету, были оценены как неудовлетворительно и отмечены желтым цветом в наборе данных.

⧪ Объяснение формулы:

2. Объедините функции ЕСЛИ и И в Excel для критериев типа И между несколькими диапазонами

Вы можете комбинировать функцию ЕСЛИ и функцию И в Excel для обработки критериев типа И между несколькими диапазонами.

Например, попробуем на этот раз для каждого из студентов решить, сдал он экзамен или нет.

Кроме того, критерием провала является то, что вы проходите, если вы проходите по всем предметам (получите оценки больше или равные 40), в противном случае нет.

Поэтому это условие типа И между несколькими диапазонами.

Выберите первую ячейку нового столбца и введите следующую формулу:

Затем перетащите маркер заполнения, чтобы скопировать эту формулу в остальные ячейки.

Посмотрите, учащиеся, успешно сдавшие оба предмета, получили оценку "Удачно" и отмечены желтым цветом в наборе данных.

⧪ Объяснение формулы:

C4>=40 возвращает TRUE, если отметка в ячейке C4 (отметка по физике) больше или равна 40, в противном случае возвращает FALSE. То же самое для D4>=40.
И(C4>=40,D4>=40) возвращает значение TRUE, если хотя бы одна ячейка между C4 и D4 содержит значение больше или равное 40, в противном случае возвращает FALSE.
Наконец, ЕСЛИ(И(C4>=40,D4>=40),"Удачно","Неудачно") возвращает "Удачно", если обнаруживает ИСТИНА. В противном случае возвращается «Ошибка».

3. Использовать вложенную функцию ЕСЛИ для критериев типа И между несколькими диапазонами

Вы можете использовать вложенную функцию ЕСЛИ для обработки критериев типа И между несколькими диапазонами в Excel.

Давайте повторим тот же пример. Примите решение за каждого учащегося, сдал он экзамен или нет.

На этот раз мы сделаем это с помощью вложенной функции ЕСЛИ.

Выберите первую ячейку нового столбца и введите следующую формулу:

Затем перетащите маркер заполнения, чтобы скопировать эту формулу в остальные ячейки.

Мы снова оценили всех учащихся, сдавших оба предмета, как удовлетворительно, отмеченных желтым цветом в наборе данных.

⧪ Объяснение формулы:

Если C4>=40 равно TRUE, формула входит в IF(D4>=40, "Удачно", "Неудачно"), в противном случае возвращается "Неудачно".
Затем, если D4>=40 также имеет значение TRUE, функция возвращает "Пройдено", в противном случае возвращается "Не пройдено".
Таким образом, он возвращает "Удачно" только в том случае, если один из них сдал по обоим предметам, в противном случае он возвращает "Неудовлетворительно".

4. Используйте функцию IFS Excel вместо IF для критериев типа AND между несколькими диапазонами

Наконец, мы будем использовать функцию Excel IFS для работы с несколькими критериями типа ИЛИ вместо функции ЕСЛИ.

Здесь мы выполним задание Метода 1, решим за каждого ученика, провалился он или нет.

Выберите первую ячейку нового столбца и введите следующую формулу:

Затем перетащите маркер заполнения, чтобы скопировать эту формулу в остальные ячейки.

Видите ли, мы снова обозначили учащихся, не прошедших хотя бы по одному предмету, как "неудовлетворительных", отмеченных желтым цветом в наборе данных.

⧪ Объяснение формулы:

Функция IFS возвращает соответствующее значение с первым аргументом TRUE, в противном случае она возвращает ошибку N/A.
Содержимое, связанное с If C4: как использовать функцию ЕСЛИ в Excel с диапазоном значений

Заключение

Используя эти методы, мы можем использовать функцию ЕСЛИ Excel между несколькими диапазонами. У вас есть вопросы? Не стесняйтесь спрашивать нас.

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