Как найти зарплату в excel
Цель занятия: Применение относительной и абсолютной адресаций для финансовый расчетов. Сортировка, условное форматирование и копирование созданных таблиц. Работа с листами электронной книги.
Задание: Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчёты, форматирование, сортировку и защиту данных. Исходные данные представлены на рис.2.5, результаты работы – на рис. 2.6., 2.7.
Порядок работы:
- Запустите редактор электронных таблиц MS EXCEL и создайте новую книгу.
- Создайте таблицу расчета заработной платы по образцу (см. рис. 2.5.). Введите исходные данные – Табельный номер, ФИО и Оклад. % премии = 27%, % Удержания = 13%.
Примечание. Выделите отдельные ячейки для значений % премии (D4) и % Удержания (F4).
Рис. 2.5. Исходные данные для задания
Произведите расчеты во всех столбцах таблицы.
При расчете Премии используется формула Премия = Оклад х х % Премии, в ячейке D5 наберите формулу =$D$4*C5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.
Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчётной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоминать, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).
Формула для расчета «Всего начислено»:
Всего начислено = Оклад + Премия;
При расчете Удержания используется формула:
Удержание = Всего начислено х % Удержания.
Для этого в ячейке F5 наберите формулу = $F$4*E5.
Формула для расчета столбца «К выдаче»:
К выдаче = Всего начислено – Удержания.
- Рассчитайте итоги по столбцам, а так же максимальный и минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/ категория – Статистические функции).
- Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Результаты работы представлены на рис. 2.6.
Рис. 2.6. Итоговый вид таблицы расчета заработной платы за октябрь
- Скопируйте содержимое листа «Зарплата октябрь» на новый лист.
- Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы, измените значение премии на 32%. Убедитесь, что программа произвела перерасчет формул.
- Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле:
Доплата = Оклад х % Доплаты. Значение доплаты примите равным 5%.
- Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата.
- Проведите условное форматирование значений колонки «К выдаче». Установите формат значений между 7000 и 10000 – зеленым текстом шрифта; меньше 7000 – красным; больше или равно 10000 – синим цветов шрифта (Формат/Условное форматирование).
- Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы – без итогов, выберите меню Данные/Сортировка, сортировать по – Столбец В).
- Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания.
Рис. 2.7. Конечный вид зарплаты за ноябрь
- Защитите лист «Зарплата за ноябрь» от изменений (Сервис/Защита/Защитить лист). Задайте пароль на лист, сделайте подтверждение пароля.
Дополнительные задания:
Задание 1. Сделать примечания к двум-трем ячейкам.
Задание 2. Выполнить условное форматирование оклада и премии за ноябрь месяц:
До 2000 р. – желтым цветом заливки;
От 2000 до 10000 р. – зеленым цветом шрифта;
Свыше 10000 р. — малиновым цветом заливки, белым цветом шрифта.
- Защитить лист зарплаты за октябрь от изменений. Проверьте защиту
- Сохраните файл зарплата с произведенными изменениями.
Как рассчитать зарплату в экселе? Примеры.
Рассмотрим простой пример расчета заработной платы. В нашем примере заработная плата состоит из двух составляющих: оклада и премии. Размер премии зависит от процента выполненного плана, если план выполнен на 50%, то премия ноль, если до 80% — то премия 50%, если больше 81%, то премия 100%. Сначала нарисуем таблицу, состоящую из семи столбцов:
- ФИО – ФИО сотрудников;
- Оклад – Размер оклада сотрудника;
- Максимальная премия – Размер максимальной премии каждого сотрудника;
- % выполнения плана – какой процент плана выполнил сотрудник за текущий период.
- Начисленная премия – размер начисленной премии, в зависимости от выполненного плана;
- НДФЛ – размер подоходного налога начисляемый на оклад и премию;
- Общие начисления – начисления, состоящие из трех компонентов: оклад + премия + НДФЛ.
В данной таблице мы уже заполнили первые четыре столбца и остается сделать расчет оставшихся трех столбцов. Рассмотрим подробно, как это сделать:
Второй шаг. Теперь нужно посчитать налог, для этого сложим размер премии и оклада, а потом разделим на 87% и затем умножим на 13%. В итоге сначала напишем в ячейке «F3» следующую формулу: =ОКРУГЛ((B3+E3)/87%*13%;). В этой формуле используем функцию ОКРУГЛ, так как налоги платят до целого рубля. Копируем формулу на оставшиеся ячейки.
Третий шаг. Осталось посчитать общую сумму начисления, так как по правилам бухгалтерии сначала начисляет общая сума зарплаты, а потом с неё удерживается налоги. Для этого просто сложим значения из столбцов 2, 5, 6, написав в ячейке «G3» формулу: =B3+E3+F3. Которую копируем на оставшиеся ячейки и получаем вот такой простой пример расчета заработной платы.
Как найти зарплату в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Расчет среднего значения
В Excel существует несколько способов найти среднее для набора чисел. Например, можно воспользоваться функцией для расчета простого среднего, взвешенного среднего или среднего, исключающего определенные значения.
Чтобы научиться вычислять средние значения, используйте предоставленные образцы данных и описанные ниже процедуры.
Копирование примера данных
Чтобы лучше понять описываемые действия, скопируйте пример данных в ячейку A1 пустого листа.
- Создайте пустую книгу или лист.
- Выделите приведенный ниже образец данных.
Примечание: Не выделяйте заголовки строк или столбцов (1, 2, 3. A, B, C. ) при копировании данных примера на пустой лист.
Расчет простого среднего значения
- Выделите ячейки с A2 по A7 (значения в столбце «Качество изделия»).
- На вкладке Формулы щелкните стрелку рядом с кнопкой Автоумма и выберитесреднее значение . Результат — 8,166666667.
Расчет среднего для несмежных ячеек
- Выберите ячейку, в которой должно отображаться среднее значение, например ячейку A8, которая находится слева ячейки с текстом «Среднее качество изделия» в примере данных.
- На вкладке Формулы щелкните стрелку рядом с кнопкой Автоумма кнопку Среднее инажмите клавишу RETURN.
- Щелкните ячейку, которая содержит только что найденное среднее значение (ячейка A8 в этом примере). Если используется образец данных, формула отображается в строка формул, =СС00(A2:A7).
- В строке формул выделите содержимое между скобками (при использовании примера данных — A2:A7).
- Удерживая нажатой клавишу , щелкните ячейки, для чего нужно вычесть среднее значение, и нажмите клавишу RETURN. Например, выберите A2, A4 и A7 и нажмите клавишу RETURN. Выделенная ссылка на диапазон в функции СРЗНАЧ заменится ссылками на выделенные ячейки. В приведенном примере результат будет равен 8.
Расчет среднего взвешенного значения
В приведенном ниже примере рассчитывается средняя цена за изделие по всем заказам, каждый из которых содержит различное количество изделий по разной цене.
- Выделите ячейку A9, расположенную слева от ячейки с текстом «Средняя цена изделия».
- На вкладке Формулы нажмите кнопку Вставить функцию, чтобы открыть панель Построитель формул.
- В списке построителя формул дважды щелкните функцию СУММПРОИЗВ.
Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СУММПРОИЗВ.
Расчет среднего, исключающего определенные значения
Вы можете создать формулу, которая исключает определенные значения. В приведенном ниже примере создается формула для расчета среднего качества всех изделий, у которых оценка качества выше 5.
- Выделите ячейку A10, расположенную слева от ячейки с текстом «Среднее качество всех изделий с оценкой качества выше 5».
- На вкладке Формулы нажмите кнопку Вставить функцию, чтобы открыть панель Построитель формул.
- В списке построителя формул дважды щелкните функцию СРЗНАЧЕСЛИ.
Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СРЗНАЧЕСЛИ.
Совет: Чтобы использовать функцию СРЗНАЧЕСЛИ для расчета среднего без нулевых значений, введите выражение «<>0″ в поле условие.
Чтобы научиться вычислять средние значения, используйте предоставленные образцы данных и описанные ниже процедуры.
Копирование примера данных
Чтобы лучше понять описываемые действия, скопируйте пример данных в ячейку A1 пустого листа.
- Создайте пустую книгу или лист.
- Выделите приведенный ниже образец данных.
Примечание: Не выделяйте заголовки строк или столбцов (1, 2, 3. A, B, C. ) при копировании данных примера на пустой лист.
Расчет простого среднего значения
Рассчитаем среднее качество изделий двумя разными способами. Первый способ позволяет быстро узнать среднее значение, не вводя формулу. Второй способ предполагает использование функции «Автосумма» для расчета среднего значения и позволяет вывести его на листе.
Быстрый расчет среднего
- Выделите ячейки с A2 по A7 (значения в столбце «Качество изделия»).
- На строка состояния щелкните стрелку всплывающее меню (если вы используете образец данных, вероятно, область содержит текст Sum=49),а затем выберите среднее . Результат — 8,166666667.
Примечание: Если строка состояния не отображается, в меню Вид выберите пункт Строка состояния.
Расчет среднего с отображением на листе
- Выберите ячейку, в которой должно отображаться среднее значение, например ячейку A8, которая находится слева ячейки с текстом «Среднее качество изделия» в примере данных.
- На панели инструментов Стандартная под названием книги щелкните стрелку рядом с кнопкой кнопку Среднее и нажмите клавишу RETURN. Результат составляет 8,166666667 — это средняя оценка качества всех изделий.
Совет: Если вы работаете с данными, в которые перечислены числа в строке, выберите первую пустую ячейку в конце строки, а затем щелкните стрелку рядом с кнопкой .
Расчет среднего для несмежных ячеек
Существует два способа расчета среднего для ячеек, которые не следуют одна за другой. Первый способ позволяет быстро узнать среднее значение, не вводя формулу. Второй способ предполагает использование функции СРЗНАЧ для расчета среднего значения и позволяет вывести его на листе.
Быстрый расчет среднего
-
Выделите ячейки, для которых вы хотите найти среднее значение. Например, выделите ячейки A2, A4 и A7.
Совет: Чтобы выбрать несмещные ячейки, щелкните их, удерживая клавишу.
Примечание: Если строка состояния не отображается, в меню Вид выберите пункт Строка состояния.
Расчет среднего с отображением на листе
- Выберите ячейку, в которой должно отображаться среднее значение, например ячейку A8, которая находится слева ячейки с текстом «Среднее качество изделия» в примере данных.
- На панели инструментов Стандартная под названием книги щелкните стрелку рядом с кнопкой кнопку Среднее и нажмите клавишу RETURN.
- Щелкните ячейку, которая содержит только что найденное среднее значение (ячейка A8 в этом примере). Если используется образец данных, формула отображается в строка формул, =СС00(A2:A7).
- В строке формул выделите содержимое между скобками (при использовании примера данных — A2:A7).
- Удерживая нажатой клавишу , щелкните ячейки, для чего нужно вычесть среднее значение, и нажмите клавишу RETURN. Например, выберите A2, A4 и A7 и нажмите клавишу RETURN. Выделенная ссылка на диапазон в функции СРЗНАЧ заменится ссылками на выделенные ячейки. В приведенном примере результат будет равен 8.
Расчет среднего взвешенного значения
В приведенном ниже примере рассчитывается средняя цена за изделие по всем заказам, каждый из которых содержит различное количество изделий по разной цене.
- Выделите ячейку A9, расположенную слева от ячейки с текстом «Средняя цена изделия».
- На вкладке Формулы в разделе Функция выберите пункт Построитель формул.
- В списке построителя формул дважды щелкните функцию СУММПРОИЗВ.
Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СУММПРОИЗВ.
Расчет среднего, исключающего определенные значения
Вы можете создать формулу, которая исключает определенные значения. В приведенном ниже примере создается формула для расчета среднего качества всех изделий, у которых оценка качества выше 5.
- Выделите ячейку A10, расположенную слева от ячейки с текстом «Среднее качество всех изделий с оценкой качества выше 5».
- На вкладке Формулы в разделе Функция выберите пункт Построитель формул.
- В списке построителя формул дважды щелкните функцию СРЗНАЧЕСЛИ.
Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СРЗНАЧЕСЛИ.
Совет: Чтобы использовать функцию СРЗНАЧЕСЛИ для расчета среднего без нулевых значений, введите выражение «<>0″ в поле условие.