Диапазон в MS Excel:

Обновлено: 07.07.2024

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

Но это руководство не об этом диапазоне.

Диапазон — это также математический термин, обозначающий диапазон в наборе данных (т. е. диапазон между минимальным и максимальным значением в данном наборе данных)

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

Что такое диапазон?

В заданном наборе данных диапазоном этого набора данных будет разброс значений в этом наборе данных.

В качестве простого примера: если у вас есть набор данных с оценками учащихся, где минимальный балл равен 15, а максимальный – 98, то разброс этого набора данных (также называемый диапазоном этого набора данных) будет быть 73

Диапазон — это не что иное, как разница между максимальным и минимальным значением этого набора данных.

Как рассчитать диапазон в Excel?

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

Но в большинстве случаев у вас будет случайный набор данных, который еще не отсортирован.

Найти диапазон в таком наборе данных также довольно просто.

Excel имеет функции для определения максимального и минимального значения диапазона (функция MAX и MIN).

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

Ниже приведена формула для расчета диапазона для этого набора данных:

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

Довольно просто… не так ли?

Расчет условного диапазона в Excel

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

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

Например, у вас может быть набор данных, в котором все значения ниже 100, но есть одно значение выше 500.

Если вы вычислите порядок для этого набора данных, это приведет к неверной интерпретации данных.

К счастью, в Excel есть много условных формул, которые могут помочь вам разобраться с некоторыми аномалиями.

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

Если вы внимательно посмотрите на эти данные, то заметите, что есть два магазина, в которых значения довольно низкие (Магазин 1 и Магазин 3).

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

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

В этом примере предположим, что я хочу игнорировать все те магазины, в которых объем продаж меньше 20 000.

Ниже приведена формула для поиска диапазона с условием:

В приведенной выше формуле вместо функции МИН я использовал функцию МИНИМН (это новая функция в Excel 2019 и Microsoft 365).

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

Итак, функция MINIFS обрабатывает весь набор данных, но при вычислении минимального значения учитывает только те значения, которые превышают 20 000.

Это гарантирует, что значения ниже 20 000 игнорируются, а минимальное значение всегда больше 20 000 (следовательно, игнорируются выбросы).

Обратите внимание, что MINIFS — это новая функция в Excel, доступная только в Excel 2019 и подписке на Microsoft 365.Если вы используете предыдущие версии, у вас не будет этой функции (и вы можете использовать формулу, описанную позже в этом руководстве)

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

Точно так же, как я использовал условную функцию MINIFS, вы также можете использовать функцию MAXIFS, если хотите избежать точек данных, которые являются выбросами в другом направлении (т. е. пары больших точек данных, которые могут исказить данные)< /p>

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

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

Заинтересованы в разработке решений, расширяющих возможности Office на различных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любые технологии веб-программирования, такие как HTML5, JavaScript, CSS3 и XML.

Примечания

Член Range по умолчанию перенаправляет вызовы без параметров на свойство Value, а вызовы с параметрами — на член Item. Соответственно, someRange = someOtherRange эквивалентно someRange.Value = someOtherRange.Value , someRange(1) — someRange.Item(1) и someRange(1,1) — someRange.Item(1,1) .

Следующие свойства и методы для возврата объекта Range описаны в разделе "Пример":

Пример

Используйте Range (arg), где arg называет диапазон, чтобы вернуть объект Range, представляющий одну ячейку или диапазон ячеек. В следующем примере значение ячейки A1 помещается в ячейку A5.

В следующем примере диапазон A1:H8 заполняется случайными числами путем задания формулы для каждой ячейки диапазона. При использовании без квалификатора объекта (объект слева от точки) свойство Range возвращает диапазон на активном листе. Если активный лист не является рабочим листом, метод не работает.

Используйте метод Activate объекта Worksheet, чтобы активировать рабочий лист, прежде чем использовать свойство Range без явного квалификатора объекта.

В следующем примере очищается содержимое диапазона с именем Criteria.

Если вы используете текстовый аргумент для адреса диапазона, вы должны указать адрес в нотации в стиле A1 (вы не можете использовать нотацию в стиле R1C1).

Используйте «Ячейки на листе», чтобы получить диапазон, состоящий из всех отдельных ячеек на листе. Вы можете получить доступ к отдельным ячейкам с помощью Item(row, column), где row — это индекс строки, а column — это индекс столбца. Элемент можно опустить, так как вызов перенаправляется на него членом Range по умолчанию. В следующем примере для ячейки A1 задается значение 24, а для ячейки B1 — 42 на первом листе активной книги.

В следующем примере задается формула для ячейки A2.

Хотя вы также можете использовать Range("A1") для возврата ячейки A1, могут быть случаи, когда свойство Cells более удобно, поскольку вы можете использовать переменную для строки или столбца. В следующем примере создаются заголовки столбцов и строк на листе Sheet1. Имейте в виду, что после активации рабочего листа свойство Cells можно использовать без явного объявления листа (оно возвращает ячейку на активном листе).

Хотя вы можете использовать строковые функции Visual Basic для изменения ссылок в стиле A1, проще (и лучше в программировании) использовать нотацию Cells(1, 1).

Используйте_выражение_.Cells, где выражение — это выражение, возвращающее объект Range, для получения диапазона с тем же адресом, состоящего из отдельных ячеек. В таком диапазоне вы получаете доступ к отдельным ячейкам через элемент (строка, столбец), где относительно левого верхнего угла первой области диапазона. Элемент можно опустить, так как вызов перенаправляется на него членом Range по умолчанию. В следующем примере задается формула для ячеек C5 и D5 первого листа активной книги.

Используйте Range (cell1, cell2), где cell1 и cell2 — объекты Range, определяющие начальную и конечную ячейки, чтобы вернуть Объект диапазона. В следующем примере задается стиль линии границы для ячеек A1:J10.

Учтите, что точка перед каждым вхождением свойства Cells требуется, если результат предыдущего оператора With должен быть применен к свойству Cells. В этом случае это указывает, что ячейки находятся на рабочем листе номер один (без точки свойство «Ячейки» возвращает ячейки на активном листе).

Используйте Строки на листе, чтобы получить диапазон, состоящий из всех строк на листе. Вы можете получить доступ к отдельным строкам через Item(row), где row — это индекс строки. Элемент можно опустить, так как вызов перенаправляется на него членом диапазона по умолчанию.

Недопустимо указывать второй параметр Item для диапазонов, состоящих из строк.Сначала вам нужно преобразовать его в отдельные ячейки с помощью Cells.

В следующем примере удаляются строки 4 и 10 первого листа активной книги.

Используйте столбцы на листе, чтобы получить диапазон, состоящий из всех столбцов на листе. Вы можете получить доступ к отдельным столбцам с помощью Item(row) [sic], где row — это индекс столбца, заданный в виде числа или адреса столбца в стиле A1. Элемент можно опустить, так как вызов перенаправляется на него членом диапазона по умолчанию.

Недопустимо указывать второй параметр Item для диапазонов, состоящих из столбцов. Сначала вам нужно преобразовать его в отдельные ячейки с помощью Cells.

В следующем примере удаляются столбцы "B", "C", "E" и "J" первого листа активной книги.

Используйте_выражение_.Rows, где выражение — это выражение, возвращающее объект Range, для получения диапазона, состоящего из строк в первой области диапазона. Вы можете получить доступ к отдельным строкам с помощью Item(row), где row — относительный индекс строки, начиная с верхней части первой области диапазона. Элемент можно опустить, так как вызов перенаправляется на него членом диапазона по умолчанию.

Недопустимо указывать второй параметр Item для диапазонов, состоящих из строк. Сначала вам нужно преобразовать его в отдельные ячейки с помощью Cells.

В следующем примере удаляются диапазоны C8:D8 и C6:D6 первого листа активной книги.

Используйте_выражение_.Columns, где выражение — это выражение, возвращающее объект Range, для получения диапазона, состоящего из столбцов в первой области диапазона. Вы можете получить доступ к отдельным столбцам с помощью Item(row) [sic], где row — это относительный индекс столбца слева от первой области диапазона, заданный как число или как адрес столбца в стиле A1. Элемент можно опустить, так как вызов перенаправляется на него членом диапазона по умолчанию.

В следующем примере удаляются диапазоны L2:L10, G2:G10, F2:F10 и D2:D10 на первом листе активной книги.

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

Используйте объединение (диапазон1, диапазон2, . ), чтобы получить диапазоны с несколькими областями, то есть диапазоны, состоящие из двух или более смежных блоков ячеек. В следующем примере создается объект, определенный как объединение диапазонов A1:B2 и C3:D4, а затем выбирается определенный диапазон.

Если вы работаете с выборками, содержащими более одной области, полезно использовать свойство Области. Он делит выделение из нескольких областей на отдельные объекты Range, а затем возвращает объекты в виде коллекции. Используйте свойство Count возвращаемой коллекции, чтобы проверить выделение, содержащее более одной области, как показано в следующем примере.

В этом примере используется метод AdvancedFilter объекта Range для создания списка уникальных значений и количества появлений этих уникальных значений в диапазоне столбца A.

Методы

Свойства

См. также

Поддержка и обратная связь

Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

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

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

Что такое диапазон в Excel и его формула?

Диапазон – это набор значений, разбросанных между максимальным значением и минимальным значением. Диапазон – это разница между самым большим (максимальным) значением и самым коротким (минимальным) значением в заданном наборе данных в математическом выражении.

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

Как найти диапазон в Excel?

Поиск диапазона – очень простой процесс, и он рассчитывается с помощью встроенных в Excel функций MAX и MIN. Давайте разберемся, как найти диапазон в Excel на нескольких примерах.

Мы привели ниже список значений:

23, 11, 45, 21, 2, 60, 10, 35

Самое большое число в указанном выше диапазоне — 60, а наименьшее — 2.

Таким образом, диапазон = 60-2 = 58

Пояснение:

В приведенном выше примере диапазон равен 58 в заданном наборе данных, что определяет диапазон набора данных. Это дало вам визуальное представление о диапазоне, поскольку мы ищем самую высокую и самую низкую точку.
Если набор данных большой, это дает вам широкое распространение результата.
Если набор данных небольшой, это дает вам близко центрированный результат.

Процесс определения диапазона в Excel

Excel Advanced Training (16 курсов, более 23 проектов) 16 онлайн-курсов | 23 практических проекта | 140+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,8 (10 130 оценок)

Использование функции MAX:

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

Мы задали некоторый набор значений:

Чтобы найти максимальное значение из набора данных, мы применим здесь функцию MAX, как показано на скриншоте ниже:

Нажмите Enter, и вы получите максимальное значение. Результат показан ниже:

Использование функции MIN:

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

Для вычисления минимального значения из данного набора данных мы применим здесь функцию MIN, как показано на снимке экрана ниже.

Нажмите клавишу ENTER, и вы получите минимальное значение. Результат приведен ниже:

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

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

Для этого мы снова возьмем пример, чтобы понять процесс.

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

Теперь, чтобы определить диапазон объема продаж в приведенном выше наборе данных, мы рассчитаем диапазон. Для этого мы будем следовать той же процедуре, что и в приведенных выше примерах.

Мы будем применять функции MAX и MIN для расчета максимальной и минимальной суммы продаж в данных.

Для определения диапазона объема продаж мы применим следующую формулу:

Диапазон = максимальное значение – минимальное значение

См. приведенный ниже снимок экрана:

Нажмите клавишу Enter, и вы получите диапазон набора данных. Результат показан ниже:

Как видно на снимке экрана выше, мы применили формулы MAX и MIN в одной строке и, вычислив разницу их результатов, нашли диапазон набора данных.

Что нужно помнить

Если значения доступны в несмежных ячейках, вы хотите узнать диапазон; вы можете передать адрес ячейки отдельно через запятую в качестве аргумента функций MAX и MIN.
Можно сократить количество шагов для расчета диапазона, применив функции MAX и MIN в одной строке. (см. пример 4 для справки)

Рекомендуемые статьи

Это руководство по диапазону в Excel. Здесь мы обсуждаем, как найти диапазон в Excel вместе с примерами Excel и загружаемым шаблоном Excel. Вы можете узнать больше об Excel из следующих статей —

Существуют различные способы определения диапазона в MS Excel.

<р>1. Выбор полной отдельной строки

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

<р>2. Выбор полных нескольких строк

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

<р>3. Выбор всего столбца

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

<р>4. Выбор полных нескольких столбцов

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

<р>5. Выбор ячейки

Вы можете выбрать конкретную ячейку, просто нажав на нее.

<р>6. Выделение блока ячеек размером 3 x 3

Предположим, вы хотите выделить всю ячейку, начиная с «B2» и заканчивая «D4». Для этого вы щелкните и перетащите мышь от ячейки B2 к ячейке D4 по диагонали, что выделит все 9 ячеек, попадающих в диапазон.

<р>7. Выбор случайных ячеек

Вы можете выбрать случайные ячейки, удерживая нажатой клавишу «Ctrl» и нажимая на нужные ячейки.

<р>8. Заполнение диапазона

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

Тот же метод можно использовать для автоматического заполнения значений. Например, вы хотите поместить порядковый номер от 1 до 100 в столбец A. Теперь вместо того, чтобы вводить числа один за другим, вы будете вводить 1 в ячейке A1 и 2 в ячейке A2. Затем вы выберете обе ячейки, щелкните и перетащите опорную точку вертикально в столбце A, пока не достигнете ячейки A100. Вы заметите, что весь диапазон будет автоматически заполнен увеличивающимися числами. Это действие сэкономит вам много времени и усилий.

<р>9. Копирование и вставка диапазона

Скопировать диапазон из одного места и вставить его в другое место в MS Excel довольно легко и просто. Во-первых, вам нужно выбрать диапазон, из которого вы хотите скопировать текст. Например, у вас есть таблица со значениями от ячейки A1 до ячейки G6, и вы должны сделать ее копию в ячейке J1 до P6. Прежде всего, вы выберете таблицу, щелкнув ячейку A1 и перетащив ее по диагонали к ячейке G6, определив свой диапазон. Затем щелкните правой кнопкой мыши выделенный текст и выберите «Копировать» в контекстном меню.

Затем щелкните правой кнопкой мыши J1 и снова выберите «Вставить» в контекстном меню. Ваш диапазон выделенного текста будет скопирован с J1 по P6.

<р>10. Вырезание и вставка диапазона

Вырезать диапазон из одного места и вставить его в другое место в MS Excel довольно легко и просто. Во-первых, вам нужно выбрать диапазон, из которого вы хотите вырезать текст. Например, у вас есть таблица со значениями от ячейки A1 до ячейки G6, и вы должны удалить ее из ее текущего местоположения и поместить в ячейку J1 до P6. Прежде всего, вы выберете таблицу, щелкнув ячейку A1 и перетащив ее по диагонали к ячейке G6, определив свой диапазон. Затем щелкните правой кнопкой мыши выделенный текст и выберите «Вырезать» в контекстном меню.

Затем щелкните правой кнопкой мыши J1 и снова выберите «Вставить» в контекстном меню. Ваш диапазон выделенного текста будет перемещен с J1 на P6.

<р>11. Перемещение диапазона

Чтобы переместить выбранный диапазон в новое место, один из способов описан в разделе «Вырезание и вставка диапазона». Тем не менее, есть еще один способ, на самом деле быстрый способ сделать это. Вы выберете нужный диапазон и вместо того, чтобы наводить указатель мыши на опорную точку, поместите ее на линию границы вашего выбора, щелкните и перетащите весь диапазон в новое место.

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