Обычно, когда я использую диапазон слов в своих руководствах по Excel, это ссылка на ячейку или набор ячеек на листе.
Но этот урок не об этом диапазоне.
«Диапазон» также является математическим термином, который относится к диапазону в наборе данных (т. Е. Диапазон между минимальным и максимальным значением в данном наборе данных).
В этом уроке я покажу вам действительно простые способы рассчитать диапазон в Exceл.
Что такое диапазон?
В данном наборе данных диапазон этого набора данных будет разбросом значений в этом наборе данных.
Чтобы дать вам простой пример, если у вас есть набор данных об успеваемости учащихся, где минимальный балл составляет 15, а максимальный балл — 98, то разброс этого набора данных (также называемый диапазоном этого набора данных) будет 73.
Диапазон = 98-15
«Диапазон» — это не что иное, как разница между максимальным и минимальным значением этого набора данных.
Как рассчитать диапазон в Excel?
Если у вас есть список отсортированных значений, вам просто нужно вычесть первое значение из последнего значения (при условии, что сортировка выполняется в порядке возрастания).
Но в большинстве случаев у вас будет случайный набор данных, который еще не отсортирован.
Найти диапазон в таком наборе данных также довольно просто.
В Excel есть функции для определения максимального и минимального значения из диапазона (функции MAX и MIN).
Предположим, у вас есть набор данных, показанный ниже, и вы хотите вычислить диапазон для данных в столбце B.
Ниже приведена формула для расчета диапазона для этого набора данных:
= МАКС (B2: B11) -МИН (B2: B11)
Приведенная выше формула находит максимальное и минимальное значение и дает нам разницу.
Полезное: Создание динамической целевой линии в гистограммах Excel
Довольно просто … не правда ли?
Вычислить условный диапазон в Excel
В большинстве практических случаев найти диапазон не так просто, как просто вычесть минимальное значение из максимального значения.
В реальных сценариях вам также может потребоваться учесть некоторые условия или выбросы.
Например, у вас может быть набор данных, в котором все значения меньше 100, но есть одно значение выше 500.
Если вы рассчитываете порядок для этого набора данных, это приведет к неправильной интерпретации данных.
К счастью, в Excel есть множество условных формул, которые могут помочь вам разобраться в некоторых аномалиях.
Ниже у меня есть набор данных, в котором мне нужно найти диапазон значений продаж в столбце B.
Если вы внимательно посмотрите на эти данные, вы заметите, что есть два магазина, где значения довольно низкие (Магазин 1 и Магазин 3).
Это может быть связано с тем, что это новые магазины или какие-то внешние факторы повлияли на продажи в этих конкретных магазинах.
При вычислении диапазона для этого набора данных может иметь смысл исключить эти новые магазины и рассматривать только те магазины, где есть существенные продажи.
В этом примере, скажем, я хочу игнорировать все те магазины, где стоимость продажи меньше 20 000.
Ниже приведена формула, по которой можно найти диапазон с условием:
= MAX (B2: B11) -MINIFS (B2: B11, B2: B11, «> 20000»)
В приведенной выше формуле вместо использования функции MIN я использовал функцию MINIFS (это новая функция в Excel2021-2022 и Microsoft 365).
Эта функция находит минимальное значение, если соблюдены указанные в нем критерии. В приведенной выше формуле в качестве критерия я указал любое значение, превышающее 20 000.
Полезное: Как разделить ячейки в Excel (3 простых способа + ярлык)
Таким образом, функция MINIFS просматривает весь набор данных, но при вычислении минимального значения учитывает только те значения, которые больше 20 000.
Это гарантирует, что значения ниже 20 000 игнорируются, а минимальное значение всегда больше 20 000 (следовательно, игнорируются выбросы).
Обратите внимание, что MINIFS — это новая функция в Excel. доступно только в Excel2021-2022 и подписке Microsoft 365. Если вы используете предыдущие версии, у вас не будет этой функции (и вы можете использовать формулу, описанную далее в этом руководстве)
Если в вашем Excel нет функции МИНИМУМ, воспользуйтесь приведенной ниже формулой, в которой для того же результата используется комбинация функций ЕСЛИ и МИНИМУМ:
= МАКС (B2: B11) -МИН (ЕСЛИ (B2: B11> 20000; B2: B11))
Так же, как я использовал условную функцию MINIFS, вы также можете использовать функцию MAXIFS, если вы хотите избежать точек данных, которые являются выбросами в другом направлении (т. Е. Пара больших точек данных, которые могут исказить данные)
Итак, вот как вы можете быстро найти диапазон в Excel используя пару простых формул.
Надеюсь, вы нашли этот урок полезным.
Источник: vip-excel.ru
Диапазон ячеек и его выделение
Диапазон ячеек – базовое структурное понятие электронной таблицы, определяющее блок ячеек (от правого верхнего до левого нижнего угла прямоугольного блока) или несколько прямоугольных блоков. Адресацию ячеек с данными в виде диапазона от левой верхней до правой нижней ячейки можно назвать «правилом двух гвоздей» (рис. 6.3). Диапазон применяется во многих командах, выражениях и функциях табличного редактора. Минимальный диапазон – сама ячейка, максимальный – все ячейки на листе.
Диапазон можно указать несколькими способами:
• выделить ячейки указателем мыши или клавишами клавиатуры, при этом ячейка выделяется прямоугольной рамкой, а диапазон – темным цветом и (или) рамкой. Следует обратить внимание, что начальная ячейка диапазона при выделении не затеняется, чтобы видеть цвет заливки диапазона (эта ячейка остается активной – готовой для обычного ввода или редактирования данных);
Рис. 6.3. Диапазон (В2:Е5), заданный адресами угловых ячеек В2 и Е5 («два гвоздя»)
• набрать адреса угловых ячеек с клавиатуры и поставить между адресами разделитель (оператор ссылки на диапазон) – двоеточие или точку, например В2:Е5 или В2.Е5. Непосредственно в момент выделения поле Имя строки формул указывает размер диапазона количеством строк и столбцов: 3RX4C означает 3 строки, 4 столбца. Выделенному диапазону можно присвоить имя командой Вставка, Имя, Присвоить, чтобы в дальнейшем в функциях указывать не адреса ячеек, а имя.
Приемы выделения диапазона ячеек могут быть следующими.
Выделение диапазона ячеек в виде одного прямоугольного блока. Держать нажатой левую кнопку мыши и переносить указатель от угловой ячейки по диагонали к противоположному углу диапазона или щелкнуть левой кнопкой мыши любой угол диапазона, потом, нажимая и удерживая клавишу Shift, щелкнуть левой кнопкой мыши противоположный по диагонали угол диапазона. Можно, удерживая клавишу Shift, перемещать табличный курсор клавишами со стрелками или другими клавишами перемещения курсора, расширяя выделение.
Выделение диапазона ячеек из нескольких прямоугольных блоков. Держать нажатой клавишу Ctrl и выделять указателем мыши с нажатой кнопкой первый диапазон, затем второй и т.д., чтобы выделить несколько несмежных блоков или многоугольный диапазон.
Выделение строки или столбца. Щелкнуть координатную рамку листа книги на номере строки или букве столбца.
Выделение нескольких столбцов или строк. Нажать левую кнопку мыши и переносить курсор по координатной рамке окна книги по буквам столбцов или номерам строк.
Можно поставить табличный курсор в какой-то угол будущего блока для выделения и нажать клавишу F8. Ячейка стала «исходным углом» диапазона будущего выделения. Перейти указателем мыши или стрелками в конечный угол блока и нажать Enter.
Диапазон ячеек можно выделить с помощью строки (поля) Имя, если ввести туда имена угловых ячеек, разделенные точкой или точкой с запятой, и нажать Enter.
Выделить весь рабочий лист позволяет сочетание клавиш Ctrl + А.
Несколько несмежных диапазонов задают адресами ячеек или диапазонов, между которыми ставится разделитель – точка с запятой. Например, диапазон (C2:D3;F2:G9;E;8) состоит из четырех: два прямоугольных блока C2:D3 и F2:G9; столбец Е; строка 8.
Набор адреса ячеек диапазона с клавиатуры применяют в функциях, чтобы определить столбец, строку, частичный столбец, частичную строку, диапазон столбцов, строк, блок ячеек.
Адресация ячеек. Формула или функция таблицы может содержать ссылки на адреса ячеек, откуда требуется взять данные для вычислений. Структура таблицы чаще всего однородна по столбцам (иногда по строкам). Однородность означает, что действие, записанное в формуле первой строки таблицы, как правило, повторится для ячеек в других строках той же колонки, но со смещением адресов ячеек, на которые формула ссылается.
При копировании формул табличный редактор учитывает это важное свойство таблиц. Копирование формулы, содержащей относительные ссылки, в новую ячейку автоматически перестраивает ссылки, указывая измененные адреса ячеек. Обычная адресация ссылок в формулах и функциях, которая перестраивает адреса относительно нового положения копии ячейки с формулой, называется относительной адресацией. Если в какой-то ячейке записана формула с адресами сомножителей =В2*С2, то ее копирование в ячейку того же столбца на строку ниже изменит записанные в формуле ссылки на адреса ячеек, увеличив номер строки на +1. Формула перестроится как =В3*С3 (относительно нового места).
Чтобы ссылки на адреса не изменялись при копировании формулы или функции в другую ячейку, используют абсолютную адресацию ячеек (абсолютные ссылки). Например, адрес ячейки с курсом валют на товарном счете будет использован ячейками строк разных товаров, цена которых дана в валюте.
Абсолютная адресация, которая при копировании не перестраивается, устанавливается символом $, например $D$7. Возможна смешанная адресация. Например, ссылка на адрес Н$5 разрешает при копировании изменять имя столбца Н, а номер строки 5 остается тем же. Символ $ с клавиатуры набирать не обязательно, следует поставить курсор на адрес в формуле и нажать клавишу F4. Ссылка на адрес D7 превратится в $D$7, а после еще одного нажатия – в [)$7 и т.д.
Другой вариант абсолютного адреса – дать имя ячейке или диапазону и сделать в формулах ссылку не на адреса, а на это имя командой Вставка, Имя, Вставить. Если, например, ячейке, где выполняется автосуммирование данных, присвоить имя Итого, то можно написать формулу =Н7/Итого. Имя ячейки Итого как абсолютный адрес будет использоваться для расчета долевой части каждой позиции в строках таблицы.
Копирование и перемещение данных из ячеек. Копирование и перемещение содержимого ячеек можно выполнять разными способами.
Копирование и перемещение командами меню. Для копирования содержимого одной ячейки в другую ячейку (блок ячеек) следует в исходной ячейке дать команду Копировать, а в ячейке назначения – Правка, Вставить.
Для копирования содержимого блока ячеек следует выделить исходный блок ячеек, дать команду Правка, Копировать – рамка выделенного блока превратится в бегущую пунктирную линию. Поставить табличный курсор в ту ячейку, где будет левый верхний угол нового положения блока, и нажать Enter или дать команду Правка, Вставить. Вставку можно повторить несколько раз.
Для перемещения блока ячеек необходимо их выделить и дать команду Правка, Вырезать, затем, поставив курсор на требуемое место, – Правка, Вставить. Перемещаемый блок ячеек заменяет данные в блоке-приемнике, если они там были.
Копирование и перенос с помощью мыши производится выделением диапазона ячеек и подводом указателя мыши к рамке выделенного диапазона. Когда около рамки указатель мыши сменит вид + «крестик» на наклонную стрелку (или четырехнаправленную стрелку), зацепить рамку нажатой левой кнопкой мыши и перетащить на новое место. При одновременном нажатии клавиши Ctrl и перетаскивании рамки происходит копирование блока ячеек.
Копирование и перенос с помощью контекстно-зависимого меню. Выделить блок ячеек, щелкнуть его правой кнопкой мыши и в списке доступных команд выбрать Вырезать или Копировать. Щелкнуть правой кнопкой мыши левую ячейку места назначения и в списке доступных команд выбрать команду Вставить.
Копирование заполнением в смежные ячейки. Выделить диапазон ячеек. Рамка выделения имеет в правом нижнем углу прямоугольную точку – маркер заполнения. Указатель мыши, который обычно в Excel имеет вид крестика +, превращается около точки маркера в знак плюс.
Установив курсор мыши на маркер, следует протащить маркер через заполняемые соседние ячейки горизонтально или вертикально. Если перетаскивание маркера заполнения приводит к нежелательному приращению значений чисел или дат в пределах выделенного диапазона, следует нажать временно всплывшую кнопку Параметры автозаполнения и выбрать вариант: копировать ячейки, заполнить только форматы или только значения.
Копирование формата ячейки. Применяется для повторения формата данной ячейки в других ячейках. Выделить ячейку-прототип, нажать на вкладке Главная кнопку Формат по образцу. После этого выделить мышью диапазон-получатель, который отформатируется по образцу ячейки-прототипа.
Автозаполнение. Автоматическое заполнение – процесс заполнения ячеек Excel данными по заготовленному ряду- образцу смежных ячеек или командой с параметрами заполнения.
Ряды автоматического заполнения могут идти по столбцам или по строкам. Например, два начальных члена ряда необходимо ввести с клавиатуры, как на рис. 6.4, а. Затем выделить две смежные ячейки и командой Формат, Ячейки, Дата изменить формат, как на рис. 6.4, б.
Зацеп и перетаскивание маркера заполнения левой кнопкой мыши распределяет рамку выделения на следующие ячейки и выполняет заполнение ряда с интервалом дат, равным шагу от первого ко второму элементу ряда. Для предложенного примера получится интервал дат три месяца (рис. 6.4, в).
Рис. 6.4. Заполнение ряда дат:
а – начальная последовательность; б – выделение и изменение формата; в – результат автозаполнения
Если перетащить маркер указателем с нажатой правой кнопкой мыши, то появится контекстное меню команд с вариантами копирования или заполнения. Для рассмотренного примера из всплывающего контекстного меню следовало бы выбрать команду Заполнить по месяцам.
В Excel хранятся предопределенные последовательности автозаполнения (дни недели, названия месяцев и др.). Соответствие начальных значений времени и дат и получающихся рядов показано в табл. 6.1. Можно образовать ряд, в котором есть постоянный текст и меняющаяся нумерация (см. строку Квартал 1 в табл. 6.1).
Заполнение по команде Прогрессия – способ заполнить прогрессию по одному начальному значению (образец второй ячейки не требуется). Необходимо выделить начальную ячейку и следующие пустые ячейки диапазона, в которых должен образоваться будущий ряд. Выделить, но не зацеплять, не протягивать, не автозаполнять – просто провести указателем мыши по ячейкам.
Примеры последовательностей заполнения ячеек
Источник: studme.org
Выделение диапазона ячеек
Для выполнения таких действий, как копирование, вырезание или печать данных из определенных ячеек, вы будете часто выделять на листе диапазон ячеек. В Excel в Интернете можно выбрать диапазон несколькими способами. При выделении небольшого диапазона, который состоит из нескольких ячеек, щелкните первую ячейку и перетащите ее на последнюю ячейку, которую необходимо включить в диапазон.
Чтобы выбрать диапазон большего размера, просто щелкните первую ячейку и, удерживая нажатой клавишу SHIFT, щелкните последнюю ячейку диапазона. Чтобы перейти к последней ячейке, можно использовать полосу прокрутки.
Чтобы выделить весь столбец или строку, щелкните заголовок столбца или строки.
При работе с большим листом вы можете использовать сочетания клавиш для выбора ячеек.
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Источник: support.microsoft.com