Обычно диапазоном ячеек называется прямоугольный блок соседних ячеек. Это, так называемый, смежный диапазон. Есть еще несмежные диапазоны, состоящие из нескольких смежных. На мой взгляд, термины не совсем удачные. Лучше подошли бы названия «простой» и «составной».
Смежные (простые) диапазоны Excel
Для простоты будем называть их просто диапазонами. Они могут состоять из
- Одной или нескольких ячеек Excel
- Одной или нескольких строк
- Одного или нескольких столбцов
- Всех ячеек листа Excel
Как задаются диапазоны
Диапазоны из одной или нескольких ячеек задаются адресами своих угловых ячеек — левой верхней и правой нижней ячеек, разделенных двоеточием.
Адрес диапазона, показанного на рисунке
B3:F8
На листе Excel диапазон можно выделить с помощью мыши. Надо щелкнуть левую верхнюю ячейку и, удерживая клавишу Shift, щелкнуть правую нижнюю ячейку диапазона.
Можно ввести адрес в Поле адреса и нажать Enter, будет выделен указанный диапазон.
После нажатия клавиши Enter
Задание в формуле:
Диапазоны, состоящие из одной или нескольких строк задаются номерами первой и последней строк, разделенных двоеточием. Например:
2:3 — Строки 2 и 3
На листе Excel их можно выделить с помощью мыши. Щелкнуть в левой части окна Excel номер первой строки диапазона и, удерживая клавишу Shift, щелкнуть номер последней строки.
Можно ввести адрес (номера первой и последней строк, разделенные двоеточием) в Поле адреса и нажать Enter.
Диапазоны, состоящие из одного или нескольких столбцов задаются буквами первого и последнего столбцов, разделенных двоеточием. Например:
C:D — Столбцы C и D
На листе Excel их можно выделить с помощью мыши. Щелкнуть в верхней части окна Excel букву первого столбца диапазона и, удерживая клавишу Shift, щелкнуть букву последнего столбца.
Можно ввести адрес в Поле адреса и нажать Enter, будет выделен указанный диапазон.
Диапазон, состоящий из всех ячеек листа Excel можно выделить, нажав Ctrl+A. Иногда выделяются не все ячейки, а некоторый блок, в этом случае надо еще раз нажать Ctrl+A. Другой способ — кликнуть мышью левый верхний угол таблицы выше первой строки и левее столбца A.
Несмежные (составные) диапазоны Excel
Состоят из нескольких смежных, они задаются перечислением составляющих их смежных диапазонов, разделенных точкой с запятой. Например:
A1:D3; C5:F8 — Два прямоугольных блока ячеек
2:3; C5:F8 — Строки 2,3 и прямоугольный блок ячеек
Допускается наложение составных частей
2:6; C5:F8 — Строки 2-6 и прямоугольный блок ячеек
На приведенном рисунке видно, что прямоугольный блок накладывается на строки.
С помощью мыши несмежный диапазон можно задать последовательно выделяя смежные диапазоны при нажатой клавише Ctrl.
Второй способ. Нажать Shift+F8. После этого можно последовательно выделять смежные диапазоны. Чтобы выйти из этого режима надо нажать Esc.
Сходство и различие диапазонов
Использование в формулах Excel
Смежные и несмежные диапазоны можно использовать в формулах.
Объединение ячеек
К смежному диапазону всегда можно применить операцию объединить ячейки. Даже в том случае, когда он состоит из нескольких строк или столбцов. Объединенная ячейка получает адрес и содержимое первой ячейки исходного диапазона.
К несмежному диапазону можно применить операцию объединить ячейки, только в том случае если он не содержит накладывающихся диапазонов. При этом на месте каждого смежного диапазона создается отдельная объединенная ячейка.
Копирование и перетаскивание
Смежный диапазон можно копировать и перетаскивать мышью.
Несмежный диапазон нельзя копировать, если он состоит из нескольких фрагментов. То есть, если он не представляет из себя прямоугольный блок соседних ячеек.
Несмежный диапазон нельзя перетаскивать мышью.
Операция пересечения в Excel
В Excel есть интересная операция, которая обозначается символом пробела. Это операция пересечения диапазонов. Можно делать пересечение двух и более диапазонов, но все они должны иметь общую область — прямоугольный блок ячеек, который и будет результатом пересечения.
При этом получается смежный (простой) диапазон, его можно копировать и перетаскивать мышью.
Пример 1: Пересечение строк 3 и 4 со столбцами C и D
3:4 C:D
=СУММ(3:4 C:D)
Видим, что результат суммирования равен 4. Просуммировались только ячейки стоящие на пересечении строк 3, 4 и столбцов C, D.
Пример 2: Пересечение диапазона B3:D8 с диапазоном C6:F10.
B3:D8 C6:F10
=СУММ(B3:D8 C6:F10)
Видим, что результат суммирования равен 6. Просуммировались только ячейки стоящие на пересечении блоков.
По теме страницы
Карта сайта — Подробное оглавление сайта.
Содержание:
- Что такое диапазон?
- Как рассчитать диапазон в Excel?
- Вычислить условный диапазон в Excel
Обычно, когда я использую диапазон слов в своих руководствах по Excel, это ссылка на ячейку или набор ячеек на листе.
Но этот урок не об этом диапазоне.
«Диапазон» также является математическим термином, который относится к диапазону в наборе данных (т. Е. Диапазон между минимальным и максимальным значением в данном наборе данных).
В этом уроке я покажу вам действительно простые способы рассчитать диапазон в Exceл.
Что такое диапазон?
В данном наборе данных диапазон этого набора данных будет разбросом значений в этом наборе данных.
Чтобы дать вам простой пример, если у вас есть набор данных об успеваемости учащихся, где минимальный балл составляет 15, а максимальный балл — 98, то разброс этого набора данных (также называемый диапазоном этого набора данных) будет 73.
Диапазон = 98-15
«Диапазон» — это не что иное, как разница между максимальным и минимальным значением этого набора данных.
Как рассчитать диапазон в Excel?
Если у вас есть список отсортированных значений, вам просто нужно вычесть первое значение из последнего значения (при условии, что сортировка выполняется в порядке возрастания).
Но в большинстве случаев у вас будет случайный набор данных, который еще не отсортирован.
Найти диапазон в таком наборе данных также довольно просто.
В Excel есть функции для определения максимального и минимального значения из диапазона (функции MAX и MIN).
Предположим, у вас есть набор данных, показанный ниже, и вы хотите вычислить диапазон для данных в столбце B.
Ниже приведена формула для расчета диапазона для этого набора данных:
= МАКС (B2: B11) -МИН (B2: B11)
Приведенная выше формула находит максимальное и минимальное значение и дает нам разницу.
Довольно просто … не правда ли?
Вычислить условный диапазон в 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.
Таким образом, функция MINIFS просматривает весь набор данных, но при вычислении минимального значения учитывает только те значения, которые больше 20 000.
Это гарантирует, что значения ниже 20 000 игнорируются, а минимальное значение всегда больше 20 000 (следовательно, игнорируются выбросы).
Обратите внимание, что MINIFS — это новая функция в Excel. доступно только в Excel2021-2022 и подписке Microsoft 365. Если вы используете предыдущие версии, у вас не будет этой функции (и вы можете использовать формулу, описанную далее в этом руководстве)
Если в вашем Excel нет функции МИНИМУМ, воспользуйтесь приведенной ниже формулой, в которой для того же результата используется комбинация функций ЕСЛИ и МИНИМУМ:
= МАКС (B2: B11) -МИН (ЕСЛИ (B2: B11> 20000; B2: B11))
Так же, как я использовал условную функцию MINIFS, вы также можете использовать функцию MAXIFS, если вы хотите избежать точек данных, которые являются выбросами в другом направлении (т. Е. Пара больших точек данных, которые могут исказить данные)
Итак, вот как вы можете быстро найти диапазон в Excel используя пару простых формул.
Надеюсь, вы нашли этот урок полезным.
На уроке рассмотрен материал для подготовки к ОГЭ по информатике, разбор 14 задания. Объясняется тема решения заданий в электронный таблицах Excel.
Содержание:
- Объяснение заданий 14 ОГЭ по информатике
- Типы ссылок в ячейках
- Стандартные функции Excel
- Построение диаграмм
- Решение 14 задания ОГЭ
- Решение заданий ОГЭ прошлых лет для тренировки
- Формулы в электронных таблицах
- Анализ диаграмм
14-е задание: «Электронные таблицы Excel».
Уровень сложности
— высокий,
Максимальный балл
— 3,
Примерное время выполнения
— 30 минут,
Предметный результат обучения:
Умение проводить обработку большого массива данных с использованием средств электронной таблицы.
* задания темы выполняются на компьютере
* Некоторые изображения страницы взяты из материалов презентации К. Полякова
Типы ссылок в ячейках
Формулы, записанные в ячейках таблицы, бывают относительными, абсолютными и смешанными.
- Имена ячеек в относительной формуле автоматически меняются при переносе или копировании ячейки с формулой в другое место таблицы:
- Имена ячеек в абсолютной формуле не меняются при переносе или копировании ячейки с формулой в другое место таблицы.
- Для указания того, что не меняется столбец, ставится знак
$
перед буквой столбца. Для указания того, что не меняется строка, ставится знак$
перед номером строки: - В смешанных формулах меняется только относительная часть:
Относительная адресация:
имя столбца вправо на 1
номер строки вниз на 1
Абсолютная адресация:
имена столбцов и строк при копировании формулы остаются неизменными
Смешанные формулы
Стандартные функции Excel
В ОГЭ встречаются в формулах следующие стандартные функции. Ниже рассмотрен их смысл. Наводите курсор на пример для просмотра ответа.
Таблица: Наиболее часто используемые функции
русский | англ. | действие | синтаксис |
---|---|---|---|
СУММ | SUM | Суммирует все числа в интервале ячеек | СУММ(число1;число2) |
Пример: | |||
=СУММ(3; 2) =СУММ(A2:A4) |
|||
СЧЁТ | COUNT | Подсчитывает количество всех непустых значений указанных ячеек | СЧЁТ(значение1, [значение2],…) |
Пример: | |||
=СЧЁТ(A5:A8) | |||
СРЗНАЧ | AVERAGE | Возвращает среднее значение всех непустых значений указанных ячеек | СРЕДНЕЕ(число1, [число2],…) |
Пример: | |||
=СРЗНАЧ(A2:A6) | |||
МАКС | MAX | Возвращает наибольшее значение из набора значений | МАКС(число1;число2; …) |
Пример: | |||
=МАКС(A2:A6) | |||
МИН | MIN | Возвращает наименьшее значение из набора значений | МИН(число1;число2; …) |
Пример: | |||
=МИН(A2:A6) | |||
ЕСЛИ | IF | Проверка условия. Функция с тремя аргументами: первый аргумент — логическое выражение; если значение первого аргумента — истина, то результатом выполнения функции является второй аргумент. Если ложно — третий аргумент. | ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) |
Пример: | |||
=ЕСЛИ(A2>B2;»Превышение»;»ОК») | |||
СЧЁТЕСЛИ | COUNTIF | Количество непустых ячеек в указанном диапазоне, удовлетворяющих заданному условию. | СЧЁТЕСЛИ(диапазон, критерий) |
Пример: | |||
=СЧЁТЕСЛИ(A2:A5;»яблоки») | |||
СУММЕСЛИ | SUMIF | Сумма непустых ячеек в указанном диапазоне, удовлетворяющих заданному условию. | СУММЕСЛИ (диапазон, критерий, [диапазон_суммирования]) |
Пример: | |||
=СУММЕСЛИ(B2:B25;»>5″) |
В качестве параметра функции везде указывается диапазон ячеек: МИН(А2:А240)
СРЗНАЧ
не учитываются пустые ячейки и текстовые ячейки; например, после ввода формулы в C2 появится значение 2 (не учитывается пустая А2):Построение диаграмм
- Диаграммы используются для наглядного представления табличных данных.
- Разные типы диаграмм используются в зависимости от необходимого эффекта визуализации.
- Так, круговая и кольцевая диаграммы отображают соотношение находящихся в выбранном диапазоне ячеек данных к их общей сумме. Иными словами, эти типы служат для представления доли отдельных составляющих в общей сумме.
- Соответствие секторов круговой диаграммы (если она намеренно НЕ перевернута) начинается с «севера»: верхний сектор соответствует первой ячейке диапазона.
- Типы диаграмм Линейчатая и Гистограмма (на левом рис.), а также График и Точечная (на рис. справа) отображают абсолютные значения в выбранном диапазоне ячеек.
Егифка ©:
Решение 14 задания ОГЭ
Задание 14_0. Демонстрационный вариант 2022 г.:
В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.
A | B | C | D | |
---|---|---|---|---|
1 | Округ | Фамилия | Предмет | Баллы |
2 | С | Ученик 1 | Физика | 240 |
3 | В | Ученик 2 | Физкультура | 782 |
4 | Ю | Ученик 3 | Биология | 361 |
5 | СВ | Ученик 4 | Обществознание | 377 |
В столбце A
записан код округа, в котором учится ученик;
в столбце B
– код фамилии ученика;
в столбце C
– выбранный учеником предмет;
в столбце D
– тестовый балл.
Всего в электронную таблицу были занесены данные по 1000 учеников.
Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На основании данных, содержащихся в этой таблице, выполните задания.
- Сколько учеников, которые проходили тестирование по информатике, набрали более 600 баллов? Ответ запишите в ячейку
H2
таблицы. - Каков средний тестовый балл учеников, которые проходили тестирование по информатике? Ответ запишите в ячейку
H3
таблицы с точностью не менее двух знаков после запятой. - Постройте круговую диаграмму, отображающую соотношение числа участников тестирования из округов с кодами «В», «Зел» и «З». Левый верхний угол диаграммы разместите вблизи ячейки
G6
. В поле диаграммы должны присутствовать легенда (обозначение соответствия данных определённому сектору диаграммы) и числовые значения данных, по которым построена диаграмма.
Полученную таблицу необходимо сохранить под именем, указанным организаторами экзамена.
✍ Решение:
Приведем один из вариантов решения.
- Поскольку спрашивается об учениках, которые проходили тестирование по информатике и набрали более 600 баллов, то здесь необходимо учесть одновременно два условия. Поэтому будем использовать функцию
ЕСЛИ
с логическим операторомИ
(одновременное выполнение нескольких условий). В ячейкуE2
запишем формулу:
=ЕСЛИ(И(C2="информатика"; D2>600); 1;0))
или для англоязычного интерфейса:
=IF(AND(C2="информатика"; D2>600); 1;0)
Т.е. если в ячейке C2
находится слово «информатика» и при этом значение ячейки D2
больше 600, то в ячейку E2
запишем 1 (единицу), иначе в ячейку E2
запишем 0 (ноль).
E
. Для этого поместите курсор в правый нижний угол ячейки Е2
, и, когда курсор мыши приобретет вид крестика дважды щелкните левой кнопкой мыши. Формула должна при этом скопироваться во все нижние ячейки столбца. H2
. Установите курсор в ячейку. Для выполнения задания нам достаточно посчитать, сколько единиц в ячейках столбца E
. Для этого мы можем суммировать их. Введите формулу:=СУММ(Е:Е)
Е:Е
обозначает диапазон ячеек по всему столбцу. Так как мы уверены, что ячейки после таблицы нет никаких лишних данных, то можно указывать такие диапазоны. Но на всякий случай следует проверять, нет ли после таблицы каких-то ненужных значений.
Ответ: 32
Решение задания 2:
- В ячейку
F2
внесем формулу:
=ЕСЛИ(C2="информатика";D2;0)
или для англоязычной раскладки:
=IF(C2="информатика"; D2; "")
Если в ячейке C2
находится слово «информатика», то в ячейку F2
установим значение из ячейки D2
, т.е. балл, иначе, поставим туда «» (пустое значение).
F
. Для этого поместите курсор в правый нижний угол ячейки F2
, и, когда курсор мыши приобретет вид крестика дважды щелкните левой кнопкой мыши. Формула должна при этом скопироваться во все нижние ячейки столбца. H3
. Установите курсор в ячейку. Для выполнения задания нам достаточно посчитать среднее арифметическое непустых значений ячеек столбца F
. Введите формулу:=СРЗНАЧ(F:F)
Ответ: 546,82
Решение задания 3:
Ответ: Секторы диаграммы должны визуально соответствовать соотношению 32:29:108.
Разбор задания 14.1:
В электронную таблицу занесли данные о тестировании учеников. Ниже приведены первые пять строк таблицы:
В столбце А записан округ, в котором учится ученик;
в столбце В — фамилия;
в столбце С — любимый предмет;
в столбце D — тестовый балл.
Всего в электронную таблицу были занесены данные по 1000 ученикам.
Выполните задание:
Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Сколько учеников в Северо-Восточном округе (СВ) выбрали в качестве любимого предмета математику? Ответ на этот вопрос запишите в ячейку Н2 таблицы.
- Каков средний тестовый балл у учеников Южного округа (Ю)? Ответ на этот вопрос запишите в ячейку Н3 таблицы с точностью не менее двух знаков после запятой.
Подобные задания для тренировки
✍ Решение:
-
Задание 1:
- В задании необходимо посчитать количество определенных данных, в зависимости от условий. Можно было бы использовать функцию СЧЁТЕСЛИ(), но ситуация осложняется тем, что в задании два условия: Северо-Восточный округ и любимый предмет — математика.
- Если заданы два условия будем использовать сначала логическую функцию ЕСЛИ с логической операцией И для выполнения двух условий одновременно. Применим данную функцию сначала для первой строки с данными:
для русскоязычной записи функций =ЕСЛИ(И(A2="св";C2="математика");1;0)
Если значение в ячейке A2 равно св и одновременно значение в ячейке C2 равно математика, то в ячейку F2 запишем значение 1, иначе — в ячейку F2 запишем значение 0.
для англоязычной записи функций =IF(AND(A2="св";C2="математика");1;0)
для англоязычной записи функций
=СУММ(F2:F1001)
Суммируем значения ячеек в диапазоне от F2 до F1001.
для англоязычной записи функций =SUM(F2:F1001)
Ответ: 17
Задание 2:
для русскоязычной записи функций
=СУММЕСЛИ(A2:A1001; "Ю";D2:D1001)
Если значения ячеек диапазона A2:A1001 равно значению «Ю», то суммируем соответствующие этим строкам значения ячеек D2:D1001.
для русскоязычной записи функций
=СЧЁТЕСЛИ(A2:A1001; "Ю")
Подсчитывается количество ячеек диапазона A2:A1001, значения которых равно «Ю».
для русскоязычной записи функций =СУММЕСЛИ(A2:A1001; "Ю";D2:D1001)/СЧЁТЕСЛИ(A2:A1001; "Ю")
для англоязычной записи функций =SUMIF(A2:A1001; "Ю";D2:D1001)/COUNTIF(A2:A1001; "Ю")
Возможны и другие варианты решения.
Ответ: 525,70
Разбор задания 14.2 :
В электронную таблицу занесли данные о калорийности продуктов. Ниже приведены первые пять строк таблицы.
В столбце A записан продукт;
в столбце B – содержание в нём жиров;
в столбце C – содержание белков;
в столбце D – содержание углеводов и
в столбце Е – калорийность этого продукта.
Всего в электронную таблицу были занесены данные по 1000 продуктам.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Сколько продуктов в таблице содержат меньше 50 г углеводов и меньше 50 г белков? Запишите число, обозначающее количество этих продуктов, в ячейку H2 таблицы.
- Какова средняя калорийность продуктов с содержанием жиров менее 1 г? Запишите значение в ячейку H3 таблицы с точностью не менее двух знаков после запятой.
Подобные задания для тренировки
✍ Решение:
-
Задание 1:
- Поскольку в задании используется условие, то начнем с него: два условия, значит будем использовать функцию ЕСЛИ с логической операцией И. Формула будет выполняться по значениям каждой строки, поэтому запишем ее сначала для второй строки таблицы, там, где начинаются основные данные. В ячейке F2:
для русскоязычной записи функций =ЕСЛИ(И(D2<50;C2<50);1;0)
Если значение в ячейке D2 меньше 50 (углеводы) и одновременно значение в ячейке C2 меньше 50 (белки), то в ячейку F2 запишем значение 1, иначе — в ячейку F2 запишем значение 0.
для англоязычной записи функций =IF(AND(D2<50;C2<50);1;0)
для англоязычной записи функций
=СУММ(F2:F1001)
Суммируем значения ячеек в диапазоне от F2 до F1001.
для англоязычной записи функций =SUM(F2:F1001)
Ответ: 864
Задание 2:
для русскоязычной записи функций
=СУММЕСЛИ(B2:B1001; "<1";E2:E1001)
Если значения ячеек диапазона B2:B1001 меньше единицы, то суммируем соответствующие этим строкам значения ячеек E2:E1001.
для русскоязычной записи функций
=СЧЁТЕСЛИ(B2:B1001;"<1")
Подсчитывается количество ячеек диапазона B2:B1001, значения которых < 1.
для русскоязычной записи функций =СУММЕСЛИ(B2:B1001; "<1";E2:E1001)/СЧЁТЕСЛИ(B2:B1001;"<1")
для англоязычной записи функций =SUMIF(B2:B1001; "<1";E2:E1001)/COUNTIF(B2:B1001;"<1")
Возможны и другие варианты решения.
Ответ: 89,45
Разбор задания 14.3:
В электронную таблицу занесли численность населения городов разных стран. Ниже приведены первые пять строк таблицы.
В столбце А указано название города; в столбце В — численность населения (тыс. чел.); в столбце С — название страны.
Всего в электронную таблицу были занесены данные по 1000 городам. Порядок записей в таблице произвольный.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Сколько городов, представленных в таблице, имеют численность населения менее 100 тыс. человек? Ответ запишите в ячейку F2.
- Чему равна средняя численность населения австрийских городов, представленных в таблице? Ответ на этот вопрос с точностью не менее двух знаков после запятой (в тыс. чел.) запишите в ячейку F3 таблицы.
Подобные задания для тренировки
✍ Решение:
-
Задание 1:
- Поскольку в задании используется условие, то начнем с него: так как задано только одно условие (численность населения менее 100 тыс), то можно использовать функцию СЧЁТЕСЛИ:
для русскоязычной записи функций
=СЧЁТЕСЛИ(диапазон;критерий)
для русскоязычной записи функций
=СЧЁТЕСЛИ(B2:B1001;"<100")
для англоязычной записи функций =COUNTIF(B2:B1001;"<100")
Дословно переведем действие формулы. Считать количество значений меньших 100 в диапазоне ячеек от B2 до B1001.
Ответ: 448
Задание 2:
для русскоязычной записи функций
=СУММЕСЛИ(C2:C1001; "Австрия";B2:B1001)
Если значения ячеек диапазона C2:C1001 равно значению «Австрия», то суммируем соответствующие этим строкам значения ячеек B2:B1001.
для русскоязычной записи функций
=СЧЁТЕСЛИ(C2:C1001; "Австрия")
Подсчитывается количество ячеек диапазона C2:C1001, значения которых равно «Австрия».
=E2/E3
Возможны и другие варианты решения.
Ответ: 51,10.
Разбор задания 14.4:
В электронную таблицу занесли информацию о грузоперевозках, совершённых некоторым автопредприятием с 1 по 9 октября.
Каждая строка таблицы содержит запись об одной перевозке. В столбце A записана дата перевозки (от «1 октября» до «9 октября»); в столбце B — название населённого пункта отправления перевозки; в столбце C — название населённого пункта назначения перевозки; в столбце D — расстояние, на которое была осуществлена перевозка (в километрах); в столбце E — расход бензина на всю перевозку (в литрах); в столбце F — масса перевезённого груза (в килограммах).
Всего в электронную таблицу были занесены данные по 370 перевозкам в хронологическом порядке.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- На какое суммарное расстояние были произведены перевозки с 1 по 3 октября? Ответ на этот вопрос запишите в ячейку H2 таблицы.
- Какова средняя масса груза при автоперевозках, осуществлённых из города Липки? Ответ на этот вопрос запишите в ячейку H3 таблицы с точностью не менее одного знака после запятой.
✍ Решение:
-
Задание 1 первый способ:
- Поскольку в задании указано, что данные приведены в хронологическом порядке, то можно утверждать, что все строки с самой первой до той, в которой последняя запись за «3 октября» будут подходить под условие «перевозки с 1 по 3 октября».
Таким образом, смотрим, что последняя запись за «3 октября» соответствует ячейке A118. Значит, для получения суммарного расстояния будем вычислять сумму по диапазону ячеек D2:D118 (столбец Расстояние). Используем функцию СУММ. - В итоге мы получим сразу искомый результат. Поэтому запишем формулу в ячейке H2:
для русскоязычной записи функций
=СУММ(D2:D118)
для англоязычной записи функций =SUM(D2:D118)
Дословно переведем действие формулы. Суммируем значения ячеек в диапазоне от D2 до D118.
Ответ: 28468
Задание 1 второй способ:
для русскоязычной записи функций =ЕСЛИ(ИЛИ(A2="1 октября";A2="2 октября";A2="3 октября");D2;0)
Если значение в ячейке A2 равно «1 октября» или значение в ячейке A2 равно «2 октября» или значение в ячейке A2 равно «3 октября», то в ячейку I2 запишем значение, которое находится в ячейке D2 (Расстояние), иначе — в ячейку I2 запишем значение 0.
для англоязычной записи функций =IF(OR(A2="1 октября";A2="2 октября";A2="3 октября");D2;0)
=СУММ(I2:I371)
Суммируем значения ячеек в диапазоне от I2 до I371.
для англоязычной записи функций =SUM(I2:I371)
Ответ: 28468
-
Задание 2:
- Для начала подумаем, как вычислить среднюю массу груза автоперевозок из Липки: для этого необходимо сумму всех значений массы перевозок из Липки (Пункт отправления — Липки) разделить на количество таких перевозок.
- Поскольку необходимо найти сумму только при определенном условии, то будем использовать функцию СУММЕСЛИ. Заметим, что проверка должна осуществляться по диапазону ячеек B2:B371 (Пункт отправления), в то время как суммироваться должны значения ячеек диапазона F2:F371 (Масса груза).
- Данная формула выглядит так:
для русскоязычной записи функций
=СУММЕСЛИ(B2:B371; "Липки";F2:F371)
Если значения ячеек диапазона B2:B371 равно значению «Липки», то суммируем соответствующие этим строкам значения ячеек F2:F371.
для русскоязычной записи функций
=СЧЁТЕСЛИ(B2:B371; "Липки")
Подсчитывается количество ячеек диапазона B2:B371, значения которых равно «Липки».
=I2/I3
Возможны и другие варианты решения, например, сортировка строк по значению столбца B с дальнейшим выбором необходимых диапазонов для функций.
Ответ: 760,9
Разбор задания 14.5:
В электронную таблицу занесли результаты тестирования учащихся по географии и информатике. Вот первые строки получившейся таблицы:
В столбце А указаны фамилия и имя учащегося; в столбце В — номер школы учащегося; в столбцах С, D — баллы, полученные, соответственно, по географии и информатике. По каждому предмету можно было набрать от 0 до 100 баллов.
Всего в электронную таблицу были занесены данные по 272 учащимся. Порядок записей в таблице произвольный.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Сколько учащихся школы № 2 набрали по информатике больше баллов, чем по географии? Ответ на этот вопрос запишите в ячейку F2 таблицы.
- Сколько процентов от общего числа участников составили ученики, получившие по географии больше 50 баллов? Ответ с точностью до одного знака после запятой запишите в ячейку F3 таблицы.
✍ Решение:
-
Задание 1:
- В задании необходимо посчитать количество определенных данных, в зависимости от условий. Можно было бы использовать функцию СЧЁТЕСЛИ(), но ситуация осложняется тем, что в задании два условия: школа №2 и баллов по информатике больше чем баллов по географии.
- Если заданы два условия, то следует использовать сначала логическую функцию ЕСЛИ с логической операцией И для выполнения двух условий одновременно. Применим данную функцию сначала для первой строки с данными. Для этого в ячейке G2 (свободный столбец) запишем формулу:
для русскоязычной записи функций =ЕСЛИ(И(B2=2;D2>C2);1;0)
Если значение в ячейке B2 равно 2 и одновременно значение в ячейке D2 больше значения в C2, то в ячейку G2 запишем значение 1, иначе — в ячейку G2 запишем значение 0.
для англоязычной записи функций =IF(AND(B2=2;D2>C2);1;0)
для англоязычной записи функций
=СУММ(G2:G273)
Суммируем значения ячеек в диапазоне от G2 до G273.
для англоязычной записи функций =SUM(G2:G273)
Ответ: 37
-
Задание 2:
- Найдём количество участников, набравших по географии более 50 баллов. Воспользуемся одной из возможных в таких случаях функций — функцией СЧЁТЕСЛИ(диапазон;критерий). В качестве критерия укажем условие «>50», обязательно указанное в кавычках. Запишем формулу в свободной ячейке H2:
для русскоязычной записи функций
=СЧЁТЕСЛИ(C2:C273; ">50")
Если значения ячеек диапазона C2:C273 больше 50, то считаем количество соответствующих этим строкам значений ячеек диапазона C2:C273.
=H2/272*100
Возможны и другие варианты решения.
Ответ: 74,6
Разбор задания 14.6:
В электронную таблицу занесли результаты тестирования учащихся по физике и информатике. Вот первые строки получившейся таблицы:
В столбце А указаны фамилия и имя учащегося; в столбце В — округ учащегося; в столбцах С, D — баллы, полученные, соответственно, по физике и информатике. По каждому предмету можно было набрать от 0 до 100 баллов.
Всего в электронную таблицу были занесены данные по 266 учащимся. Порядок записей в таблице произвольный.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Чему равна средняя сумма баллов по двум предметам среди учащихся школ округа «Южный»? Ответ на этот вопрос запишите в ячейку F2 таблицы.
- Сколько процентов от общего числа участников составили ученики школ округа «Западный»? Ответ с точностью до одного знака после запятой запишите в ячейку F3 таблицы.
Подобные задания для тренировки
✍ Решение:
-
Задание 1:
- Необходимо найти среднюю сумму баллов по двум предметам. Значит, для учащихся южного округа посчитаем сумму баллов по двум ячейкам со значениями баллов по предметам. Так как предусмотрено условие, то будем использовать функцию ЕСЛИ, а в случае истинности значения — выводить сумму двух ячеек. Для этого в ячейке G2 (свободный столбец) запишем формулу:
для русскоязычной записи функций
=ЕСЛИ(B2="Южный";C2+D2;"-")
Если в ячейке B2 стоит значение «Южный», то выводим сумму значений ячеек C2 и D2, иначе выводим «-«
для англоязычной записи функций
=If(B2="Южный";C2+D2;"-")
для русскоязычной записи функций
=СРЗНАЧ(G2:G267)
Функция СРЗНАЧ самостоятельно суммирует значения в ячейках диапазона от G2 до G267 и делит получившуюся сумму на количество ячеек, которые имеют числовые значения.
для англоязычной записи функций
=AVERAGE(G2:G267)
Ответ: 117,15;
Ответ: 15,4.
Разбор задания 14.7:
В московской Библиотеке имени Некрасова в электронной таблице хранится список поэтов Серебряного века. Ниже приведены первые пять строк таблицы:
Каждая строка таблицы содержит запись об одном поэте. В столбце А записана фамилия, в столбце В — имя, в столбце С — отчество, в столбце D — год рождения, в столбце Е — год смерти.
Всего в электронную таблицу были занесены данные по 150 поэтам Серебряного века в алфавитном порядке.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Определите количество поэтов, родившихся в 1889 году. Ответ на этот вопрос запишите в ячейку H2 таблицы.
- Определите в процентах, сколько поэтов, умерших позже 1940 года, носили имя Сергей. Ответ с точностью не менее 2 знаков после запятой запишите в ячейку НЗ таблицы.
✍ Решение:
-
Задание 1:
Ответ: 8.
-
Задание 2:
- Для того, чтобы вычислить процент, необходимо сначала определить общее количество поэтов, умерших позже 1940 года, а затем определить сколько среди этого числа поэтов с именем Сергей.
- Таким образом, можно использовать условие (функция ЕСЛИ) для определения года смерти позже 1940, в случае истинности условия выводить имя поэта. Запишем в ячейку F2 (свободного столбца) формулу:
для русскоязычной записи функций
=ЕСЛИ(E2>1940;B2;"-")
Если год смерти (ячейка E2) позже 1940, то в текущую ячейку выводим значение ячейки B2 (имя), иначе выводим дефис («-«).
для англоязычной записи функций
=IF(E2>1940;B2;"-")
для русскоязычной записи функций =СЧЁТЕСЛИ(F2:F151;"=Сергей")/СЧЁТЕСЛИ(E2:E151;">1940")*100
Считаем количество ячеек в диапазоне F2:F151, в которых находится имя Сергей. Полученное значение сначала делим на количество ячеек диапазона E2:E151, соответствующих критерию >1940, а затем умножаем на 100.
для англоязычной записи функций =COUNTIF(F2:F151;"=Сергей")/COUNTIF(E2:E151;">1940")*100
Ответ: 6,02.
Разбор задания 14.8:
В медицинском кабинете измеряли рост и вес учеников с 5 по 11 классы. Результаты занесли в электронную таблицу. Ниже приведены первые пять строк таблицы:
Каждая строка таблицы содержит запись об одном ученике. В столбце А записана фамилия, в столбце В — имя; в столбце С — класс; в столбце D — рост, в столбце Е — вес учеников.
Всего в электронную таблицу были занесены данные по 211 ученикам в алфавитном порядке.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Каков рост самого высокого ученика 10 класса? Ответ на этот вопрос запишите в ячейку H2 таблицы.
- Какой процент учеников 8 класса имеет вес больше 65? Ответ с точностью не менее 2 знаков после запятой запишите в ячейку НЗ таблицы.
✍ Решение:
Разбор задания 14.9:
В электронную таблицу занесли результаты сдачи нормативов по лёгкой атлетике среди учащихся 7−11 классов. Результаты занесли в электронную таблицу. Ниже приведены первые строки таблицы:
В столбце А указана фамилия; в столбце В — имя; в столбце С — пол; в столбце D — год рождения; в столбце Е — результаты в беге на 1000 метров; в столбце F — результаты в беге на 30 метров; в столбце G — результаты по прыжкам в длину с места.
Всего в электронную таблицу были занесены данные по 1000 учащимся.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Сколько процентов участников пробежало дистанцию в 1000 м меньше, чем за 5 минут? Ответ запишите в ячейку L1 таблицы.
- Найдите разницу в см с точностью до десятых между средним результатом у мальчиков и средним результатом у девочек в прыжках в длину. Ответ на этот вопрос запишите в ячейку L2 таблицы.
✍ Решение:
Разбор задания 14.10:
Результаты сдачи выпускных экзаменов по алгебре, русскому языку, физике и информатике учащимися 9 класса некоторого города были занесены в электронную таблицу. На рисунке приведены первые строки получившейся таблицы:
В столбце A электронной таблицы записана фамилия учащегося, в столбце B — имя учащегося, в столбцах C, D, E и F — оценки учащегося по алгебре, русскому языку, физике и информатике. Оценки могут принимать значения от 2 до 5.
Всего в электронную таблицу были занесены результаты 1000 учащихся.
Выполните задание:
Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
- Какое количество учащихся получило только четвёрки или пятёрки на всех экзаменах? Ответ на этот вопрос запишите в ячейку I2 таблицы.
- Для группы учащихся, которые получили только четвёрки или пятёрки на всех экзаменах, посчитайте средний балл, полученный ими на экзамене по алгебре. Ответ на этот вопрос запишите в ячейку I3 таблицы с точностью не менее двух знаков после запятой.
✍ Решение:
- В задании необходимо посчитать количество определенных данных, в зависимости от условий. Можно было бы использовать функцию СЧЁТЕСЛИ(), но ситуация осложняется тем, что в задании несколько условий: только четвёрки или пятёркина всех (четырех!) экзаменах.
- Если заданы несколько условий, то следует использовать сначала логическую функцию ЕСЛИ с логической операцией И для выполнения четырех условий для четырех экзаменов одновременно.
- При этом заметим, что оценка 4 или 5, означает условие оценка > 3. Применим данную функцию сначала для первой строки с данными. Для этого в ячейке G2 (свободный столбец) запишем формулу:
для русскоязычной записи функций =ЕСЛИ(И(C2>3;D2>3;E2>3;F2>3);1;0)
Если значение в ячейке C2 > 3 и значение в ячейке D2 > 3 и значение в ячейке E2 > 3 и значение в ячейке F2 > 3, то в ячейку G2 запишем значение 1, иначе — в ячейку G2 запишем значение 0.
для англоязычной записи функций =IF(AND(C2>3;D2>3;E2>3;F2>3);1;0)
для русскоязычной записи функций
=СУММ(G2:G1001)
для англоязычной записи функций
=SUMM(G2:G1001)
Ответ: 1)88; 2)4,32.
Решение заданий ОГЭ прошлых лет для тренировки
Рассмотрим, как решается задание 14 ОГЭ по информатике.
Формулы в электронных таблицах
Подробный видеоразбор по ОГЭ 14 задания:
📹 Видеорешение на RuTube здесь
Разбор задания 14.1:
Дан фрагмент электронной таблицы:
Какая из формул, приведённых ниже, может быть записана в ячейке A2, чтобы построенная после выполнения вычислений диаграмма по значениям диапазона ячеек A2:D2 соответствовала рисунку?
1) =B1/C1 2) =D1−A1 3) =С1*D1 4) =D1−C1+1
Подобные задания для тренировки
✍ Решение:
- Вычислим значения в ячейках согласно заданным формулам:
B2 = D1 - 1 B2 = 5 - 1 = 4
C2 = B1 * 4 C2 = 4 * 4 = 16
D2 = D1 + A1 D2 = 5 + 3 = 6
1) =B1/C1 = 2 2) =D1−A1 = 2 3) =С1*D1 = 10 4) =D1−C1+1 = 4
Ответ: 4
Разбор задания 14.2:
Дан фрагмент электронной таблицы:
Какое число должно быть записано в ячейке B1, чтобы построенная после выполнения вычислений диаграмма по значениям диапазона ячеек A2:D2 соответствовала рисунку?
1) 0 2) 6 3) 3 4) 4
Подобные задания для тренировки
✍ Решение:
- Обратим внимание, что ни в одной формуле указанного диапазона ячеек не встречается ячейка A1. То есть оставим ее пустой.
- Вычислим значения в ячейках согласно заданным формулам:
A2 = D1 - 3 A2 = 4 - 3 = 1
B2 = C1 - D1 B2 = 5 - 4 = 1
C2 = (A2 + B2) / 2 C2 = (1 + 1) / 2 = 1
D2 = B1 - D1 + C2 D2 = ? - 4 + 1
D2 = B1 - D1 + C2 D2 = ? - 4 + 1 = 3 получаем: 6 - 4 + 1 = 3
Ответ: 2
Разбор задания 14.3:
Дан фрагмент электронной таблицы:
Какое число должно быть записано в ячейке B1, чтобы построенная после выполнения вычислений диаграмма по значениям диапазона ячеек A2:D2 соответствовала рисунку?
1) 1 2) 2 3) 0 4) 4
Подобные задания для тренировки
✍ Решение:
- Обратим внимание, что ни в одной формуле указанного диапазона ячеек не встречается ячейка D1. То есть оставим ее пустой.
- Вычислим значения в ячейках согласно заданным формулам:
A2 = C1 - 3 A2 = 5 - 3 = 2
B2 = (A1 + C1) / 2 B2 = (3 + 5) / 2 = 4
C2 = =A1 / 3 C2 = 3 / 3 = 1
D2 = (B1 + A2) / 2 D2 = (? + 2) / 2
D2 = (B1 + A2) / 2 D2 = (? + 2) / 2 = 3 получаем: D2 = (4 + 2) / 2 = 3
Ответ: 4
Анализ диаграмм
14_4:
На диаграмме отображено количество участников тестирования по предметам в разных регионах России.
Какая из диаграмм правильно отражает соотношение общего количества участников (из всех трех регионов) по каждому из предметов тестирования?
✍ Решение:
- столбчатая диаграмма позволяет определить числовые значения. Так, например, в Татарстане по биологии количество участников 400 и т.п. Найдем с помощью нее общее количество участников со всех регионов по каждому предмету. Для этого посчитаем значения абсолютно всех столбцов в диаграмме:
400 + 100 + 200 + 400 + 200 + 200 + 400 + 300 + 200 = 2400
Биология: 1200/2400 = 0,5 = 50% История: 600/2400 = 0,25 = 25% Химия: 600/2400 = 0,25 = 25%
Результат: 1
Предлагаем посмотреть подробный разбор данного 7 задания на видео:
📹 Видеорешение на RuTube здесь
14_5:
На диаграмме отображено количество участников тестирования по предметам в разных регионах России.
Какая из диаграмм правильно отражает соотношение количества участников тестирования по истории в регионах?
✍ Решение:
Результат: 2
Подробный разбор задания смотрите на видео:
📹 Видеорешение на RuTube здесь
Математически вы вычисляете диапазон, вычитая минимальное значение из максимального значения определенного набора данных. Он представляет собой разброс значений в наборе данных и полезен для измерения изменчивости — чем больше диапазон, тем более разбросаны и изменчивы ваши данные.
К счастью, найти диапазон набора данных легко при использовании функций Excel. Вот как это сделать.
Как найти и рассчитать диапазон за 3 шага
Самый простой способ найти диапазон в Microsoft Excel — использовать функции MIN и MAX. Функция MIN вычисляет наибольшее значение в наборе данных, а функция MIN вычисляет наименьшее значение.
Во-первых, убедитесь, что ваши данные аккуратно организованы на листе для простоты анализа. Затем вы можете использовать функции MIN и MAX следующим образом:
- Выберите ячейку за пределами вашего набора данных (в нашем примере D1). В этой ячейке введите = MAX и выберите = MAX в раскрывающемся меню.
- Выберите диапазон ячеек, щелкнув и перетащив. Кроме того, вы можете ввести диапазон ячеек в ячейку вручную (например, =MAX(B2:B15). Нажмите Enter для подтверждения.
- Выберите другую ячейку (в нашем примере D2) и введите =MIN. Повторите шаг 2 для этой ячейки.
- Теперь нужно найти разницу между этими значениями. Для этого выберите ячейку (в нашем примере D3) и используйте функцию вычитания, набрав =(ячейка, содержащая максимальное значение)-(ячейка, содержащая минимальное значение). Например, =D1-D2.
Можно вычислить диапазон значений за один быстрый шаг, объединив эти функции в одной ячейке. Для этого мы будем использовать наш пример, в котором набор данных содержится в ячейках с B2 по B15.
Используя эти ячейки, функция диапазона будет выглядеть следующим образом:
=МАКС(B2:B15)-МИН(B2-B15)
Все, что вам нужно сделать, это изменить значения ячеек, чтобы они соответствовали вашим данным.
Как найти условный диапазон в Excel
Если в вашем наборе данных есть несколько выбросов, можно рассчитать условный диапазон, игнорирующий выбросы. Например, взгляните на этот пример скриншота:
Почти все значения находятся в диапазоне от 40 до 80, но есть два значения около 1000, а два самых низких значения близки к 1. Если вы хотите рассчитать диапазон, но игнорируете эти значения, вам нужно будет игнорировать значения меньше 5 и больше 900. Здесь на помощь приходят функции MAXIFS и MINIFS.
MAXIFS добавляет условие, которое игнорирует значения выше определенного числа, а MINIFS игнорирует значения меньше определенного числа (с каждым условием, разделенным запятой).
Итак, для нашего примера функция MAX будет выглядеть так:
=МАКСЕСЛИ(B2:B15,B2:B15,”<900″)
И функция MIN станет:
=МИНИФС(B2:B15,B2:B15”>5”)
В этом случае итоговая формула Excel для расчета условного диапазона будет такой:
=МАКСИФН(B2:B15,B2:B15″,<900″)-МИНИФМН(B2:B15,B2:B15″,>5″)
Примечание. Функции МАКСИФС и МИНИМФС доступны только в Excel 2019 и Microsoft Office 365, поэтому, если вы используете более старую версию, вам нужно будет включить функцию ЕСЛИ отдельно. Например: =МАКС(В2:В15)-МИН(ЕСЛИ(В2:В15>5,В2:В15)
Анализ данных никогда не был проще
Microsoft Excel — мощный инструмент для анализа данных. С помощью этого руководства вы сможете легко рассчитать диапазон любого набора данных, даже если вам нужно удалить выбросы.
Related post
Начнем с самого простого: дайте ячейке имя. Для этого просто выделите ее (1) и в поле имени (2) вместо адреса ячейки введите любое легко запоминающееся имя.
Именная ячейка C7
Имя ограничено 255 символами, что более чем достаточно. Имя также не должно содержать пробелов, поэтому, если оно состоит из нескольких слов, можно разделить их символом подчеркивания.
Если теперь мы хотим вывести это значение на другие листы книги или использовать его в дальнейших расчетах, нет необходимости переходить на первый лист и вручную указывать ячейку. Просто введите имя ячейки, и ее значение будет заменено.
Использование имени ячейки
Ячейки, строки, столбцы
Начнем с выделения ячеек, строк и столбцов.
- Чтобы выбрать ячейку C3, нажмите на поле на пересечении столбца C и строки 3.
- Чтобы выбрать столбец C, щелкните по заголовку столбца C.
- Чтобы выбрать строку 3, щелкните по заголовку строки 3.
Примеры диапазона
Диапазон — это набор из двух или более ячеек.
- Чтобы выделить диапазон B2:C4, щелкните в правом нижнем углу ячейки B2 и перетащите указатель мыши в ячейку C4.
- Чтобы выделить диапазон, состоящий из отдельных (не смежных) ячеек, удерживая нажатой клавишу Ctrl, щелкните каждую ячейку, которую вы хотите включить в диапазон.
Заполнение диапазона
Следуйте приведенным ниже инструкциям, чтобы заполнить диапазон:
- В ячейку B2 введите значение 2.
- Выделите ячейку B2, зажмите ее правый нижний угол и перетащите вниз к ячейке B8.
Результат:
Эта техника перетаскивания очень важна, вы будете часто использовать ее в Excel. Вот еще один пример:
- Введите значение 2 в ячейку B2 и значение 4 в ячейку B3.
- Выделите ячейки B2 и B3, зажмите правый нижний угол этого диапазона и перетащите его вниз.
Excel автоматически заполнит диапазон на основе формулы из первых двух значений. Довольно круто, правда? Вот еще один пример:
- Введите дату 13/6/2013 в ячейку B2 и дату 16/6/2013 в ячейку B3 (на рисунке показан американский эквивалент этих дат).
- Выделите ячейки B2 и B3, зажмите правый нижний угол этого диапазона и перетащите его вниз.
Именованный диапазон
Диапазон ячеек можно назвать аналогичным образом, т.е. выделить диапазон (1) и ввести его имя в поле имени (2):
Создание именованного диапазона
Затем это имя можно использовать в формулах, например, для вычисления суммы:
Использование именованного диапазона в формуле
Именованный диапазон также можно создать на вкладке Формулы, выбрав инструмент Задать имя.
Создание именованного диапазона с помощью панели инструментов
Появится диалоговое окно, в котором нужно ввести имя диапазона, выбрать область, к которой будет применяться имя (т.е. ко всей книге или к отдельным листам), при необходимости заполнить примечание, а затем выбрать соответствующий диапазон на листе.
Создание имени с помощью диалогового окна
Для работы с существующими диапазонами на вкладке Формулы есть Менеджер имен.
Именной менеджер
Используйте этот инструмент для удаления, изменения или добавления новых имен к ячейкам или диапазонам.
Управление именованными диапазонами
Однако важно понимать, что если вы используете именованные диапазоны в формулах, удаление имени такого диапазона приведет к ошибкам.
Задача
У вас есть таблица продаж некоторых товаров по месяцам (см. файл-образец ):
Задача состоит в том, чтобы найти общий объем продаж продукции в данном месяце. Пользователь должен иметь возможность выбрать месяц и получить общую сумму продаж. Пользователь должен выбрать месяц, используя выпадающий список.
Чтобы решить эту проблему, нам нужно создать два динамических диапазона: один для выпадающего списка, содержащего месяцы, а другой для диапазона суммы.
Для генерации динамических диапазонов мы будем использовать функцию HUMMING(), которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Вы можете указать высоту и ширину диапазона, а также смещение по строкам и столбцам.
Создайте динамический диапазон для выпадающего списка, содержащего месяцы. С одной стороны, необходимо учесть, что пользователь может добавить продажи за месяцы после апреля (май, июнь…), с другой стороны, выпадающий список не должен содержать пустых строк. Динамический диапазон как раз и является решением этой проблемы.
Для создания динамического диапазона:
- на вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
- В поле Имя введите: Месяц ;
- В поле Область выберите Книжный лист ;
- В поле Range введите формулу =MEMBERSHIP(sheet1!$B$5;;;1;ACCOUNT(sheet1!$B$5:$I$5)).
- Нажмите OK.
Теперь подробнее. Любой диапазон в EXCEL определяется координатами верхней левой и нижней правой ячеек диапазона. Начальной ячейкой, от которой рассчитывается положение нашего динамического диапазона, является ячейка B5 . Если аргументы offset_by_rows, offset_by_columns не заданы (как в нашем случае), то эта ячейка является верхней левой ячейкой диапазона. Правая нижняя ячейка диапазона задается аргументами height и width . В нашем случае значение высоты =1, а значение ширины диапазона равно результату расчета формулы SCHOTZ(sheet1!$B$5:$I$5), который равен 4 (строка 5 содержит 4 месяца с января по апрель). Итак, адрес правой нижней ячейки нашего динамического диапазона определен — это E 5 .
Когда вы заполните таблицу данными о продажах за май, июнь и так далее, формула READ(sheet1!$B$5:$I$5) вернет количество заполненных ячеек (количество названий месяцев) и таким образом определит новую ширину динамического диапазона, который в свою очередь создаст выпадающий список.
ПРИМЕЧАНИЕ: При использовании функции SCRETZ() убедитесь, что нет пустых ячеек! Т.е. вы должны заполнить список месяцами без пробелов.
Теперь создадим еще один динамический диапазон для подведения итогов продаж.
Для создания динамического диапазона :
- На вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
- В поле Имя введите: Продажи_в_месяц;
- В поле Диапазон введите формулу = AMOUNT(worksheet1!$A$6;;SCHEDULE(worksheet1!$C$1;worksheet1!$B$5:$I$5;0);12).
- нажмите OK.
Функция ПОИСКПОЗ() ищет в строке 5 (список месяцев) месяц, выбранный пользователем (ячейка C1 с выпадающим списком), и возвращает соответствующий номер элемента из диапазона поиска (названия месяцев должны быть уникальными, т.е. этот пример не подходит для нескольких лет). Левый верхний угол нашего динамического диапазона (начиная с ячейки A6) перемещается на это количество столбцов, высота диапазона остается фиксированной — 12 (при желании вы можете сделать ее динамической, в зависимости от количества товаров в диапазоне).
И, наконец, если вы введете формулу = SUMM(Sales_over_month) в ячейку C2, вы получите сумму продаж в выбранном месяце.
Например, в мае месяце.
Или, например, в апреле месяце.
Примечание: Вместо формулы SMUM() можно использовать формулу INDEX() : = $B$5:INDEX(B5:I5;AMOUNT($B$5:$I$5)) для расчета количества завершенных месяцев.
Формула подсчитывает количество элементов в строке 5 (SCRUTZ() ) и определяет ссылку на последний элемент в строке (INDEX() ), таким образом возвращая ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Текущий динамический диапазон можно выделить с помощью условного форматирования . В файле примера правило условного форматирования применяется к ячейкам диапазона B6:I14 с помощью формулы: = столбец(B6)= столбец(Продажи_в_месяц)
Условное форматирование автоматически выделило серым цветом продажи текущего месяца, который был выбран в выпадающем списке.
Пример 2. Определите количество столбцов в таблице и введите это значение в ячейку A16.
Таблица:
Мы используем формулу OVERALL, выбирая в качестве параметра поочередно каждый столбец ячейки. Нажмите и удерживайте клавишу Ctrl перед выбором следующего столбца. Если вы добавите «)» и нажмите Enter, появится диалоговое окно, указывающее на то, что вы ввели слишком много аргументов. Добавьте дополнительные открывающие и закрывающие скобки.
Результат расчета:
Определение принадлежности ячейки к диапазону таблицы
Пример 3 Определяет, принадлежит ли данная ячейка заданному диапазону ячеек.
Рассмотренная здесь функция также позволяет определить, принадлежит ли ячейка выбранному диапазону. Процедура выполняется следующим образом:
-
- Введите часть формулы «=WORLD(()» в любую ячейку и выделите любой диапазон ячеек для заполнения аргументов:
-
- Поставьте пробел и выберите любую ячейку в этом диапазоне:
-
- Закройте обе скобки и нажмите Enter. Результат будет следующим:
-
- Если вы выберете ячейку из диапазона, отличного от указанного, вы получите ошибку #empty!
Эта ошибка указывает на то, что ячейка не принадлежит выбранному диапазону.
Если вы выделите более одной ячейки в диапазоне, функция VARIABLE вернет количество выделенных ячеек:
Описанные возможности этой функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
См. также = IF(ANSWER(A2);A2;B2) вы копируете макрос «Фильтр» соответствующим образом…. Это будет работать только в таблице: Я думаю, что это возможно, если колонна. Т.е. получается, что ВСЕ», затем выполните и нажмите кнопку Вставить более сложную процедуру, щелкните значок Вставить на следующих действиях.Вставить, вы можете выбрать для временного отображения данных, выбранный раздел наПримечание: вставить как выходные значения таблицы с необходимостью?
The_Prist скопируйте выделенный диапазон, который вы выбрали для вышеуказанных действийCtrl+Paste. Чтобы переместить ячейки, щелкните мышью. Вставьте варианты, которые не требуют другого рабочего листа или Попробуйте как можно чаще