Как установить диапазон значений в Excel

Обновлено: 04.07.2024

Если вы работаете с электронными таблицами Excel, это может означать большую экономию времени и эффективность.

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

Это руководство охватывает:

Именованные диапазоны в Excel — введение

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

Аналогично в Excel вы можете дать имя ячейке или диапазону ячеек.

Теперь вместо ссылки на ячейку (например, A1 или A1:A10) вы можете просто использовать присвоенное ей имя.

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

В этом наборе данных, если вам нужно обратиться к диапазону с датой, вам придется использовать A2: A11 в формулах. Точно так же для торговых представителей и продаж вам придется использовать B2:B11 и C2:C11.

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

Именованные диапазоны Excel упрощают обращение к наборам данных в Excel.

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

Вы также можете создать имя для отдельной ячейки. Например, если в ячейке указан процент комиссии за продажу, вы можете назвать эту ячейку «Комиссия».

Преимущества создания именованных диапазонов в Excel

Вот преимущества использования именованных диапазонов в Excel.

Используйте имена вместо ссылок на ячейки

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

Например, вы можете использовать =СУММ(ПРОДАЖИ) вместо =СУММ(C2:C11) для приведенного выше набора данных.

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

Количество продаж со значением более 500: =СЧЁТЕСЛИ(Продажи,">500″)
Сумма всех продаж, совершенных Томом: =СУММЕСЛИ(СУММЕСЛИ(Представитель по продажам, "Том",Продажи)
Комиссия, полученная Джо (продажи Джо, умноженные на комиссионный процент):
=СУММЕСЛИ(SUMIF(SalesRep»,Joe»,Sales)*Commission

Вы согласитесь, что эти формулы легко создавать и легко понимать (особенно когда вы делитесь ими с кем-то еще или пересматриваете сами).

Нет необходимости возвращаться к набору данных для выбора ячеек

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

Вы можете просто ввести пару букв этого именованного диапазона, и Excel покажет соответствующие именованные диапазоны (как показано ниже):

Именованные диапазоны делают формулы динамичными

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

Например, в случае комиссии с продаж вместо значения 2,5 % можно использовать именованный диапазон.

Теперь, если позже ваша компания решит увеличить комиссию до 3%, вы можете просто обновить Именованный диапазон, и все расчеты будут автоматически обновлены, чтобы отразить новую комиссию.

Как создавать именованные диапазоны в Excel

Вот три способа создания именованных диапазонов в Excel:

Вот шаги для создания именованных диапазонов в Excel с помощью определения имени:

Это создаст именованный диапазон SALESREP.

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

Например, в приведенном ниже наборе данных, если вы хотите быстро создать три именованных диапазона (Дата, Sales_Rep и Sales), вы можете использовать метод, показанный ниже.

Вот шаги, чтобы быстро создать именованные диапазоны из набора данных:

Это создаст три именованных диапазона — Date, Sales_Rep и Sales.

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

Соглашение об именах для именованных диапазонов в Excel

Существуют определенные правила именования, которые необходимо знать при создании именованных диапазонов в Excel:

Первым символом именованного диапазона должна быть буква и символ подчеркивания (_) или обратная косая черта (\). Если это что-то другое, он покажет ошибку. Остальные символы могут быть буквами, цифрами, специальными символами, точкой или символом подчеркивания.
Вы не можете использовать имена, которые также представляют ссылки на ячейки в Excel. Например, вы не можете использовать AB1, так как это также ссылка на ячейку.
Вы не можете использовать пробелы при создании именованных диапазонов. Например, у вас не может быть торгового представителя в качестве именованного диапазона. Если вы хотите объединить два слова и создать именованный диапазон, используйте для его создания символ подчеркивания, точку или символы верхнего регистра. Например, у вас может быть Sales_Rep, SalesRep или SalesRep.
- При создании именованных диапазонов Excel одинаково обрабатывает прописные и строчные буквы. Например, если вы создадите именованный диапазон ПРОДАЖИ, вы не сможете создать другой именованный диапазон, такой как «продажи» или «Продажи».
Слишком много именованных диапазонов в Excel? Не волнуйтесь

Иногда при работе с большими наборами данных и сложными моделями в Excel может создаваться множество именованных диапазонов.

Что делать, если вы не помните название созданного вами именованного диапазона?

Не волнуйтесь — вот несколько полезных советов.

Получение имен всех именованных диапазонов

Вот как получить список всех созданных вами именованных диапазонов:

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

Отображение совпадающих именованных диапазонов

Если вы уже создали именованный диапазон, вы можете изменить его, выполнив следующие действия:

Полезные ярлыки именованных диапазонов (сила F3)

Вот несколько полезных сочетаний клавиш, которые пригодятся при работе с именованными диапазонами в Excel:
- Чтобы получить список всех именованных диапазонов и вставить его в формулу: F3.
- Чтобы создать новое имя с помощью диалогового окна диспетчера имен, нажмите Control + F3.
- Чтобы создать именованные диапазоны из выделенного: Control + Shift + F3.
Создание динамических именованных диапазонов в Excel

До сих пор в этом руководстве мы создавали статические именованные диапазоны.

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

Например, если A1:A10 называется "Продажи", это всегда будет относиться к A1:A10.

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

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

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

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

Этот вид динамического именованного диапазона можно создать с помощью функции Excel ИНДЕКС. Вместо указания ссылок на ячейки при создании именованного диапазона мы указываем формулу. Формула автоматически обновляется при добавлении или удалении данных.

Давайте посмотрим, как создавать динамические именованные диапазоны в Excel.

Предположим, у нас есть данные о продажах в ячейке A2:A11.

Вот шаги для создания динамических именованных диапазонов в Excel:

Теперь у вас есть динамический именованный диапазон с названием "Продажи". Он будет автоматически обновляться всякий раз, когда вы добавляете в него данные или удаляете из него данные.

Как работают динамические именованные диапазоны?

Чтобы объяснить, как это работает, вам нужно немного больше узнать о функции ИНДЕКС в Excel.

Большинство людей используют ИНДЕКС для возврата значения из списка на основе номера строки и столбца.

Но у функции ИНДЕКС есть и другая сторона.

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

Например, вот формула, которую мы использовали для создания динамического именованного диапазона:

INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,"<>"&"") –> Ожидается, что эта часть формулы вернет значение (которое будет 10-м значение из списка, учитывая, что элементов десять).

Однако при использовании перед ссылкой (= $A$2: ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,"<>"&"")) возвращается ссылка на ячейку вместо значения.

Следовательно, здесь возвращается =$A$2:$A$11

Если мы добавим два дополнительных значения в столбец продаж, он вернет =$A$2:$A$13

При добавлении новых данных в список функция Excel СЧЁТЕСЛИ возвращает количество непустых ячеек в данных. Этот номер используется функцией ИНДЕКС для извлечения ссылки на ячейку последнего элемента в списке.

Примечание:
- Это будет работать, только если в данных нет пустых ячеек.
- В приведенном выше примере я назначил большое количество ячеек (A2:A100) для формулы именованного диапазона. Вы можете изменить это в зависимости от вашего набора данных.
Вы также можете использовать функцию OFFSET для создания динамических именованных диапазонов в Excel, однако, поскольку функция OFFSET нестабильна, это может привести к медленной работе книги Excel. ИНДЕКС, с другой стороны, является полузависимым, что делает его лучшим выбором для создания динамических именованных диапазонов в Excel.

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

Загрузить книгу с практиками

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

3 простых способа создать диапазон чисел в Excel

Способ 1. Использование параметра проверки данных для создания диапазона чисел в Excel

Давайте сначала познакомимся с нашей рабочей тетрадью. В этой таблице я использовал 3 столбца и 7 строк для представления имен, пола и возраста некоторых сотрудников. Теперь я создам диапазон для столбца «Возраст», чтобы никто не мог непреднамеренно ввести недопустимое число. Можно предположить, что возраст сотрудника не может быть больше 100 лет.

Шаг 1:

⭆ Выберите весь столбец Возраст.

⭆ Затем перейдите в раздел «Данные» > «Инструменты данных» > «Проверка данных».

Откроется диалоговое окно.

Шаг 2:

⭆ Перейти в настройки

⭆ Выберите «Целое число» в раскрывающемся списке «Разрешить».

⭆ Выберите «Между» на раскрывающейся вкладке «Данные».

⭆ Снимите отметку с параметра «Игнорировать пустое».

⭆ Теперь введите минимальное и максимальное число. Я установил здесь от 0 до 100.

⭆ Затем нажмите OK

Теперь вставьте любое число в столбец Возраст. Он обнаружит действительность. Я поставил 35 в ячейку D5, и она стала действительной. Но когда я вставил 105 в ячейку D6, открылось диалоговое окно, показывающее, что данные не совпадают с проверкой.

Способ 2. Вставка функции для создания диапазона чисел для присвоения значения или категории в Excel

В этом методе я покажу, как применить функцию ЕСЛИ для создания диапазона чисел для присвоения значения или категории в Excel. Здесь я использовал новый набор данных с двумя столбцами. Столбцы имеют заголовки с номером и присвоенным значением. И есть несколько случайных чисел в 3 последовательных строках. Я хочу присвоить номер (пусть это будет «7») для ячейки C5, если число в ячейке B5 находится в диапазоне от 0 до 1000.

Для следующих двух строк я хочу назначить 9 для диапазона от 1001 до 2000 и 11 для диапазона от 2001 до 3000.

Шаг 1:

⭆ Выберите ячейку C5 и введите приведенную ниже формулу.

👉 Как работает формула?
- Первая комбинация функций ЕСЛИ и И проверяет, находится ли входное значение в диапазоне от 0 до 1000. Если это так, то входное значение будет присвоено ячейке.
- Если первое условие не выполняется, то вторая комбинация функций ЕСЛИ и И проверит, находится ли входное значение между 1001 и 2000. Если это так, формула позволит вам ввести значение, в противном случае она выиграет. 'т.
- Аналогично для диапазона чисел от 2001 до 3000 третья комбинация функций ЕСЛИ и И позволит вам ввести определенное числовое значение.
- Если ни одно из условий не соответствует, отображается «0».
⭆ Нажмите кнопку Enter.

Посмотрите на изображение ниже, где показано присвоенное значение.

Шаг 2:

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

📓 Примечание. Эта формула также может помочь присвоить данным текстовый формат. Примените приведенную ниже формулу:

Способ 3. Использование функции ВПР для создания диапазона чисел в Excel

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

Шаг 1:

⭆ В ячейке C12 введите приведенную ниже формулу:

⭆ Теперь просто нажмите кнопку Enter. Он покажет присвоенное значение.

Шаг 2:

⭆ Теперь просто используйте инструмент AutoFill Handle, чтобы скопировать формулу для следующих двух строк с помощью мыши.

Заключение

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

Диапазон в Excel – это разница между максимальным и минимальным лимитом доступных чисел в Excel. Например, у нас есть около 10 различных чисел, случайно выбранных в списке в Excel. Чтобы вычислить диапазон для этих чисел, нам нужно найти верхнее и нижнее значения, используя функции MAX и MIN в списке этих ячеек. Как только мы получим максимальное и минимальное значения из этих чисел, вычтем максимальное значение из минимального значения. Возвращаемое число будет диапазоном.

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

В Excel широко используются два типа диапазонов, которые показаны ниже:

Симметричный диапазон: диапазон, состоящий из всех ячеек, расположенных рядом друг с другом. Такой диапазон обычно отображается в виде квадрата или прямоугольника в электронной таблице, когда он выделен. Диапазон, показанный на изображении, будет (A1:C4)

Неправильный диапазон: диапазон, состоящий из ячеек, которые не являются смежными и могут иметь неправильную геометрическую форму при выделении. Диапазон, выделенный на изображении, будет (A1:C4, E1,E4, B6,A7)

Примеры функции диапазона в Excel

Теперь диапазон сам по себе бесполезен, поскольку нам нужно получать информацию из данных диапазона. Таким образом, формулы используются с диапазонами ячеек, которые добавляют операцию, которую мы хотим выполнить, к данным из диапазона. Например, если мы хотим найти сумму чисел в ячейках от A1 до C4, мы должны использовать =СУММ(A1:C4)

1) Поиск максимального и минимального значений в диапазоне ячеек: Мы используем следующие функции, когда ищем минимальное и максимальное значения в диапазоне ячеек. Обратите внимание, что это даст нам математический результат, а не максимум и минимум, определенные номером ячейки.

Обучение Excel (21 курс, более 9 проектов) 21 онлайн-курс | 9 практических проектов | 110+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,9 (10 089 оценок)
- Для максимального значения: мы будем использовать функцию =MAX(диапазон ячеек), как показано ниже.
- Для минимума: мы будем использовать функцию =MIN(диапазон ячеек), как показано ниже.
2) Допустим, нас интересуют не только минимум и максимум в выделенном диапазоне, но также верхние и нижние k чисел в диапазоне. Мы можем использовать следующие функции для их вычисления.
- Для верхнего числа k, скажем, k=3, что означает третье по величине число в диапазоне, мы должны использовать функцию =НАИБОЛЬШИЙ(Массив ячеек,k) для симметричных диапазонов или =НАИБОЛЬШИЙ((Диапазон ячеек), k) для нерегулярных диапазонов, как показано ниже.
- Очень похожей функцией для нахождения k-го наименьшего числа в диапазоне будет использование =МАЛЕНЬКИЙ(Массив ячеек, k) для симметричного диапазона или =МАЛЕНЬКИЙ((Диапазон ячеек),k) для нерегулярного диапазона.
Теперь мы рассмотрим, как определять и использовать диапазоны в Excel. Во-первых, нам нужны данные для работы. Это может быть что угодно в электронной таблице, от букв до цифр или их комбинации. Для иллюстраций, сопровождающих это обсуждение, я использую образец из производственной базы данных, в которой хранятся данные о том, сколько деталей производится за год.

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

MAX(диапазон ячеек)-MIN(диапазон ячеек)

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

Мы находим, что ассортимент продукции составляет 368 деталей. Теперь, если мы хотим выяснить вхождение определенного значения в диапазон или диапазон значений в пределах диапазона, мы используем другую функцию, называемую СЧЁТЕСЛИ. Эта функция имеет следующий синтаксис:

Предположим, мы хотим найти месяц, в котором мы наткнулись более чем на 300 деталей.Формула будет =СЧЁТЕСЛИ(C2:C23",>300″)

Мы выяснили, что за 7 месяцев производство составило более 300 деталей. Мы также можем узнать, был ли у нас какой-либо месяц, который был меньше определенного числа, предположим, 100. Мы могли бы использовать вложенную формулу СЧЁТЕСЛИ в операторе ЕСЛИ, чтобы получить ответ Да или Нет, например:

=ЕСЛИ(СЧЁТЕСЛИ(диапазон, "значение"), "Да", "Нет")

Это будет выглядеть так:

Результатом будет "Нет", так как ни один из производственных номеров в диапазоне не меньше 100. Можно использовать вариант этого, чтобы определить, есть ли у нас какой-либо производственный номер в определенном значении. Это будет следующим образом:

СЧЁТЕСЛИ(диапазон, "*"&значение&"*") или СЧЁТЕСЛИ(диапазон, значение)

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

Мы можем использовать функцию ПОИСКПОЗ вместо СЧЁТЕСЛИ, если мы хотим найти количество значений больше или меньше заданного значения.

В приведенном выше примере мы используем функцию ПОИСКПОЗ, чтобы найти количество месяцев, в течение которых было произведено менее 300 деталей.

Что нужно помнить
- Мы должны сортировать данные в порядке возрастания или убывания везде, где это возможно, чтобы упростить операции при использовании диапазонов.
- Кавычки ("") и звездочка (*) используются в формулах всякий раз, когда мы ищем подстроки или определенные диапазоны текста в пределах диапазона.
- Нестандартные диапазоны чаще всего используются в бизнесе. Поэтому, когда это возможно, мы должны использовать таблицы для классификации данных перед выполнением каких-либо операций с ними.
- Необходимо отметить, что диапазоны можно выделять вручную, а Excel отображает количество ячеек в нем в виде числа внизу; однако мы можем узнать количество строк или столбцов в диапазоне, используя следующие функции:
Обычно эти две функции не требуются, но они полезны для больших таблиц и нескольких баз данных, а также для записи макросов.

Заключение

Знание диапазона в Excel является важным условием для работы с данными. Диапазон также используется при записи макросов и кодировании VBA, поэтому всестороннее понимание диапазона является обязательным для всех, кто использует Excel.

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

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

Всякий раз, когда мы говорим о диапазоне в 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 121 оценка)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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