Сколько в списке учащихся из майского района

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

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

Начало таблицы. Файл находится в архиве к демонстрационному варианту (на странице "Основные материалы к ОГЭ-2022")
Всего записей в таблице 1000 .

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

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

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

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

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

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

Вопрос 14, часть 2 (практика) ОГЭ (ГИА-9): Умение проводить обработку большого массива данных с использованием средств электронной таблицы

Кодификатор 2.3.2/2.6.1/2.6.2/2.6.3/3.1. Уровень сложности В, 2 балла.

Время выполнения — 30 минут.

Теоретический материал по Excel.

В 2020 году до кого-то дошло, что уже пора убрать отсюда базы данных (чего не надо было включать с первого дня).

Указания по оцениванию	Баллы
Во всех случаях допустима запись ответа в другие ячейки (отличные от тех, которые указаны в задании) при условии правильности полученных ответов. Также допустима запись ответов с точностью более двух знаков.
Получены правильные ответы на два вопроса и верно построена диаграмма	3
Не выполнены условия, позволяющие поставить 3 балла. При этом имеет место одна из следующих ситуаций: — получен правильный ответ только на один из двух вопросов, и верно построена диаграмма; — получены правильные ответы на оба вопроса, диаграмма построена неверно	2
Не выполнены условия, позволяющие поставить 2 балла. При этом имеет место одна из следующих ситуаций: — получен правильный ответ только на один из двух вопросов; — диаграмма построена верно	1
Не выполнены условия, позволяющие поставить 1, 2 или 3 балла	0
Максимальный балл	3

Лично у меня нет ни одного замечания по критериям. Четко, просто, понятно.

1. Пессимистично. «На компьютере должны быть установлены знакомые участникам экзамена программы». (Методические рекомендации. 2020, Рособрнадзор от 16.12.2019 №10-1059)
   Но установить абсолютно все невозможно и это надо понимать.
   Пример. Версии Excel 2007–2019 отличаются, но не настолько принципиально, чтобы поднимать шум. Если же вам предложили использовать Excel 2003 (XP) (или совсем другую программу), то вы имеете право требовать замены. Ее не будет (однозначно) и потребуется написание апелляции, по результатам которой вам обязаны добавить баллы до максимального даже при невыполненном задании.
2. Первое и главное, что надо понять: задания 1 и 2 в принципе не предполагают проведения расчетов, их может вообще не быть в файле. Только запись ответов. Если вы их списали, то это остается на вашей совести, и вы не обязаны никому ничего доказывать, снижение баллов недопустимо!
3. Эффективное выполнение возможно только при умении пользоваться клавиатурой.
4. Критерии допускают запись ответов в других ячейках. Изначальные ячейки заданы не от большого ума, так как сформирована утопическая идеальная схема выполнения. Таким образом, для большого числа экзаменуемых эти ячейки потребуются для обработки.
   Как быть?
   1. Если вам эти ячейки не нужны, разместите ответы в соответствии с заданием
   2. Войдите в положение проверяющего и не складывать ответы не пойми куда.
   3. Идеальным решением будет не используемая 1-я строка. Напрашиваются ячейки H1 и I1: и найти несложно и вам не мешает.
   4. Диаграмма ляжет на ваши расчеты с вероятностью не менее 90%. Но вам это уже не помешает никак.
5. .

Возможные алгоритмы выполнения

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

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

Следующий момент — включение автофильтра, создающего потенциальные варианты косвенной проверки (или оценки) правильности хода решения.

Алгоритмы для задания 1

Доступ к размещенным в этом месте материалам ограничен и предоставляется следующим категориям:
1. Студент I/II курса ВХК РАН. 2. Бывший студент ВХК РАН. 3. Подготовка к ОГЭ. 4. Подготовка к ЕГЭ. 5. VIP-пользователь. 6. Благотворитель.

Алгоритмы для задания 2

Доступ к размещенным в этом месте материалам ограничен и предоставляется следующим категориям:
1. Студент I/II курса ВХК РАН. 2. Бывший студент ВХК РАН. 3. Подготовка к ОГЭ. 4. Подготовка к ЕГЭ. 5. VIP-пользователь. 6. Благотворитель.

Алгоритмы для задания 3

Для формирования любой диаграммы, нужно подготовить таблицу с данными.

Доступ к размещенным в этом месте материалам ограничен и предоставляется следующим категориям:
1. Студент I/II курса ВХК РАН. 2. Бывший студент ВХК РАН. 3. Подготовка к ОГЭ. 4. Подготовка к ЕГЭ. 5. VIP-пользователь. 6. Благотворитель.

Мне кажется, что об альтернативных вариантах говорить нет нужды.

Задания

1. Демо 2022 (14). Дублирует Демо 2020 (14).
2. Демо 2021 (14). Дублирует Демо 2020 (14).
   Файл к заданию, похоже, тот же самый.
   Для задания 3 добавлено: «В поле диаграммы должна присутствовать легенда (обозначение, какой сектор диаграммы соответствует каким данным) и числовые значения данных, по которым построена диаграмма».
3. Демо 2020 (14). В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.

В столбце A записан код округа, в котором учится ученик, в столбце B — фамилия, в столбце C — выбранный учеником предмет, в столбце D — тестовый балл.
Всего в электронную таблицу были занесены данные по 1000 учеников.
Выполните задание
Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На основании данных, содержащихся в этой таблице, выполните задания:
1. Определите, сколько учеников, которые проходили тестирование по информатике, набрали более 600 баллов. Ответ запишите в ячейку H2 таблицы.
2. Найдите средний тестовый балл учеников, которые проходили тестирование по информатике. Ответ запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.
3. Постройте круговую диаграмму, отображающую соотношение количеств участников из округов с кодами «В», «Зел» и «З». Левый верхний угол диаграммы разместите вблизи ячейки G6.
Полученную таблицу необходимо сохранить под именем, указанным организаторами экзамена.

В столбце A электронной таблицы записана фамилия участника, в столбце B — имя участника, в столбце C — класс, в котором учится участник, в столбцах D, E, F и G — оценки каждого участника, полученные за каждую из четырех задач, предлагавшихся на олимпиаде. Всего в электронную таблицу были занесены результаты 1000 участников.
По данным результатам жюри хочет определить победителя олимпиады и трех лучших участников. Победитель и лучшие участники определяется по сумме всех баллов, а при равенстве баллов — по количеству полностью решенных задач (чем больше задач решил участник полностью, тем выше его положение в таблице при равной сумме баллов). Задача считается полностью решена, если за нее выставлена оценка 10 баллов.
Выполните задание
Откройте файл с данной электронной таблицей (расположение файла вам сообщат организаторы экзамена). После этого отсортируйте данную таблицу в порядке уменьшения результатов участников, то есть по уменьшению количества баллов, а при равном количестве баллов у участников — по уменьшению количества верно решенных задач. При этом первая строка таблицы, содержащая заголовки столбцов, должна остаться на своем месте. Полученную таблицу необходимо сохранить в каталоге под именем, указанным организаторами экзамена.

Copyright © 1993–2023 Мацкявичюс Д.А. Все права защищены.
Никакая часть сайта не может быть воспроизведена никаким способом без письменного разрешения правообладателя и явной ссылки на данный ресурс.

У скольких учащихся фамилия начинается с буквы з excel

* И еще, допустим, первое задание звучит вот так
1. У скольких учащихся фамилия начинается с буквы « У «?
Проверьте. По Вашей формуле получится ответ 4, а на самом деле 3. Не всегда краткость — сестра, иногда в СЧЁТЕСЛИ лучше диапазон нормальный писать

Доброго времени суток, подскажите пожалуйста формулу для решения задачи п.3 в прилагаемом файле.

3. Кто из учащихся получил высший балл по русскому языку?
Запишите фамилии учащихся в ячейки 4-й строки, начиная с G4.

Доброго времени суток, подскажите пожалуйста формулу для решения задачи п.3 в прилагаемом файле.

3. Кто из учащихся получил высший балл по русскому языку?
Запишите фамилии учащихся в ячейки 4-й строки, начиная с G4. Rassell89

Сообщение Доброго времени суток, подскажите пожалуйста формулу для решения задачи п.3 в прилагаемом файле.

3. Кто из учащихся получил высший балл по русскому языку?
Запишите фамилии учащихся в ячейки 4-й строки, начиная с G4. Автор — Rassell89
Дата добавления — 23.04.2020 в 22:16

Урок 26
§26. Сортировка данных

Перейдите в каталог work-29 и откройте файл exam.xls (или exam.ods).

Уровень A

Используя сортировку, ответьте на вопросы:

1) У скольких учащихся фамилия начинается с буквы «З»?

2) Сколько в списке учащихся из Майского района?

3) Кто из учащихся получил высший балл по русскому языку?

Уровень B

Используя сортировку, ответьте на вопросы:

4) Сколько процентов учащихся получили 41 балл по математике?

5) Сколько учащихся сдали русский язык выше, чем на 50 баллов?

6) Сколько процентов от общего числа учащихся составляют жители Заречного района?

Уровень C

Используя сортировку, ответьте на вопросы:

7) Сколько процентов из учащихся Майского района получили 18 баллов по математике?

Кто из учащихся Подгорного района получил лучший балл по русскому языку?

9) Кто из учащихся, получивших 38 баллов по математике, получил лучший балл по русскому языку?

Следующая страница §26. Сортировка данных

Cкачать материалы урока

Вопрос 14, часть 2 (практика) ОГЭ (ГИА-9): Умение проводить обработку большого массива данных с использованием средств электронной таблицы

Кодификатор 2.3.2/2.6.1/2.6.2/2.6.3/3.1. Уровень сложности В, 2 балла.

Время выполнения — 30 минут.

Теоретический материал по Excel.

В 2020 году до кого-то дошло, что уже пора убрать отсюда базы данных (чего не надо было включать с первого дня).

Указания по оцениванию	Баллы
Во всех случаях допустима запись ответа в другие ячейки (отличные от тех, которые указаны в задании) при условии правильности полученных ответов. Также допустима запись ответов с точностью более двух знаков.
Получены правильные ответы на два вопроса и верно построена диаграмма	3
Не выполнены условия, позволяющие поставить 3 балла. При этом имеет место одна из следующих ситуаций: — получен правильный ответ только на один из двух вопросов, и верно построена диаграмма; — получены правильные ответы на оба вопроса, диаграмма построена неверно	2
Не выполнены условия, позволяющие поставить 2 балла. При этом имеет место одна из следующих ситуаций: — получен правильный ответ только на один из двух вопросов; — диаграмма построена верно	1
Не выполнены условия, позволяющие поставить 1, 2 или 3 балла	0
Максимальный балл	3

Лично у меня нет ни одного замечания по критериям. Четко, просто, понятно.

1. Пессимистично. «На компьютере должны быть установлены знакомые участникам экзамена программы». (Методические рекомендации. 2020, Рособрнадзор от 16.12.2019 №10-1059)
   Но установить абсолютно все невозможно и это надо понимать.
   Пример. Версии Excel 2007–2019 отличаются, но не настолько принципиально, чтобы поднимать шум. Если же вам предложили использовать Excel 2003 (XP) (или совсем другую программу), то вы имеете право требовать замены. Ее не будет (однозначно) и потребуется написание апелляции, по результатам которой вам обязаны добавить баллы до максимального даже при невыполненном задании.
2. Первое и главное, что надо понять: задания 1 и 2 в принципе не предполагают проведения расчетов, их может вообще не быть в файле. Только запись ответов. Если вы их списали, то это остается на вашей совести, и вы не обязаны никому ничего доказывать, снижение баллов недопустимо!
3. Эффективное выполнение возможно только при умении пользоваться клавиатурой.
4. Критерии допускают запись ответов в других ячейках. Изначальные ячейки заданы не от большого ума, так как сформирована утопическая идеальная схема выполнения. Таким образом, для большого числа экзаменуемых эти ячейки потребуются для обработки.
   Как быть?
   1. Если вам эти ячейки не нужны, разместите ответы в соответствии с заданием
   2. Войдите в положение проверяющего и не складывать ответы не пойми куда.
   3. Идеальным решением будет не используемая 1-я строка. Напрашиваются ячейки H1 и I1: и найти несложно и вам не мешает.
   4. Диаграмма ляжет на ваши расчеты с вероятностью не менее 90%. Но вам это уже не помешает никак.
5. .

Возможные алгоритмы выполнения

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

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

Следующий момент — включение автофильтра.

Алгоритмы для задания 1

1. Устанавливаем фильтр по предмету.
2. Устанавливаем второй фильтр на баллы, с условием >600.
3. Выделяем любую колонку и считываем в правом нижнем углу Количество.
4. Так как в него попал заголовок (1-я строка), вычитаем единицу. Ответ получен!
5. Время выполнения не превысит 1 минуту.

1. Нам потребуется слово «информатика», которое лучше сразу скопировать. Набирать руками в данном случае чреваты ошибками!
2. Формула должна считать.
3. Счет идет сразу по двум условиям, откуда появляется необходимость использования функции СЧЁТЕСЛИМН.
4. Ставим курсор в нужную ячейку.
5. Вводим значения в диалоге (скопированное слово пригодится). Итог: =СЧЁТЕСЛИМН(C:C;»информатика»;D:D;»>600″).

Обратите внимание на предложенную авторами задания формулу =ЕСЛИ(И(C2=»информатика»; D2>600); 1;0)).

1. Естественно, абсолютно необходимым будет знание простейших функций.
2. Вставим в ячейку E2 формулу =ЕСЛИ(C2=»информатика»; ИСТИНА; ЛОЖЬ)). [Интересна другая версия, но не здесь: =ЕСЛИ(C2=»информатика»; 1; «»)]
3. Скопируем ее в диапазон E2:E1001.
4. Результатом станет вывод ИСТИНА только в тех ячейках, где указан нужный нам предмет.
5. В ячейку F2 запишем формулу =ЕСЛИ(D2>600; ИСТИНА; ЛОЖЬ)) и также распространим ее на всю колонку.
6. В ячейку G2 запишем формулу =E2+F2, дающую значения 0, 1 или 2. Последнее совпадает с искомыми нами строками.
7. В ячейку H2 запишем формулу =ЕСЛИ(G2=2; 1; «») и размножим. Именно ради этого мы и работали. Все ненужные ячейки выглядят пустыми, а нужные можно сосчитать функцией.
8. Умнее было бы вписать сразу три формулы и размножать их. Но кто знаком с быстрыми способами работы, потратит лишь несколько лишних секунд.
9. Вряд ли такой трудоемкий способ удобен для экзамена. Важен принцип разделения на сколь угодно много частей с их последующим обобщением.

Алгоритмы для задания 2

1. Нам потребуется слово «информатика», которое лучше сразу скопировать.
2. Формула должна вычислять среднюю.
3. Счет идет по условию из другой колонки: нужна функция СРЗНАЧЕСЛИ.
4. Ставим курсор в нужную ячейку.
5. Вводим значения в диалоге (скопированное слово пригодится). Итог: =СРЗНАЧЕСЛИ(C:C;»информатика»;D:D).
6. Время выполнения не превысит 1 минуту.

1. Скопируем слово «информатика».
2. В ячейку I2 запишем формулу =ЕСЛИ(C5=»информатика»; D5; «»)
3. Скопируем ее в диапазон I2:I1001.
4. Теперь по этой колонке можно посчитать обычную среднюю: =СРЗНАЧ(I:I).

Алгоритмы для задания 3

Для формирования любой диаграммы, нужно подготовить таблицу с данными.

1. Введем в три ячейки одной колонки значения по заданию: В, Зел и З. (Мной использованы ячейки Q3, Q4 и Q5, что повлияет на формулы.)
2. В соседнюю с «В» ячейку (R3) введем формулу: =СЧЁТЕСЛИ(A:A;Q3) и скопируем ее в R4 и R5.
3. Выделим 6 полученных ячеек и вставим диаграмму требуемого типа. Кстати, авторы произвели запрещенное разнесение секторов.
4. Время выполнения не превысит 2 минуты.
5. Таблица для диаграммы крайне примитивна. В более сложном варианте может потребоваться много больше времени!

Мне кажется, что об альтернативных вариантах говорить нет нужды.

Задания

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

Copyright © 1993–2022 Мацкявичюс Д.А. Все права защищены.
Никакая часть сайта не может быть воспроизведена никаким способом без письменного разрешения правообладателя и явной ссылки на данный ресурс.

• №16239
• Скачать
• Формулы
• Ответы
• Видео
• Обсуждения

Просмотр

Если НЕ ОТОБРАЗИЛИСЬ материалы, то
ОБНОВИТЕ СТРАНИЦУ
или СКАЧАЙТЕ ИХ!

Урок 26
§26. Сортировка данных

Перейдите в каталог work-29 и откройте файл exam.xls (или exam.ods).

Уровень A

Используя сортировку, ответьте на вопросы:

1) У скольких учащихся фамилия начинается с буквы «З»?

2) Сколько в списке учащихся из Майского района?

3) Кто из учащихся получил высший балл по русскому языку?

Уровень B

Используя сортировку, ответьте на вопросы:

4) Сколько процентов учащихся получили 41 балл по математике?

5) Сколько учащихся сдали русский язык выше, чем на 50 баллов?

6) Сколько процентов от общего числа учащихся составляют жители Заречного района?

Уровень C

Используя сортировку, ответьте на вопросы:

7) Сколько процентов из учащихся Майского района получили 18 баллов по математике?

Кто из учащихся Подгорного района получил лучший балл по русскому языку?

9) Кто из учащихся, получивших 38 баллов по математике, получил лучший балл по русскому языку?

Следующая страница §26. Сортировка данных

Cкачать материалы урока

Задание 14 № 18283

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

A	B	C	D
1	Ученик	Район	Математика	Физика
2	Шамшин Владислав	Майский	65	79
3	Гришин Борис	Заречный	52	30
4	Огородников Николай	Подгорный	60	27
5	Богданов Виктор	Центральный	98	86

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

Выполните задание

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

1. Чему равна наибольшая сумма баллов по двум предметам среди учащихся Майского района? Ответ на этот вопрос запишите в ячейку G1 таблицы.

2. Сколько процентов от общего числа участников составили ученики Майского района? Ответ с точностью до одного знака после запятой запишите в ячейку G2 таблицы.

3. Постройте круговую диаграмму, отображающую соотношение количества участников из Майского, Кировского и Центрального районов. Левый верхний угол диаграммы разместите вблизи ячейки G6.

1. В столбце Е для каждого учащегося вычислим сумму баллов по двум предметам, если это ученик Майского района. Для ученика другого района ячейка будет содержать пустую строку. В ячейку E2 запишем формулу =ЕСЛИ(B2=»Майский»;C2+D2;»») Скопируем формулу во все ячейки диапазона Е3:Е1001. Для того чтобы найти наибольшую сумму, в ячейку G1 внесём формулу =MАКС(E2:E1001). Получаем: 194.

2. Для ответа на второй вопрос в ячейке H3, найдём количество учеников Майского района, принимавших участие

в тестировании, с помощью формулы: =СЧЁТЕСЛИ(B2:B1001;»Майский»). Выразим полученное значение в процентах от общего числа участников тестирования. Результат запишем в ячейку G2: =H3/1000*100. Получаем: 39,1.

3. В ячейку G2 вставим формулу =СЧЁТЕСЛИ(B2:B1001; »Майский»), в ячейку G3 вставим формулу =СЧЁТЕСЛИ(B2:B1001; »Кировский»), в ячейку G4 вставим формулу =СЧЁТЕСЛИ(B2:B1001; »Центральный»). Теперь построим по полученным значениям круговую диаграмму, подпишем сектора.

Ответ: 1. 194; 2. 39,1.

Не выполнены условия, позволяющие поставить 3 балла. При этом имеет место одна из следующих ситуаций:

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

Не выполнены условия, позволяющие поставить 2 балла. При этом имеет место одна из следующих ситуаций:

На районной олимпиаде по математике 5 учеников набрали одинаковое количество баллов и стали победителями.Сколько вариантов

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

В таком случае при выборе первого участника количество способов будет равно 5.

После этого у нас остаются еще:

5 — 1 = 4 победителя.

Среди них мы также можем выбрать только одного.

При выборе второго участника городской олимпиады, количество способов будет равно 4.

Для того, чтоб найти общее число способов, умножаем способы при выборе первого на количество способов при выборе второго.

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

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

Подсчет ячеек, начинающихся с определенной буквы с помощью Kutools for Excel

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

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

1. Введите любую из следующих формул в пустую ячейку:

= СЧЁТЕСЛИ (A2: A15; «B *»)

= СУММПРОИЗВ ((ЛЕВЫЙ (A2: A15) = «B») * 1)

Внимание: В приведенных выше формулах A2: A15 это диапазон данных, который вы хотите подсчитать, а буква B — это критерии, с которых вы хотите начать подсчет.

2, Затем нажмите Enter key, и вы получите нужный результат подсчета, см. снимок экрана:

Tips: Если вы хотите подсчитать количество ячеек, значения которых не начинаются с буквы B, вы можете применить эту формулу: = СУММПРОИЗВ ((ЛЕВЫЙ (A2: A15) <> «B») * (A2: A15 <> «»)) , и нажмите Enter ключ для получения результата. Смотрите скриншот:

Подсчет ячеек, начинающихся с определенной буквы с помощью Kutools for Excel

Если вы устали от формул, я расскажу о вас как о простом и мощном инструменте —Kutools for Excel, С его Выбрать определенные ячейки утилита, вы можете не только подсчитывать ячейки, начинающиеся с буквы B, но и выбирать эти конкретные ячейки.

После установки Kutools для Excel, выполните шаги:

1. Выберите диапазон данных, который вы хотите использовать.

2. Нажмите Кутулс > Выберите > Выбрать определенные ячейки, см. снимок экрана:

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

4. Затем нажмите Ok or Применитьпоявится окно с подсказкой, в котором будет указано количество ячеек, соответствующих вашему условию, и они также будут выбраны. Смотрите скриншот:

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

Как найти наибольшую сумму баллов по двум предметам в excel

Функции СУММЕСЛИ и СУММЕСЛИМН помогут посчитать сумму для тех значений столбца, которые соответствуют указанному критерию.

Для примера рассмотрим следующую таблицу. В ней указано имя и пол ученика, в каком классе учится и средний бал. Используя функцию СУММЕСЛИ, решим такую задачу: посчитаем сумму значений из столбца «Средний бал» для всех мальчиков и для всех девочек. Задача для функции СУММЕСЛИМН такая: посчитать сумму среднего бала для мальчиков и девочек, которые учатся в 8 и 9 классе отдельно.

Рассмотрим аргументы для СУММЕСЛИ.

=СУММЕСЛИ(диапазон; критерий; [диапазон суммирования])

Диапазон — здесь необходимо указать, тот столбец, данные в котором будут сравниваться с заданным Критерием. В примере, это Диапазон D2:D26 .

Критерий — здесь нужно выбрать одно значение из ранее указанного Диапазона. Сначала будем указывать мальчиков, затем девочек.

Диапазон суммирования — это столбец, значения в котором нужно просуммировать, если они относятся к выбранному Критерию. Например, если для выбранного Диапазона D2:D26 указан Критерий «мальчик» , значит в столбце «Средний бал» : С2:С26 , будут суммироваться все значения, которые относятся к мальчикам.

Пример функции СУММЕСЛИ.

Выделяем ячейку G4 и ставим «=» . Пишем в ней СУММЕСЛИ и открываем скобку «(» . В качестве Диапазона, из которого будут выбираться значения, выделяем D2:D26 . В качестве Критерия, нужно выбрать одно из значений в ранее указанном Диапазоне. У нас это «мальчик» , поэтому выделяем ячейку D3 . Диапазон суммирования — это столбец, в котором указан «Средний бал» . Выделяем ячейки С2:С26 . Между аргументами ставьте «;» , в конце, закройте скобку «)» . Нажмите «Enter» .

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

Теперь давайте растянем формулу по столбцу. Конечно, можно также прописать ее и для девочек, но если у Вас много разных критериев — это займет немало времени. Закрепим диапазоны для столбцов С и D , чтобы при копировании формулы они не ползли вниз.

Выделяем ячейку G4 и в «Строке формул» после D2 , D26 , C2 , C26 нажимаем клавишу «F4» . В адресах на ячейки, появятся знаки «$» . Таким образом, мы делаем ссылки абсолютными. Чтобы растянуть формулу по столбцу, потяните за правый нижний угол ячейки, курсор изменит при этом вид на черный плюсик.

Если Вам интересно, как сделать ссылки в Эксель: абсолютные, относительные, на другой лист или книгу, прочтите статью по этой теме.

Дальше смотрим на «Строку формул» . В качестве аргументов для Диапазона и Диапазона суммирования берутся правильные значения: D2:D26 , C2:C26 . В качестве Критерия укажите адрес нужной ячейки. Поскольку, нужно посчитать средний балл для всех девочек, в столбце D выбираем адрес любой ячейки, в которой указано «девочка» : D4 .

Теперь рассмотрим аргументы для функции СУММЕСЛИМН в Excel.

=СУММЕСЛИМН(диапазон суммирования; диапазон условия1; условие1; диапазон условия2; условие2)

Диапазон суммирования — это столбец, значения из которого будут выбираться, если они соответствуют одному и второму заданному условию. В примере, это С2:С26 .

Диапазон условия1 — это первый диапазон для сравнения. Нужно выбрать пол ученика, поэтому диапазон D2:D26 .

Условие1 — нужно указать то значение, которое будет выбираться из Диапазона условий1. Нас интересуют мальчики, поэтому укажем ячейку D3 .

Диапазон условия2 — второй диапазон сравнения. Здесь будем выбирать класс, в котором учится ребенок. Соответственно укажем диапазон В2:В26 .

Условие2 — конкретное значение, которое нас интересует из Диапазона условий2. Сначала нужно будет выделить ячейку, в которой указан «8 класс» , затем «9 класс» .

Пример функции СУММЕСЛИМН.

Решать будем вторую задачу. Выделяем ячейку Н8 , ставим «=» , пишем СУММЕСЛИМН и открываем скобку «(» . В качестве Диапазона суммирования указываем столбец со «Средним балом» : С2:С26 . Диапазон условия1 — это пол ученика: D2:D26 , Условие1 — это мальчики: ячейка D3 . Диапазон условия2 — это класс: В2:В26 , Условие2 — 8 класс: ячейка В3 . Закройте скобку, нажмите «Enter» .

В «Строке формул» сразу сделайте абсолютные ссылки на столбцы: Класс, Средний бал, Мал/Дев.

Растягиваем формулу на ячейки Н8:Н11 . Теперь укажем правильные ячейки в качестве Условия1 и Условия2.

В ячейке Н9 считается средний балл для мальчиков, которые учатся в 9 классе. Поэтому, Условие1 для первого диапазона «мальчик» : D3 , Условие2 для второго диапазона «9» : В6 .

В ячейке Н10 считаем средний балл девочек, которые учатся в 8 классе. Условие1 — «девочка» , D4 , Условие2 — «8» , В3 .

В ячейке Н11 считаем средний балл девочек, которые учатся в 9 классе. Условие1 — «девочка» , D4 , Условие2 — «9» , В2 .

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

Давайте теперь проверим, чтобы результат был правильным. Посчитаем сумму «Среднего бала» для всех значений в исходной таблице, для Задачи 1 и для Задачи 2. Сумма должна быть одинаковой.

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

Вот на таких несложных примерах мы разобрались, как работает функция СУММЕСЛИ и функция СУММЕСЛИМН в Эксель.

Поделитесь статьёй с друзьями:

Как найти наибольшую сумму баллов по двум предметам в excel

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

A	B	C	D
1	Ученик	Район	Математика	Физика
2	Шамшин Владислав	Майский	65	79
3	Гришин Борис	Заречный	52	30
4	Огородников Николай	Подгорный	60	27
5	Богданов Виктор	Центральный	98	86

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

Выполните задание

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

1. Чему равна наибольшая сумма баллов по двум предметам среди учащихся Майского района? Ответ на этот вопрос запишите в ячейку G1 таблицы.

2. Сколько процентов от общего числа участников составили ученики Майского района? Ответ с точностью до одного знака после запятой запишите в ячейку G2 таблицы.

3. Постройте круговую диаграмму, отображающую соотношение количества участников из Майского, Кировского и Центрального районов. Левый верхний угол диаграммы разместите вблизи ячейки G6.

1. В столбце Е для каждого учащегося вычислим сумму баллов по двум предметам, если это ученик Майского района. Для ученика другого района ячейка будет содержать пустую строку. В ячейку E2 запишем формулу =ЕСЛИ(B2="Майский";C2+D2;"") Скопируем формулу во все ячейки диапазона Е3:Е1001. Для того чтобы найти наибольшую сумму, в ячейку G1 внесём формулу =MАКС(E2:E1001). Получаем: 194.

2. Для ответа на второй вопрос в ячейке H3, найдём количество учеников Майского района, принимавших участие

в тестировании, с помощью формулы: =СЧЁТЕСЛИ(B2:B1001;"Майский"). Выразим полученное значение в процентах от общего числа участников тестирования. Результат запишем в ячейку G2: =H3/1000*100. Получаем: 39,1.

3. В ячейку G2 вставим формулу =СЧЁТЕСЛИ(B2:B1001; "Майский"), в ячейку G3 вставим формулу =СЧЁТЕСЛИ(B2:B1001; "Кировский"), в ячейку G4 вставим формулу =СЧЁТЕСЛИ(B2:B1001; "Центральный"). Теперь построим по полученным значениям круговую диаграмму, подпишем сектора.