Часть 2. Задание 14

Вариант ОГЭ-2023. Демо. 

В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.

В столбце A записан код округа, в котором учится ученик; в столбце B – код фамилии ученика; в столбце C – выбранный учеником предмет; в столбце D – тестовый балл.
Всего в электронную таблицу были занесены данные по 1000 учеников.
Откройте файл с данной электронной таблицей . На основании данных, содержащихся в этой таблице, выполните задания.

1. Сколько учеников, которые проходили тестирование по информатике, набрали более 600 баллов? Ответ запишите в ячейку H2 таблицы.

2. Каков средний тестовый балл учеников, которые проходили тестирование по информатике? Ответ запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.

3. Постройте круговую диаграмму, отображающую соотношение числа участников тестирования из округов с кодами «В», «Зел» и «З».
    Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должны присутствовать легенда (обозначение соответствия данных определённому сектору диаграммы) и числовые значения данных, по которым построена диаграмма.
Полученную таблицу необходимо сохранить под именем, указанным организаторами экзамена.
(выделение цветом сделано мною)

Решение.

Во-первых, предлагаю скопировать содержимое первого листа таблицы скопировать и вставить на второй и третий листы книги (таблицы). Что это дает?
1) Дело в том, что ответы надо вносить в ячейки Н2 и Н3 первого листа. Вам просто не хватит места для промежуточного решения заданий (надо дополнять таблицу дополнительными столбцами с промежуточными результатами).
2) Каждое задание условия желательно выполнять на отдельных листах (страницах) документа, а на первый лист в указанные ячейки поставим ссылки на полученные результаты.
3) Для построения диаграммы необходимо создать еще одну таблицу, устанавливающую связь между количеством участников из отдельных зон и соответствующими зонами.
Но обо все по порядку. Итак,

1 шаг. На первой странице (страницы с условиями задания) скопируем столбцы A, B, C и D. Для этого щелкаем мышкой, при нажатой клавиши клавиатуры Shift, по заголовкам таблицы.

Скопировать выделенные столбы. Это можно выполнить двумя способами: или комбинацией клавиш Ctrl + C или используя инструменты панели инструментов (смотри рисунок ниже).

Переходим на Лист2, устанавливаем активный курсор в ячейке с адресом А1 (первая ячейка страницы) и нажимаем комбинацию клавиш Ctrl + V . Эту же процедуру можно выполнить и при помощи инструментов панели инструментов.

Перейти на Лист3 и повторить процедуру вставки.
Примечание. Если не получилось вставить скопированный фрагмент документа, проверьте, остался ли активным копируемый фрагмент документа на Лист1 (вокруг фрагмента должен быть прямоугольник из пунктирных линий)?

2 шаг. Переходим на Лист2 и приступаем к выполнению задания №1: Сколько учеников, которые проходили тестирование по информатике, набрали более 600 баллов?
Для ответа на поставленный вопрос используем стандартные функции среды Excel. Нам предстоит сделать выборку учащихся, отвечающих условиям (их два: информатика в столбце С и число, больше 600, в столбце D). И эти два условия должны выполняться одновременно (логическая операция И). То есть, если выполняются оба условия, то поставить единицу напротив кода ученика в столбце Е. И после этого другой стандартной функцией подсчитать, сколько таких единиц получилось. Ниже приводится по-шаговые действия выполнения найденного алгоритма.
1) установим курсор в столбце Е (Е2) и открываем список стандартных функций:

Если в качестве Лог_выражения используется сложное выражение или другая стандартная функция, то это выражение надо вводить вручную. Или разбить выполнение на несколько шагов. В нашем алгоритме необходимо использовать логическую функцию И.
Совет. Если сомневаетесь в написании функции (например, использование логической функции) - проверьте ее действие в столбце, свободном от записей. Потом скопируйте ее в строке ввода (fx) и просто вставьте затем ее в окне свойств выбранной функции в качестве Лог_выражения.
Покажем на примере использования логической функции И процесс проверки применения ее при расчетах: поставим курсор в ячейке F2, выберем стандартную функцию И (категория - Логические). В окне свойств функции И

заполняем строки Лог_выражение1 и Лог_выражение2 вручную. Дело в том, что в ячейке С2 - записано слово "физика". Естественно и в ячейке D2 - не тот балл. Значит, наша функция должна возвращать значение ЛОЖЬ, что и отражается в окне свойств функции по мере ввода данных. Нажимаем Ок. Получим запись в ячейке F2 - ЛОЖЬ. Наводим курсор на правый нижний угол ячейки F2 (должен появиться курсор в виде крестика)  и, при нажатой левой кнопки мыши, тянем мышью вниз, заполняя нижележащие ячейки столбца F2 формулой, записанной по первому адресу.

Видно, что выделенные ячейки заполняются формулой, записанной в ячейке по адресу F2. А в каждой строке отражается информация для соответствующей записи. Это видно по рисунку ниже:

Обратите внимание, что для ячейки F8 в строке ввода указана запись, отвечающая условию этой конкретной записи (С8="информатика";С8>600). Следовательно, при формировании логических выражений нами использовались относительные адреса. То есть, "бери значения слева от выделенной ячейки, но в этой строке!". Слева от активного курсора - это и есть: относительно от данной ячейки.
Теперь, после проверки логической функции И, копируем ее выражение и вставляем в нашу функцию если в качестве Лог_выражения (смотри окно свойств - (2) - выше).
В этом же окне (для стандартной функции если) в строке Значение_если_истина заносим значение 1, а в строке Значение_если_ложь заносим двойные кавычки (""), что означает оставь пустым.

После нажатия клавиши Ок содержание ячейки Е2 остается "пустым", но мы можем видеть, что в строке ввода есть запись стандартной функции. Следовательно, ничего не выведено, так как логическое выражение ложно и ячейка не заполняется видимой информацией. А остальные ячейки этого фрагмента таблицы заполнены протяжкой левой кнопкой мыши за указатель в виде крестика в правом нижнем углу ячейки Е2. Но у нас 1000 записей. Можно заполнить весь столбец (метод называется - по образцу), щелкнув дважды по указанному указателю в виде крестика. Заполняются все ячейки данного столбца, если в левых ячейках есть информация. Так как время на экзамене на вес золота, то воспользуйтесь данным методом.
Для подсчета количества единиц в столбце Е2, а это и есть ответ на первое задание надо воспользоваться стандартной функцией СЧЁТ, которая подсчитывает количество непустых ячеек в указанном диапазоне.

Совет. Чтобы не тратить время на работу с мышью, запишите у себя в черновике данные: первая строка с информацией (в нашем случае - 2) и последняя строка (1001).
Установим курсор в ячейке Н2, но на странице Лист2, нажмем на вставку стандартной функции в строке ввода выражения. Выбираем функцию СЧЕТ...

В строку Значение1 заносим диапазон подсчета (вручную, можно конечно и мышкой протянуть по всему столбцу Е2) - Е2:Е1001 (вот здесь и пригодились записи черновика, это будет быстрее, чем использование мыши).
Обратите внимание, что до того, как будет нажата кнопка Ок, в окне свойств функции уже отображается итог подсчета (= 32).
После нажатия кнопки Ок это же значение и будет записано в ячейку Н2.
Для того, чтобы занести результат поиска решения на первую страницу работы, перейдем на Лист1, выберем ячейку Н2 и нажмем клавишу клавиатуры "=". Затем щелкнем левой кнопкой мыши на заголовок Лист2 (я переименовала лист2 в Задание1) - переходим на Лист2 и щелкаем по ячейке Н2 и нажимаем кнопку Enter  клавиатуры. На первой странице в ячейку Н2 будет вписан результат.

Обратите внимание на строку ввода: ='1 Задание'!Н2, а в ячейке появилось значение: 32. В окне адреса ячейки стоит конкретный адрес: Н2 (ячейка Н2 листа1). Если вы не будете переименовывать ваши листы книги, то у вас может быть вот такая запись: ='Лист2'!Н2. В общем виде: ='Название страницы документа'<! -разделительный знак>Адрес ячейки, из которой копируется значение. 

3 шаг. Переходим на Лист3 и приступаем к выполнению задания 2: Каков средний тестовый балл учеников, которые проходили тестирование по информатике? Ответ запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.
Для ответа на поставленный вопрос, необходимо найти:
1) количество всех учащихся, которые сдавали тест по информатике;
2) суммарный балл, набранный всеми учащимися, которые сдавали тест по информатике.
3) средний балл = сумма баллов/кол-во учащихся, сдавших тест
Для хранения промежуточных значений выбираем следующие ячейки: F2 - для отражения количества всех учащихся, которые сдавали тест по предмету информатика, G2 - для записи суммарного балла. И ячейку Н3 - для записи среднего балла.
Для первой подзадачи используем стандартную функцию СЧЁТЕСЛИ.

В строке Диапазон указываем диапазон поиска в столбце Предмет (С2:С1001). Столбец С содержит название предметов.
В строке Критерий либо пишем слово "информатика" либо щелкаем левой кнопкой мыши по ячейке в столбце Предмет, где записано название сдаваемого предмета (А в ячейке С8  как раз и содержится слово "информатика"). И, опять же, если все сделано правильно, результат мы можем увидеть еще до нажатия клавиши Ок. Итак, в ячейке F2 - впишется число 72.
Для второй подзадачи используем стандартную функцию СУММЕСЛИ.

Устанавливаем курсор в ячейку G2 и выбираем стандартную функцию СУММЕСЛИ.Диапазон значений для данной функции включает два столбца (один - название предмета, второй - значение баллов по предмету), поэтому в строку Диапазон вписываем C2:D1001.
Критерий: информатика (предмет). Если вы не поставите кавычки, вписывая предмет, как я, то Мастер сам заключит слово в кавычки.
Диапазон_суммирования - необходимо указать столбец, откуда взять числовые значения баллов - D2:D1001.
Нажимаем клавишу Ок и в ячейке G2 вписывается число 39371.
Для нахождения среднего балла устанавливаем курсор в ячейке Н3, нажимаем клавишу клавиатуры "=". Мышкой щелкаем по ячейке G2, нажимаем клавишу "/", щелкаем мышкой по ячейке F2 и нажимаем клавишу Enter клавиатуры.

В ячейке Н3 вписалось число 546,8194 (разрядность числа может быть другой, все зависит от настройки среды Excel). Для выставления заданного разряда - количество знаков после запятой - используйте клавишу Уменьшить разрядность (как на рисунке выше). Если требуется увеличить разрядность числа - используйте инструмент слева от того, на который указывает курсор мыши на рисунке.
Теперь перейдите на первую страницу документа и в ячейке H3 сделайте ссылку на полученный результат, который находится на третьем листе документа.

Поясню полученный результат. Третий лист документа (Лист3) у меня переименован в 2 задание.
Внимание! Покажу как можно переименовать название листа документа:
1) Двойным щелчком мыши выделить название, например, Лист3. набрать с клавиатуры новое название страницы (например, 2 задание) и нажать клавишу Enter клавиатуры.
2) 2 способ. Навести указатель мыши на название страницы документа (внизу документа, листы изображены в виде закладок), щелкнуть правой кнопкой мыши и выбрать команду: Переименовать.

Название страницы будет выделено. Остается ввести новое имя страницы и нажать клавишу Enter.

4 шаг. Задание: Постройте круговую диаграмму, отображающую соотношение числа участников тестирования из округов с кодами «В», «Зел» и «З».
    Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должны присутствовать легенда (обозначение соответствия данных определённому сектору диаграммы) и числовые значения данных, по которым построена диаграмма.
Последовательность выполнения задания.
1) Переходим на Лист1.
2) Так как левый верхний угол диаграммы должен быть вблизи ячейки G6, то решим первую подзадачу: какие ячейки выбрать для отображения числовых значений данных, по которым потом будем строить диаграмму. Таблица будет содержать 6 ячеек (3 столбца, 2 строки). Первые три ячейки первой строки будем использовать для обозначения районов (В, Зел, З). А во второй строке: кол-во участников из указанных районов. Диапазон для вспомогательной таблицы: G24:I25.

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

Критерий - это ячейки указанного столбца (А), в которых прописаны названия районов. Для ячейки G25 - В, H25 -Зел, I25 - З.
Диапазон для всех ячеек вспомогательной таблицы один и тот же: А2:А1001.
В итоге получаем заполненную таблицу:

Выделяем все ячейки диапазона G24 - I25, переходим на вкладку Вставка, выбираем инструмент Круговая\Разрезанная круговая.

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

 Определяем, все ли стили форматирования учтены? Легенда есть.
Легендой называется область диаграммы, в которой отображено соответствие между частями диаграммы и районами, в которых учащиеся сдавали тест по информатике.
Замечаем, что отсутствуют числовые значения, по которым построена диаграмма (не отображены размеры отдельных частей диаграммы в числовом выражении).
Переходим на вкладку Макет и выбираем Подписи данных. Размещение значений выбираем: снаружи.

В итоге получим вот такую диаграмму:

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

Задания для самостоятельной реализации

Задание 1. В электронную таблицу занесли результаты тестирования учащихся по математике и физике. На рисунке приведены первые строки получившейся таблицы.

В столбце A указаны фамилия и имя учащегося; в столбце B – район города, в котором расположена школа учащегося; в столбцах C, D – баллы, полученные соответственно по математике и физике. По каждому предмету можно было набрать от 0 до 100 баллов.
Всего в электронную таблицу были занесены данные по 1000 учащихся. Порядок записей в таблице произвольный.
Выполните задание.
Откройте файл с данной электронной таблицей (архив "допфайлы.zip" Вариант 13). На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
1. Чему равна наибольшая сумма баллов по двум предметам среди учащихся Майского района? Ответ на этот вопрос запишите в ячейку G1 таблицы.
2. Сколько процентов от общего числа участников составили ученики Майского района?
    Ответ с точностью до одного знака после запятой запишите в ячейку G2 таблицы.
3. Постройте круговую диаграмму, отображающую соотношение количества участников из Майского, Кировского и Центрального районов. Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должна присутствовать легенда (обозначение, какой сектор диаграммы соответствует каким данным) и числовые значения данных, по которым построена диаграмма. 

Задание 2. В электронную таблицу занесли численность населения городов разных стран. На рисунке приведены первые строки получившейся таблицы.

В столбце A указано название города, в столбце B – численность населения (тыс. чел.), в столбце C – название страны.
Всего в электронную таблицу были занесены данные по 1000 городам. Порядок записей в таблице произвольный.
Выполните задание.
Откройте файл с данной электронной таблицей (скачать архив с файлом). На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
1. Сколько жителей в самом крупном по численности населения городе Египта? Ответ на этот вопрос (в тыс. чел.) запишите в ячейку F2 таблицы.
2. Сколько городов Египта имеют жителей больше, чем средняя численность населения городов, представленных в таблице? Ответ на этот вопрос запишите в ячейку F3.
3. Постройте круговую диаграмму, отображающую соотношение количества городов Швейцарии, Канады и Германии, представленных в таблице. Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должна присутствовать легенда (обозначение, какой сектор диаграммы соответствует каким данным) и числовые значения данных, по которым построена диаграмма.

Задание 3. файл

В электронную таблицу внесены данные о проданных путёвках за некоторый период (см. табл. ниже). В столбце А записана дата nродажи путёвки, в столбце В - количество проданных путёвок, в столбце С - город, в который осуществлялась поездка, в столбце D - продолжительность поездки. Всего в таблицу занесены данные по 1023 операциям продажи путёвок.

Откройте файл с электронной таблицей. На основании данных, указанных в таблице, выполните задания.

1. Определите общее количество путёвок, проданных 03.01.2022 в город Псков. Ответ на этот вопрос запишите в ячейку НЗ таблицы.

2. Определите среднее количество проданных путёвок продолжительностью более 4 дней. Ответ укажите в ячейке Н4, округлив до сотых по правилам математического округления.

3. Постройте круговую диаграмму, показывающую соотношение продаж за весь период в города Мурманск, Няндому и Ярославль. В поле диаграммы должна присутствовать легенда (указание для каждого сектора, каким данным он соответствует) и числовые данные, по которым строится диаграмма. Левый верхний угол диаграммы расположите вблизи ячейки Н6.

Задание 4. фал

В электронную таблицу занесли информацию о товарах на складе некоторого магазина. Ниже приведены первые несколько строк данной таблицы (см. табл. ниже).

Каждая строка таблицы содержит сведения о товарах на складе. В столбце А записаны наименования продукции; в столбце В - бренд; в столбце С - количество товара на складе ( ед.); в столбце D - стоимость единицы товара (руб.). Всего в электронной таблице имеются данные о 1810 наименованиях товаров.

Откройте файл с электронной таблицей. На основании данных, содержащихсяся в этой таблице, выполните задания.

1.  Определите количество наименований, по которым на складе имеется меньше 10 единиц товара. Ответ запишите в ячейку G1 таблицы

2.  Найдите наибольшую стоимость (Цена ✖  Количество на складе) товара на складе по всем наименованиям. Ответ запишите в ячейку Н1 таблицы.

3.  Постройте круговую диаграмму, отображающую соотношение количества товара на складе по брендам «ЩИТ», «MICR», «ЛИСТ». Левый верхний угол диаграммы разместите вблизи ячейки G6.

Полученную таблицу сохраните.

По указанному адресу (https://yandex.ru/tutor/subject/tag/problems/?ege_number_id=2110&tag_id=19) находятся еще 6 вариантов заданий (решение одного варианта приводится здесь).