Составление формул и работа с ячейками
Требуется подсчитать средние затраты за январь, февраль, март.
В ячейке F1 запишите «Среднее за квартал».
Выделите ячейку F2, затем на Панели инструментов щелкните по кнопке Мастер функций (Function Wizard).
Откроется диалоговое окно Мастера функций.
В списке Категория (Function Category) статистические (Statistical).
В окне функция (Function) появится список статистических функций.
В этом окне надо выделить строку СРЗНАЧ (AVERAGE) и щелкнуть по кнопке ДАЛЕЕ (NEXT).
Откроется окно Мастер функций -шаг 2 из 5 (Function Wizard -step 2 of 5).
Поместить указатель мыши на заголовок диалогового окна, нажать левую кнопку мыши и перетащить окно к нижней границе экрана.
Щелкните в поле Число 1 (Number 1).
Выделите диапазон ячеек от А2 до D2.
В процессе выделения диапазона в поле диалогового окна «Значение» ((VALUE) указывается средняя величина содержимого выделенных ячеек. В поле Число1 (Number 1) появились адреса A2:D2.
Щелкните по кнопке Готово (Finish). В ячейке Е2 появится среднее значение.
Выделите ячейку Е2, установите в ней указатель мыши в правый нижний угол и протяните при нажатой левой кнопке мыши в следующую ячейку Е3.
Отпустите клавишу мыши. В ячейке Е3 будет средний приход.
Остальные вычисления проведите, используя полученные знания в предыдущих упражнениях
Принцип работы с функцией AVERAGE распространяется на все остальные функции.
Математические функции.
В EXCEL имеется целый ряд математических функций, позволяющих выполнять различные специализированные вычисления.
Функция =СУММ (число). Аргумент числа может содержать до 30 элементов, каждый из которых может быть или const или ссылкой на ячейку. Функция =СУММ ( ) соответствует автосуммированию (е). При выполнении СУММ ( ) можно назначить имя диапазону ячеек. Ячейки могут быть и не смежными (чтобы выделить несмежные ячейки, надо, удерживая CTRL щелкнуть мышкой по ячейкам). Чтобы назначить имя диапазону ячеек, надо:
Выделить диапазон ячеек
Выполнить Вставка®Имя®Определить.
Назначить имя диапазону.
В дальнейшем в формуле можно использовать это имя.
Задача. Требуется определить итоги лицензирования а/м.
|
А |
В |
С |
D |
E |
F |
|
|
1 |
Вид собственности |
Легковые а/м |
Автобусы |
Грузовые а/м |
опасные грузы |
Специальные |
|
2 |
Федеральные |
|||||
|
3 |
Муниципальные |
|||||
|
4 |
Частная |
|||||
|
5 |
С иностр. кан. |
|||||
|
6 |
Итого |
Определим имена
В В6: =СУММ (легковые).
Функция =СУММ ( ) достачно гибкая, но при добавлении ячейки в конец (начало) диапазона могут возникнуть трудности.
Например:
1 100
2 200
3 100
4 100
5 200
6 100 - ввести
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.
Функция корень.
=Корень (число). Возвращает значение квадратного корня.
Функция остаток.
=ОСТАТОК (число; делитель)
возвращает остаток от деления.
=остаток (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 вложений.
Функция ДАТ
Создание упорядоченного ряда. Правка®Содать®Пргрессия®окно прогрессия®Установить флажки:
по строкам столбцами секция тип дата.
В секции единицы выбрать день месяц, год.
Ввести с клавиатуры дату: например 25.04.99 и скопировать.
Ввод дат с использованием автозаполнения
вводим начальную дату и за ней следующую
Форматирование дат и времени:
Формат®Ячейка®вкладка число®Дата®Тип.
Арифмитические операции с датами:
а) 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.
Присвойте следующие имена
Минимум
Начислено
Налог
Проф
Пенс
Удержано
Выдано
Используя присвоенные имена, формулы и формулы массива, выполните расчеты
Алгоритм расчета :
В ячейке G2 записать 83,49 - это текущий необлагаемый налогом минимум
В столбце Начислено записать исходные суммы начислений
Проф. взносы подсчитать как 1% от Начислено.
Пенс. Отчисления подсчитать как 1% от Начислено.
Налог подсчитать как 12% от (Начислено - Пенс - минимум).
Выполнить функцию округления результата до целых
Удержано подсчитать как Пенс. + Проф. +Налог.
Выдано подсчитать как Начислено - Удержано.
Подсчитайте ИТОГО.