Упражнение № 11 Создайте таблицу и выполните расчеты
| A | B | C | D | E | F | G |
| 1 | Январь | февраль | март | Всего | Среднее за квартал | % март к февралю |
| 2 Затраты | 300 | 250 | 430 | |||
| 3 Приход | 800 | 550 | 730 | |||
| 4 Прибыль | =В3-В2 | |||||
| 5 Налог на прибыль | В4*20% |
Порядок расчета:
1. Подсчитайте Прибыль и Налог на прибыль.
2. Используя функцию СУММ подчситайте значение “Всего”
3. С помощью функции СРЗНАЧ рассчитайте “Среднее за квартал”
4. Столбец G подсчитывается по формуле: D/C*100%
Требуется подсчитать средние затраты за январь, февраль, март.
1. В ячейке F1 запишите «Среднее за квартал».
2. Выделите ячейку F2, затем на Панели инструментов щелкните по кнопке Мастер функций (Function Wizard).
3. Откроется диалоговое окно Мастера функций.
4. В списке Категория (Function Category) статистические (Statistical).
5. В окне функция (Function) появится список статистических функций.
6. В этом окне надо выделить строку СРЗНАЧ (AVERAGE) и щелкнуть по кнопке ДАЛЕЕ (NEXT).
7. Откроется окно Мастер функций -шаг 2 из 5 (Function Wizard - step 2 of 5).
8. Поместить указатель мыши на заголовок диалогового окна, нажать левую кнопку мыши и перетащить окно к нижней границе экрана.
9. Щелкните в поле Число 1 (Number 1).
10. Выделите диапазон ячеек от А2 до D2.
В процессе выделения диапазона в поле диалогового окна «Значение» (( VALUE ) указывается средняя величина содержимого выделенных ячеек. В поле Число1 (Number 1) появились адреса A2:D2.
|
|
|
11. Щелкните по кнопке Готово (Finish). В ячейке Е2 появится среднее значение.
12. Выделите ячейку Е2, установите в ней указатель мыши в правый нижний угол и протяните при нажатой левой кнопке мыши в следующую ячейку Е3.
13. Отпустите клавишу мыши. В ячейке Е3 будет средний приход.
14. Остальные вычисления проведите, используя полученные знания в предыдущих упражнениях
Принцип работы с функцией AVERAGE распространяется на все остальные функции.
Математические функции.
В EXCEL имеется целый ряд математических функций, позволяющих выполнять различные специализированные вычисления.
Функция =СУММ (число). Аргумент числа может содержать до 30 элементов, каждый из которых может быть или const или ссылкой на ячейку. Функция =СУММ ( ) соответствует автосуммированию (å). При выполнении СУММ ( ) можно назначить имя диапазону ячеек. Ячейки могут быть и не смежными (чтобы выделить несмежные ячейки, надо, удерживая CTRL щелкнуть мышкой по ячейкам). Чтобы назначить имя диапазону ячеек, надо:
1. Выделить диапазон ячеек
2. Выполнить Вставка®Имя®Определить.
3. Назначить имя диапазону.
|
|
|
4. В дальнейшем в формуле можно использовать это имя.
Задача. Требуется определить итоги лицензирования а/м.
| А | В | С | D | E | F | |
| 1 | Вид собственности | Легковые а/м | Автобусы | Грузовые а/м | опасные грузы | Специальные |
| 2 | Федеральные | |||||
| 3 | Муниципальные | |||||
| 4 | Частная | |||||
| 5 | С иностр. кан. | |||||
| 6 | Итого |
1. Определим имена
2. В В6: =СУММ (легковые).
Функция =СУММ ( ) достачно гибкая, но при добавлении ячейки в конец (начало) диапазона могут возникнуть трудности.
Например:
1 100
2 200
3 100
4 100
5 200
Ввести
7 =СУММ (А1:А5) ® 700
Вставим строку 6 и внесем в нее число. Например, 100. Результат не изменится. Надо в ячейку А7 ввести =СУММ (А1:А6).
ABS - возвращает абсолютное значение чисел. Имеет синтаксис =ABS (число).
Например:
В А1 записано - 200, в ячейку А2 введем =ABS (A1). Возвращает значение 200.
Функция ЗНАК возвращает знак числа.
= ЗНАК (число).
Если число больше 0, функция ЗНАК возвращает 1.
Если число меньше , то -1.
Если число =0, то Æ.
Например:
| A | B | C | D | E | |
| Остаток на нач. | Приход | Расход | Остаток | ||
| 1 | АО "РАССВЕТ" | 100 | 200 | 350 |
В ячейке Е1 записано =ЗНАК (СУММ(А1:D1)). Возвращает -1.
|
|
|
Функция ОКРУГЛ.
=ОКРУГЛ (число; число_цифра)
Аргумент число - это число, ссылка на ячейку. Аргумент число цифр может быть положительным или отрицательным. Указывает на то количество цифр, до которого округляется.
Формула. Результат.
=ОКРУГЛ (897,457; - 2) 900
=ОКРУГЛ (897,457; - 1) 900
=ОКРУГЛ (897,457; 0) 897
=ОКРУГЛ (897,457; 1) 897,5
Функция ЧЕТН округляет число до ближайшего нечетного числа.
ЧЕТН (число) ; нечетн. (число)
ЧЕТН (5) 6
НЕЧЕТН (7,2) 9
Функция целое.
=целое (число)
=целое (185,37) ® 185.
=целое (200,99) ® 200.
Функция корень.
=Корень (число). Возвращает значение квадратного корня.
Функция остаток.
=ОСТАТОК (число; делитель)
n возвращает остаток от деления.
=остаток (33;4) ® 1.
Функция степень.
=степень (число; степень).
log 10 (число) - десятичный логоритм.
log (число; основание).
log (12; 2)
log2 12.
|
|
|
LN (число) - натуральный логоритм.
Текстовые функции.
Текстовые функции позволяют преобразовывать текстовые значения в числовые, числовые значения в строки символов, а также выполняют различные операции над текстовыми строками.
ЗНАЧЕН (текст).
Аргумент ТЕКСТ может быть строкой, заключенной в двойные кавычки или ячейкой в которой содержится текст. Преобразуемые текстовые значения могут быть записаны в любом допустимом формате. Например:
=ЗНАЧЕН (А1)
А1 содержит "0025"
Ответ: 25.
Функция РУБМ.
=ДЛСТР (текст).
Аргумент текст может быть как текстовым, так и числовым. Сама функция возврвщает длину строки или числа.
Задача. Определить длину текста в ячейке А1:.
"Функции EXCEL".
=ДЛСТР (А1)
13
Или в ячейке А1 записано число 156.
=ДЛСТР (А1)
3
Ячейка, на на которую ссылается функция ДЛСТР может содержать другие текстовые функции. В ячейку А1 записано выражение:
=ПОВТОР ("*В"; 25)
=ДЛСТР (А1)
50.
=СЖПРОБЕЛЫ (текст) - удаляет начальные и конечные пробелы из строки и между словами оставляет по одному пробелу. В А1 записано #### Тарасов ##### Алексей ###### Петрович. Записать =СЖПРОБЕЛЫ (А1).
=СОВПАД (текст 1; текст 2) ВА1: Адрес:, 355000, 26, СТАВРОПОЛЬ Г, ВАСИЛЬЕВА УЛ, 45 В С3 записано.
Адрес: , 355000, 26, СТАВРОПОЛЬ, УЛ ВАСИЛЬЕВА, 45.
СОВПАД (А1;С3). Если не совпадет, то ложь.
=ПРОПИСН (текст)
В А1 записано: с: статистические функции.
В А2 записать:
СТАТИСТИЧЕСК ФУНК
=ПРОПИСН (А1)
=СТРОЧНАЯ
=СТРОЧН (А1) - статистичекие функции.
ПРОПНАЧ (текст)
Статистичекие функции
=ПРОПНАЧ (А1).
Работа с элементами строк.
А1 А2 А3
Иванов Петр Иванович
Надо записать в А2
в А4: Иванов П. И.
ЛЕВСИМВ (текст; число знаков)
=ПРАВСИМВ (текст; количество символов)
=ПС (текст; начальная позиция; количество символов)
В А1 запишем 20-11-35, 20 часов 11 минут 35 секунд
Выделить минуты (А1, 4, 2). Ответ: 11
=Сцепить – эквивалент &
Выполнить двумя способами: Иванов Петр Иванович.
Логические функции.
=Если (Условие, выраж_если_истина;выражение_если_ложь)
Задача. Имеем свободную таблицу успеваемости, в которой надо обозначить пометку о назначении стипендии.
| А | В | С | D | E | |
| 1 | ФИО | экз. 1 | экз. 2 | экз. 3 | отметка о стипенд. |
| 2 | Иванов | 4 | 3 | 2 | |
| 3 | Косов | 4 | 4 | 4 | |
| 4 | Симонов | 5 | 5 | 5 |
В ячейку Е2 записать Если (и (В2 >3; С2 > 3; D2 > 3); "да"; "нет").
Вложенные функции.
Если в ячейке А1 записана сумма вычетов из заработной платы для определения налогооблагаемой суммы. Этот вычет может быть равен одному минимальному зароботку, двум, трем пяти. Каждому из них присвавается код.: 21, 22, 23, 25.
=Если (А1=1; "21"; если (А1=2; "22"; если А1=3; "23"; если А1=5; "25")))).
Допускается до 7 вложений.
Функция ДАТ
1. Создание упорядоченного ряда. Правка®Содать®Пргрессия®окно прогрессия®Установить флажки:
n по строкам столбцами секция тип дата.
n В секции единицы выбрать день месяц, год.
Ввести с клавиатуры дату: например 25.04.99 и скопировать.
2. Ввод дат с использованием автозаполнения
n вводим начальную дату и за ней следующую
3. Форматирование дат и времени:
Формат®Ячейка®вкладка число®Дата®Тип.
4. Арифмитические операции с датами:
а) d + k - количество дней - новая
В ячейку А1: 1.03.99
В2:=1.03.99 + 100® 9.06.99.
б) D2-D7 - количество дней.
В ячейке А5 записано: 27.07.99
А6: 31.12.99.
В ячейке В5: =А6-А5®157.
в) =Сегодня ( ) - возвращаеттекущее время.
г) ТДАТА ( ) - возвращает текущую дату и время.
д) ДЕНЬНЕД (десятичная дата, тип)
тип = 1, воскр. = 1 день
тип = 2 - счет с понедельника
е) месяц (дата)
ж) год (дата)
е) день(дата)
ЗАДАНИЕ. Выполните расчеты в таблице, используя имена.
1. Заполните таблицу как это показано на рис 4.
|
|
рис 4
2. Присвойте следующие имена
- Минимум
- Начислено
- Налог
- Проф
- Пенс
- Удержано
- Выдано
3. Используя присвоенные имена, формулы и формулы массива, выполните расчеты
4. Алгоритм расчета :
- В ячейке G 2 записать 83,49 - это текущий необлагаемый налогом минимум
- В столбце Начислено записать исходные суммы начислений
- Проф. взносы подсчитать как 1% от Начислено.
- Пенс. Отчисления подсчитать как 1% от Начислено.
- Налог подсчитать как 12% от (Начислено - Пенс - минимум).
- Выполнить функцию округления результата до целых
- Удержано подсчитать как Пенс. + Проф. +Налог.
- Выдано подсчитать как Начислено - Удержано.
- Подсчитайте ИТОГО.
Дата добавления: 2020-01-07; просмотров: 187; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!