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

Задание
№ 7 (Excel)

Использование функций ЕСЛИ(), ИЛИ(), ВПР(), СРЗНАЧ(),
СЧЕТ(), СЧЕТЗ().

Расчет
стипендии с помощью приложения MS Excel.

По представленному образцу рассчитать стипендию в
зависимости от результатов сессии (среднего балла).

1.  Сначала необходимо повторить
оформление таблицы.

2.  Ввести:

§  Результаты экзаменов и зачетов
(диапазон ячеек B4:F7).

§  Мин. зарплату, предварительно выбрав
собственный формат числа 0,00р в текстовом окошке Тип после
выполнения команды Формат ► Ячейки ► Вкл.: Число.

§  Справа данные Для расчета
стипендии – Средний балл и Коэффициент.

3.  В строке Ср. балл для
расчета использовать функцию СРЗНАЧ() (в Категории Статистические).

4.  Для подсчета студентов, сдавших
экзамены, в строке Сдало использовать функцию СЧЕТ() (Категория: Статистические).

5.  Для подсчета студентов, сдавших
зачеты, в строке Сдало использовать функцию СЧЕТЗ() (Категория Статистические).

6. 
В столбце Средн.
балл для студента Петрова А. расчет среднего балла подсчитывается по
формуле (B4+C4+D4)/3, но только в том случае, когда
сданы все экзамены и все зачеты. Для реализации этих условий в ячейке G4 можно использовать, например,
такую формулу:

=ЕСЛИ(ИЛИ(B4*C4*В4=0;E4&F4<>”++”);0;(B4+C4+D4)/3)

Функции ЕСЛИ()
и ИЛИ() следует выбирать из Категории Логические. Эту формулу
распространить для остальных студентов.

7.  Величина начисляемой стипендии
(ячейка Н4) определяется с помощью функции вертикального поиска
значения  G4 в области J4:K8. Найденный коэффициент умножается
на величину минимальной зарплаты К1. В расчетной таблице (область J4:K8) выбирается средний балл, значение
которого ближе всего к значению среднего балла ячейки G4, а затем и соответствующий этому
выбранному значению коэффициент. Если же средний бал в столбе G равен 0, то коэффициент сразу
должен быть принят нулю. Такой алгоритм может быть реализован формулой:  =ЕСЛИ(G4=0;0;ВПР(G4;J$4:K$8;2;1)*K$1). Функция ВПР() выбирается
из Категории Ссылки и массивы.

8. 
Сумму начисленной
стипендии (Н9) вычислить с помощью функции СУММ().

Пояснения к п. 7. Значения аргументов функции
ВПР() означают следующее:

ВПР(<что искать>;<где
искать>;<откуда взять>;<как искать>) или

ВПР(<искомое значение>;<область
поиска>;<номер столбца извлечения>; <тип поиска>).

Если <тип поиска>=1, то поиск –
приблизительный, если <тип поиска>=0, поиск точный.

Расчет стипендии с помощью приложения MS Excel с использованием функций

Страницы работы

Содержание работы

Задание № 7 (Excel)

Использование функций ЕСЛИ(), ИЛИ(), ВПР(), СРЗНАЧ(), СЧЕТ(), СЧЕТЗ().

Расчет стипендии с помощью приложения MS Excel.

По представленному образцу рассчитать стипендию в зависимости от результатов сессии (среднего балла).

1. Сначала необходимо повторить оформление таблицы.

§ Результаты экзаменов и зачетов (диапазон ячеек B4:F7).

§ Мин. зарплату, предварительно выбрав собственный формат числа 0,00р в текстовом окошке Тип после выполнения команды Формат ► Ячейки ► Вкл.: Число.

§ Справа данные Для расчета стипендии – Средний балл и Коэффициент.

3. В строке Ср. балл для расчета использовать функцию СРЗНАЧ() (в Категории Статистические).

4. Для подсчета студентов, сдавших экзамены, в строке Сдало использовать функцию СЧЕТ() (Категория: Статистические).

5. Для подсчета студентов, сдавших зачеты, в строке Сдало использовать функцию СЧЕТЗ() (Категория Статистические).

6. В столбце Средн. балл для студента Петрова А. расчет среднего балла подсчитывается по формуле (B4+C4+D4)/3, но только в том случае, когда сданы все экзамены и все зачеты. Для реализации этих условий в ячейке G4 можно использовать, например, такую формулу:

Функции ЕСЛИ() и ИЛИ() следует выбирать из Категории Логические. Эту формулу распространить для остальных студентов.

7. Величина начисляемой стипендии (ячейка Н4) определяется с помощью функции вертикального поиска значения G4 в области J4:K8. Найденный коэффициент умножается на величину минимальной зарплаты К1. В расчетной таблице (область J4:K8) выбирается средний балл, значение которого ближе всего к значению среднего балла ячейки G4, а затем и соответствующий этому выбранному значению коэффициент. Если же средний бал в столбе G равен 0, то коэффициент сразу должен быть принят нулю. Такой алгоритм может быть реализован формулой: =ЕСЛИ(G4=0;0;ВПР(G4;J$4:K$8;2;1)*K$1). Функция ВПР() выбирается из Категории Ссылки и массивы.

8. Сумму начисленной стипендии (Н9) вычислить с помощью функции СУММ().

Пояснения к п. 7. Значения аргументов функции ВПР() означают следующее:

Если =1, то поиск – приблизительный, если =0, поиск точный.

Источник

Как посчитать количество отличников в excel

Как посчитать количество отличников в excel

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

Я не понимаю как сделать этот подсчет. Подскажите формулу пожалуйста! Заранее спасибо!
p.s. с диаграмой разобрался. нужно только сделать этот подсчет

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

Я не понимаю как сделать этот подсчет. Подскажите формулу пожалуйста! Заранее спасибо!
p.s. с диаграмой разобрался. нужно только сделать этот подсчет neketsh

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

Практическая работа по Excel «Стипендиальная ведомость»

Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.

Практическая работ а «Стипендиальная ведомость»

Стипендиальная ведомость факультета представляет собой ЭТ Excel , содержащую 5 рабочих листов. Соответственно Лист 1 – курс 1, Лист 2 – курс 2 и т. д.

На каждом рабочем листе составлены списки двух групп по 25 человек. Каждая таблица содержит следующие поля: №пп, ФИО, оценки по пяти предметам, средний балл, успеваемость, стипендия, стипендия с надбавкой хорошистам и отличникам, стипендия с надбавкой из дополнительного фонда.

Поля: №пп, ФИО, оценки по пяти предметам заполняются, остальные поля расчетные. (пример на рисунке)

Успеваемость

Успеваемость студентов определяется по следующей схеме: если средний балл 4,75 и выше, присваивается категория «отличник», если в промежутке от 3,75 до 4,75 – «хорошист», в промежутке от 2,5 до 3,75 – «троечник», если средний балл меньше 2,5 – «неуспевающий».

Для расчета успеваемости используется логическая функция ЕСЛИ. Данная функция включает в себя три Условия, три Истины и Ложь. Выглядит следующим образом:

Условие1 – средний балл >=4,75; ему соответствует Истина1 «отличник»;

Условие2 – средний балл >=3,75; ему соответствует Истина2 «хорошист»;

Условие3 – средний балл >=2,5; ему соответствует Истина3 «троечник»;

Ложью является значение «неуспевающий».

Стипендия

В условии задачи заявлено, что стипендия студентам, чей балл меньше 3,5 не начисляется. Стипендия остальным студентам составляет 460 руб.

Для назначения стипендии используется логическая функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:

Условие – средний балл <3,5;

Стипендия с надбавкой хорошистам и отличникам

Студентам, имеющим категорию успеваемости «хорошист» или «отличник», назначается надбавка в размере 10% от стипендии.

Для расчета стипендии с надбавкой используется логическая функция ЕСЛИ. Данная функция включает в себя два Условия, одну Истину и Ложь. Выглядит следующим образом:

Условие1 – категория «отличник»;

Условие2 – категория «хорошист»;

Истина – стипендия с надбавкой 10%;

Стипендия с доп.надбавкой

Всему факультету дополнительно выделили 50% стипендиального фонда. Необходимо распределить его между отличниками. Для выполнения данных расчетов необходимо:

1. Вставить дополнительный лист в рабочую книгу, назвать его «общий фонд».

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

3. Рассчитать первоначальный стипендиальный фонд. Для этого используется Консолидация данных, расположенная на ленте Данные. Откроется окно, в котором необходимо выбрать действие – Сумма, далее необходимо по очереди Добавить ссылки на ячейки, содержащие итоговые значения фондов по каждой группе.

После нажатия ОК в ячейке появится сумма стипендиальных фондов групп. Щелкнув слева от рабочего поля на символе +, можно увидеть список чисел, являющихся фондами этих групп.

4. Рассчитать дополнительный фонд, умножив общий фонд на 50%

5. Рассчитать количество отличников на факультете. Для этого необходимо воспользоваться функцией СЧЁТЕСЛИ, выбрав ее в категории статистические. На втором шаге Мастера функций указать диапазон ячеек первой таблицы, содержащей информацию о категории успеваемости. Критерий для отбора указать «отличник».

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

Таблица в режиме отображения формул выглядит следующим образом

6. Рассчитать общее количество отличников. Для этого вставить функцию СУММ внизу таблицы.

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

8. Рассчитать Стипендию с доп.надбавкой.

Для этого используется условная функция ЕСЛИ. Данная функция включает в себя одно Условие, Истину и Ложь. Выглядит следующим образом:

Условие – категория успеваемости студента — «отличник»;

Истина – стипендия с дополнительной надбавкой (ссылке на ячейку, содержащую доп.надбавку, присваивается абсолютное значение – клавишей F 4);

Ложь – стипендия без изменений.

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

Подсчет уникальных значений в Excel

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

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

И вот о чем мы сейчас поговорим:

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

• Уникальные значения – те, которые появляются в списке только один раз.
• Различные – это все, которые имеются в списке без учета повторов, то есть уникальные плюс первое вхождение повторяющихся.

Следующий рисунок иллюстрирует эту разницу:

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

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

Считаем уникальные значения в столбце.

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

Примечание. Это формула массива, поэтому обязательно нажмите Ctrl + Shift + Enter, чтобы корректно ввести её. Как только вы это сделаете, Excel автоматически заключит всё выражение в , как показано на скриншоте ниже. Ни в коем случае нельзя вводить фигурные скобки вручную, это не сработает.

В этом примере мы считаем уникальные имена в диапазоне A2: A10, поэтому наше выражение выглядит так:

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

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

Как работает формула подсчета уникальных значений?

Как видите, здесь используются 3 разные функции – СУММ, ЕСЛИ и СЧЁТЕСЛИ. Посмотрим, что делает каждая из них:

• Функция СЧЁТЕСЛИ считает, сколько раз каждое отдельное значение появляется в анализируемом диапазоне.

В этом примере СЧЁТЕСЛИ(A2:A10;A2:A10)возвращает массив .

• Функция ЕСЛИ оценивает каждый элемент в этом массиве, сохраняет все единицы (то есть, уникальные) и заменяет все остальные цифры нулями.

Итак, функция ЕСЛИ(СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0) преобразуется в ЕСЛИ() = 1,1,0).

И далее она превращается в массив чисел . Здесь 1 означает уникальное значение, а 0 – появляющееся более 1 раза.

• Наконец, функция СУММ складывает числа в этом итоговом массиве и выводит общее количество уникальных значений. Что нам и нужно.

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

Подсчет уникальных текстовых значений.

Если ваш список содержит как числа так и текст, и вы хотите посчитать только уникальные текстовые строки, добавьте функцию ЕТЕКСТ() в формулу массива, описанную выше:

Функция ЕТЕКСТ возвращает ИСТИНА, если исследуемое содержимое ячейки является текстом, и ЛОЖЬ в противоположном случае. Поскольку звездочка (*) в формулах массива работает как оператор И, то функция ЕСЛИ возвращает 1, только если рассматриваемое одновременно текстовое и уникальное, в противном случае получаем 0. И после того, как функция СУММ сложит все числа, вы получите количество уникальных текстовых значений в указанном диапазоне.

Не забывайте нажимать Ctrl + Shift + Enter , чтобы правильно ввести формулу массива, и вы получите результат, подобный этому:

Как вы можете видеть на скриншоте выше, мы получили общее количество уникальных текстовых значений, исключая пустые ячейки, числа, логические выражения ИСТИНА и ЛОЖЬ, а также ошибки.

Как сосчитать уникальные числовые значения.

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

Пример и результат вы видите на скриншоте чуть выше.

Примечание. Поскольку Microsoft Excel хранит дату и время как числа, они также участвуют в подсчёте.

Уникальные значения с учетом регистра.

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

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

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

Подсчет различных значений.

Используйте следующую универсальное выражение:

Помните, что это формула массива, поэтому вам следует нажать Ctrl + Shift + Enter , вместо обычного Enter.

Кроме того, вы можете использовать функцию СУММПРОИЗВ и записать формулу обычным способом:

=СУММПРОИЗВ(1 / СЧЁТЕСЛИ( диапазон ; диапазон ))

Например, чтобы сосчитать различные значения в диапазоне A2: A10, вы можете использовать выражение:

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

Этот метод подходит для текста, чисел, дат.

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

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

Тогда в расчёт попадёт и будет засчитана и пустая ячейка.

Как это работает?

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

После этого выполняется ряд операций деления, где единица делится на каждую цифру из этого массива. Это превращает все неуникальные значения в дробные числа, соответствующие количеству повторов. Например, если число или текст появляется в списке 2 раза, в массиве создаются 2 элемента равные 0,5 (1/2 = 0,5). А если появляется 3 раза, в массиве создаются 3 элемента 0,333333.

В нашем примере результатом вычисления выражения 1/СЧЁТЕСЛИ(A2:A10;A2:A10) является массив .

Пока не слишком понятно? Это потому, что мы еще не применили функцию СУММ / СУММПРОИЗВ. Когда одна из этих функций складывает числа в массиве, сумма всех дробных чисел для каждого отдельного элемента всегда дает 1, независимо от того, сколько раз он появлялся. И поскольку все уникальные элементы отображаются в массиве как единицы (1/1 = 1), окончательный результат представляет собой общее количество всех встречающихся значений.

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

Помните, что все приведенные ниже выражения являются формулами массива и требуют нажатия Ctrl + Shift + Enter .

Подсчет различных значений без учета пустых ячеек

Если столбец, в котором вы хотите совершить подсчет, может содержать пустые ячейки, вам следует в уже знакомую нам формулу массива добавить функцию ЕСЛИ. Она будет проверять ячейки на наличие пустот (основная формула Excel, описанная выше, в этом случае вернет ошибку #ДЕЛ/0):

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

Источник

Практическая работа Exel. Практическая работа Работа с электронными таблицами в среде ms excel Работа Настройка новой рабочей книги

Задание № 3. Подготовьте для каждой группы ведомость (рис. 3.4) назначения студентов на стипендию по результатам экзаменационной сессии. При выполнении данного задания потребуется произвести расчеты по более сложным, чем в предыдущем задании 2, формулам.
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ

Группа №________

Рис. 3.4. Форма стипендиальной ведомости
Ознакомьтесь с алгоритмом действий по технологии выполнения данного задания:

1. Загрузите экзаменационную ведомость.

2. На новом листе создайте ведомость стипендии (см. рис.3.4) и скопируйте в нее список группы из экзаменационной ведомости, отображенный на рис.3.9.

3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.

• если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
• если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
• если средний балл меньше 3, стипендия не выплачивается.

5. Подсчитайте сумму стипендиального фонда для всей группы.

6. Сверьте полученные общий вид таблицы, результаты и структуры формул с тем, что отображено на рис.3.5 и 3.6.

Рис. 3.5. Электронная таблица Ведомость назначения на стипендию в режиме отображения значений
Методика выполнения работы

• выполните команду Файл, Открыть;
• в диалоговом окне установите следующие параметры:

3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 5.

Для этого введите название таблицы — ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕН—
ДИЮ Группа № и названия столбцов — № п/п; Фамилия, имя, отчество; Стипендия,
задайте шрифт и тип выделения — полужирный.

4. Укажите размер минимальной стипендии в ячейке D3;

5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 3.5. Скорректируйте расхождение.

• установите курсор в ячейке С6;

Минимум
стипендии Итого стипендиальный фонд по группе

• введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:

  Категория: Статистические
  Имя: СРЗНАЧ

• щелкните по кнопке , появится панель ввода аргументов функции СРЗНАЧ;
• установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
• установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
• установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
• щелкните по кнопке ;
• в ячейке С6 появится значение, рассчитанное по формуле:

• установите курсор в ячейке С6;
• наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;
• нажмите левую кнопку мыши и протащите ее до конца этого столбца;
• просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке.

• установите курсор в ячейке D6;
• введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:

• щелкните по кнопке , появится панель ввода аргументов функции СЧЕТ;
• установите курсор в 1-й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
• установите курсор во 2-й строке (имя Значение 2), щелкните на названии листаЭкзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экза-
  мену;
• установите курсор в 3-й строке (имя Значение 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
• щелкните по кнопке ;
• в ячейке D6 появится значение, рассчитанное по формуле

9. Скопируйте формулу по всем ячейкам столбца D аналогично п.7.

10. Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула должна иметь следующий вид: =4,5;D6=3); =3;D6=3);$D$3;0)’>$D$3*1,5 ;ЕСЛИ(И(C6>=3;_D_6=3);$D$3;0))__Внимание!’>=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(C6>=3; D6=3);$D$3;0))

Внимание!

1. В структуре формулы имеются вложенные функции И(. ), ЕСЛИ(. ). Для ввода этих функций надо воспользоваться
кнопкой вызова функции, находящейся в строке
ввода под панелями.

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

3. В процессе набора формулы постоянно сравнивайте ее с выражением, которое приведено в этом пункте выше.

4. В числах для отделения целой части от дробной используется либо точка, либо запятая, что зависит от установок Excel.

5. Если после ввода формулы появится синтаксическая ошибка,
то следует проверить количество скобок, наличие разделите-
ля (точки с запятой), заменить в числе точку на запятую или
наоборот.

• установите курсор в ячейке Е6;
• введите команду Формулы, Вставить функцию и выберите в диалоговом окне параметры:

• щелкните по кнопке , появится панель ввода аргументов функции ЕСЛИ;
• курсор будет находиться в 1-й строке (имя — Логическое выражение) панели ввода аргументов функции;
• нажмите кнопку вызова функции в строке ввода, выберите категорию Другие функции и функцию И, нажмите кнопку ;
• появится второе окно ввода аргументов функции И, курсор автоматически будет установлен в строке Логическое значение1;
• щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение С6>=4,5
• установите курсор на второй строке Логическое значение 2 и аналогично сформируйте выражение, которое указывает необходимое количество сданных экзаменов (в данном примере — это число 3) D6=3
• щелкните по кнопке . В результате в строке ввода должно появиться выражение =ЕСЛИ(И(С6>=4,5;D6=3)
• щелкните мышью на строке ввода, появится первое окно ввода аргументов для функции ЕСЛИ;
• установите курсор во 2-й строке (Значение_ если_ истина), щелкните в ячейке D3 и нажмите клавишу . Появится символ $ перед именем столбца и номером строки. Введите выражение *1,5. В результате в этой строке будет выражение $D$3*1,5
• установите курсор в 3-й строке ( Значение_ если_ ложь) и по аналогичной технологии введите оставшуюся часть формулы ЕСЛИ(И(С6>=3;D6=3);$D$3;0)
• после окончания формирования формулы нажмите кнопку .

• вводите другие оценки в экзаменационные ведомости;
• измените минимальный размер стипендии.

8. Закройте рабочую книгу командой Файл, Закрыть.
Работа 3. Построение, редактирование и форматирование диаграмм
Задание № 1. Для таблицы на рис.3.7 постройте два вида диаграмм — внедренную на лист с исходными данными и на отдельном листе.

Для этого вам необходимо выполнить следующие действия:

• создать рабочую книгу;
• сохранить рабочую книгу;
• переименовать Лист1 на Успеваемость.

1. Создать таблицу в соответствии с рис.3.7 и вычислить средний балл по факультету по каждому предмету.

Рис. 3.7. Таблица успеваемости к заданию 1
3. Построить внедренную диаграмму, оформив согласно рис.3.8.

4. Построить диаграмму другого типа и разместить ее на отдельном листе.

Статья представляет собой подборку заданий для проведения итоговых уроков по изучению Excel. Задания снабжены пояснениями, которые с каждым упражнением становятся все менее подробными. Такой подход заставляет учащихся не просто выполнять предложенные упражнения, но и запоминать приемы работы, ведь иначе придется возвращаться к уже выполненной работе и разбираться с заданием заново; а также способствует простому и понятному для учащихся оцениванию их работы.

Упражнение 1. «Магазин»

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

Комментарии:

I. Создайте две таблицы: с исходными данными и результатами.

1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:

A	B	C	D	E	F
1	№	Наименование	Поставщик	Количество, л	Цена	Стоимость
2	1	Молоко	Поставщик № 1	76	43,00р.
3	2	Кефир	Поставщик № 2	40	26,00р.
4	3	Варенец	Поставщик № 3	86	34,00р.
5	4	Молоко	Поставщик № 3	45	26,00р.
6	5	Молоко	Поставщик № 4	87	24,00р.
7	6	Варенец	Поставщик № 4	34	41,00р.
8	7	Кефир	Поставщик № 4	67	32,00р.
9	8	Кефир	Поставщик № 3	45	39,00р.
10	9	Молоко	Поставщик № 2	43	35,00р.
11	10	Молоко	Поставщик № 5	32	33,00р.

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

II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке

A	B	C	D	E	F
1	№	Наименование	Поставщик	Количество, л	Цена	Стоимость
2	1	Молоко	Поставщик № 1	76	43,00р.	3 268,00р.
3	2	Кефир	Поставщик № 2	40	26,00р.	1 040,00р.
4	3	Варенец	Поставщик № 3	86	34,00р.	2 924,00р.
5	4	Молоко	Поставщик № 3	45	26,00р.	1 170,00р.
6	5	Молоко	Поставщик № 4	87	24,00р.	2 088,00р.
7	6	Варенец	Поставщик № 4	34	41,00р.	1 394,00р.
8	7	Кефир	Поставщик № 4	67	32,00р.	2 144,00р.
9	8	Кефир	Поставщик № 3	45	39,00р.	1 755,00р.
10	9	Молоко	Поставщик № 2	43	35,00р.	1 505,00р.
11	10	Молоко	Поставщик № 5	32	33,00р.	1 056,00р.

2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:

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

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5
16	2	Кефир	3
17	3	Варенец	2

b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:

где F2:F11 — диапазон стоимости продукта из первой таблицы.

A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5	9 087,00р.
16	2	Кефир	3	4 939,00р.
17	3	Варенец	2	4 318,00р.

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5	9 087,00р.	32,20р.
16	2	Кефир	3	4 939,00р.	32,33р.
17	3	Варенец	2	4 318,00р.	37,50р.

Замечания:

Данное упражнение может быть дополнено следующими заданиями (и не только ими):

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

Упражнение 2. «Студенческие стипендии» [1]

• стипендия не назначается, если среди оценок есть хотя бы одна двойка;
• 3,0 < средний балл < 3,5 — 1000р.
• 3,5 < средний балл < 4,0 — 1200р.
• 4,0 < средний балл < 4,5 — 1500р.
• 4,5 < средний балл < 5,0 — 1800р.
• средний балл = 5,0 — 2000р.

Определить общую сумму назначенных стипендий.

Замечания:

Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.

• Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:

№	Фамилия	Предметы	Средний балл	Стипендия
Матем.	Физика	Информ.	Химия	История
1	Алексеев	5	3	2	3	3
2	Гаврилов	4	4	3	3	3
3	Зайцев	3	3	3	3	3
4	Иванов	4	3	5	3	3
5	Краснов	5	4	4	4	4
6	Кузнецов	4	5	5	5	5
7	Петров	5	5	5	5	5
8	Сидоров	4	3	3	3	5
9	Смирнов	4	4	4	4	4
10	Солдатов	5	4	5	2	3

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

№	Фамилия	Предметы	Средний балл	Стипендия
Матем.	Физика	Информ.	Химия	История
1	Алексеев	5	3	2	3	3	3,2	0р.
2	Гаврилов	4	4	3	3	3	3,4	1 000р.
3	Зайцев	3	3	3	3	3	3	1 000р.
4	Иванов	4	3	5	3	3	3,6	1 200р.
5	Краснов	5	4	4	4	4	4,2	1 500р.
6	Кузнецов	4	5	5	5	5	4,8	1 800р.
7	Петров	5	5	5	5	5	5	2 000р.
8	Сидоров	4	3	3	3	5	3,6	1 200р.
9	Смирнов	4	4	4	4	4	4	1 500р.
10	Солдатов	5	4	5	2	3	3,8	0р.
Итого:	11 200р.

Упражнение 3. «Завод железобетонных изделий»

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
b) Столбец «Количество блоков» заполните произвольными данными.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.

Как посчитать стипендию в excel

Расчет стипендии с помощью приложения MS Excel с использованием функций

Страницы работы

Содержание работы

Задание № 7 (Excel)

Использование функций ЕСЛИ(), ИЛИ(), ВПР(), СРЗНАЧ(), СЧЕТ(), СЧЕТЗ().

Расчет стипендии с помощью приложения MS Excel.

По представленному образцу рассчитать стипендию в зависимости от результатов сессии (среднего балла).

1. Сначала необходимо повторить оформление таблицы.

§ Результаты экзаменов и зачетов (диапазон ячеек B4:F7).

§ Мин. зарплату, предварительно выбрав собственный формат числа 0,00р в текстовом окошке Тип после выполнения команды Формат ► Ячейки ► Вкл.: Число.

§ Справа данные Для расчета стипендии – Средний балл и Коэффициент.

3. В строке Ср. балл для расчета использовать функцию СРЗНАЧ() (в Категории Статистические).

4. Для подсчета студентов, сдавших экзамены, в строке Сдало использовать функцию СЧЕТ() (Категория: Статистические).

5. Для подсчета студентов, сдавших зачеты, в строке Сдало использовать функцию СЧЕТЗ() (Категория Статистические).

6. В столбце Средн. балл для студента Петрова А. расчет среднего балла подсчитывается по формуле (B4+C4+D4)/3, но только в том случае, когда сданы все экзамены и все зачеты. Для реализации этих условий в ячейке G4 можно использовать, например, такую формулу:

=ЕСЛИ(ИЛИ(B4*C4*В4=0;E4&F4<>”++”);0;(B4+C4+D4)/3)

Функции ЕСЛИ() и ИЛИ() следует выбирать из Категории Логические. Эту формулу распространить для остальных студентов.

7. Величина начисляемой стипендии (ячейка Н4) определяется с помощью функции вертикального поиска значения G4 в области J4:K8. Найденный коэффициент умножается на величину минимальной зарплаты К1. В расчетной таблице (область J4:K8) выбирается средний балл, значение которого ближе всего к значению среднего балла ячейки G4, а затем и соответствующий этому выбранному значению коэффициент. Если же средний бал в столбе G равен 0, то коэффициент сразу должен быть принят нулю. Такой алгоритм может быть реализован формулой: =ЕСЛИ(G4=0;0;ВПР(G4;J$4:K$8;2;1)*K$1). Функция ВПР() выбирается из Категории Ссылки и массивы.

8. Сумму начисленной стипендии (Н9) вычислить с помощью функции СУММ().

Пояснения к п. 7. Значения аргументов функции ВПР() означают следующее:

ВПР( ; ; ; ) или

Если =1, то поиск – приблизительный, если =0, поиск точный.

Расчет стипендии в зависимости от оценок

Здравствуйте. Помогите, пожалуйста, гуманитарию решить задачку.

Смысл в чем: посчитать размер стипендии исходя из условий.
 если за все экзамены получены оценки 5, то размер стипендии 2550 р.,
 если за экзамены получены оценки 4 или 5, то стипендия составляет 1700 р.,
 если среди оценок за экзамены есть хотя бы одна 3, то стипендия не начисляется.

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

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

Excel_lab7.xlsx (17.3 Кб, 71 просмотров)

Исходя из массива оценок по предметам найти студентов, имеющих право на получение стипендии
Есть задача — Написать программу, используя массив. Описание задачи: Имеем оценки m студентов по n.

Расчет стипендии
Здравствуйте такая проблема возникла! Дипломку пишу, вот такая задача появилась рассчитать.

Расчет стипендии в Excel
Помогите решить! Извините если не правильно оформил тему. Расчет стипендии. Создать таблицу.

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