Тема 15. Электронные таблицы MS Excel: Работа с формулами Вот формул - Информатика и компьютерная техника - Заочне навчання

Информатика и компьютерная техника

Тема 15. Электронные таблицы MS Excel: Работа с формулами Вот формул

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

 Запись формулы начинается знаком =.

Примеры.

1) = 2,3 * 4,5 - по этой формуле MS Excel умножит 2,3 на 4,5 и результат поместит в ту ячейку, в которой записана эта формула.

2) = А1 / А3 - по этой формуле MS Excel разделит содержимое ячейки А1 на содержимое ячейки А3. Результат от деления будет помещено в ту ячейку, где будет записана эта формула.

3) = $ A $ 1 / $ A $ 3 - по этой формуле будет вычислено то же, что и в случае 2). Знаки $, которые стоят перед именами столбцов и номерами строк играют роль только при копировании формул, а при вычислениях просто опускаются.

 Вот формулы можно осуществлять непосредственно, поместив табличный курсор в нужную ячейку и набрав с клавиатуры знак равенства (=), а дальше же формулу. Можно также воспользоваться кнопкой Изменить формулу, щелкнув на ней мышью. Вот адресов ячеек в формулу можно осуществлять щелчком мышью на соответствующих ячейках.Например, для ввода формулы = А1 * В1 нужно щелкнуть на кнопке Изменить формулу или набрать знак =, после чего щелкнуть на ячейке А1, дальше, набрать знак *, щелкнуть на ячейке В1, нажать клавишу Enter.

равна результата подъема содержимого ячейки А1 в степень, равную содержимого ячейки С5

MS Excel содержит очень большое количество встроенных функций. [1] Для удобного их выбора из списка при вводе, они разделены на категории: математические, финансовые, инженерные, текстовые, информационные и другие. [2] Детальное описание всех встроенных (порой еще говорят - стандартных) функций в рамках данного курса невозможно по двум причинам: во-первых, функций очень много, а во-вторых, описание некоторых категорий требует специальных знаний. Поэтому мы рассмотрим лишь наиболее важные [3] из них. В то же время, рассмотренных функций вполне достаточно для выполнения задач контрольной работы и понимание материала курса.

 В Excel нет встроенной функции для записи корня n-й степени. Для его вычисления нужно воспользоваться функцией СТЕПЕНЬ (a; b), исходя из того, что.

 Функции CTG  X  и arcctg  X  вычисляются по формулам:

,

. Пример. Платежная ведомость

 Сформируем рабочий лист для расчета заработной платы компании. АВС. Данные приведены в таблице:

Налог, Аванс и на руки рассчитываются по формулам: Налог = 0,06 * Оклад, Аванс = 0,44 * Оклад, на руки = Оклад-Налог-Аванс. Таблицу дополнить строкой (всего), содержащий суммы по соответствующим столбцам.

 Заносим данные в рабочий лист:

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

 В ячейку В1 заносим текст Платежная ведомость. Чтобы выделить заголовок полужирным шрифтом, нужно, после того, как текст введен и нажата клавиша Enter, установить курсор на ячейку В1 и щелкнуть мышью на кнопке Полужирный.

 Порядковые номера сотрудников можно записать воспользовавшись автозаполнением. Для этого в ячейке А4 записываем 1, а в ячейке А5 - 2. После этого, выделив эти две ячейки, перетаскиваем маркер автозаполнения вниз, пока в окошке появляется у маркера, а не будет записано 5.

 Чтобы в ячейке В3 текст Фамилия и инициалы разместился в две строки, нужно установить курсор на эту ячейку и выбрать из главного меню: ФорматÞЯчейкы .... В окне Формат ячеек, которое открывается выбираем вкладку Выравнивание:

В фрейме Отображение нужно поставить отметку в окошке переносит по словам, как показано на рисунке вверху.

 Чтобы в столбце Оклад у чисел появились наименование денежной единицы, нужно выделить ячейки с числами и выбрать ФорматÞЯчейкы .... В окне Формат ячеек, которое открывается выбираем вкладку Число:

В списке Числовые форматы выбираем Финансовый.

 После того, как данные занесены, заносим расчетные формулы для первого сотрудника: в ячейку D4 заносим формулу = 0,06 * C4, в ячейку E4 - формулу = 0,44 * C4 и, наконец, в ячейку F4 - формулу = C4-D4 -E4.

Выделив ячейки D4, E4, F4, используя автозаполнение, заносим формулы в ячейки D5: F8.

Для подсчета суммы окладов сотрудников, выделим ячейки С4: С9, после чего щелкаем мышью на кнопке Автосумма. После этого, в ячейке С9 появится сумма чисел, находящихся в ячейках С4: С8. Если вместо суммы в ячейке будет записано, то это значит, что число, которое получено в результате вычислений, имеет большее количество знаков чем может поместиться в ячейке. В этом случае нужно увеличить ширину ячейки, переместив правую границу заголовка с помощью мыши на достаточную величину.

 Выделяем ячейку С9 и с помощью автозаполнения копируем формулу из нее в ячейки D9: F9.

 В результате получим:

Логические функции

 При проведении вычислений часто возникает ситуация, когда следующее вычисления нужно проводить по одной из двух формул, в зависимости от результатов предыдущих вычислений. [4] То есть, если выполняется некоторое условие, то вычисляется значение одного выражения, а если не выполняется, то другое. Примером такой задачи является вычисление значений функции, заданной различными аналитическими выражениями (то есть различными формулами) на различных интервалах области определения. Так, для вычисления значения функции, заданной формулой

нужно вычислять по формуле, если, или по формуле в противном случае (т.е. если).

В MS Excel для такого рода вычислений используют логические выражения. Логическое выражение - это выражение, значением которого является одно из двух логических значений: ИСТИНА или ЛОЖЬ. [5] Простыми примерами логических выражений является равенства и неравенства. Сами значения ИСТИНА и ЛОЖЬ также логическими выражениями.

Примеры логических выражений.

1) 2 = 3 - логическое выражение, имеющее значение ЛОЖЬ;

2) 10 <100 - логическое выражение, имеющее значение ИСТИНА;

3) х> y - логическое выражение, значение которого определяется конкретными значениями x и y. Если значение х больше значения в, то значением этого выражения будет ИСТИНА. В противном случае значением логического выражения будет ЛОЖЬ.

Организация разветвлений осуществляется с помощью логической функции ЕСЛИ. Ее синтаксис [6]:

ЕСЛИ ( ЛО ;  В1 ;  В2 ),

где  ЛВ  - логическое выражение;  В1 ,  В2  - выражения, исчисляемые в двух разных ветвях алгоритма.

 Значение функции ЕСЛИ вычисляется так [7]:

·                  вычисляется значение логического выражения  ЛО ;

·                  если логическое выражение  ЛВ  имеет значение ИСТИНА, то вычисляется значение выражения  В1 . Вычислено значение и будет значением функции ЕСЛИ.

·                  если логическое выражение  ЛВ  важно ЛОЖЬ, то вычисляется значение выражения  В2 . Вычислено значение и будет, в этом случае, значением функции ЕСЛИ.

Не обязательно оба выражения должны быть одного типа. Выражение В1 может иметь, например, числовой тип, а выражение В2 -текстовый.

Примеры.

1) Предположим, что в ячейку А4 занесены формулу = ЕСЛИ (А1 <3; A2 + A3; A2 * A3). Тогда, если в ячейке А1 находится число, которое меньше 3, то вычисляется сумма чисел, находящихся в ячейках А2 и А3, и результат помещается в ячейку А4. Если в ячейке А1 находится число, которое больше 3 или равна 3, то вычисляется произведение чисел, находящихся в ячейках А2 и А3, и результат помещается в ячейку А4.

Пусть в ячейке А2 находится число 2, а в ячейке А3 находится число 3. Если в ячейке А1 находится число 5, то в ячейку А4 будет помещено число 2 × 3 = 6. Если в ячейке А1 находится число 1, то в ячейку А4 будет помещено число 2 + 3 = 5.

2) Рассмотрим нахождения корней квадратного уравнения 2х 2 -7х-11 = 0. Сформируем рабочий лист, занеся в ячейки данные, как показано ниже:

 

 Примечание . В ячейку В1 занесены текст Нахождение корней квадратного уравнения. На рисунке вверху видна лишь часть этого текста.

 После занесения данных и формул в рабочий лист получим результат:

 Если в ячейки В3: В5 занесем новые значения, формулы в рабочем листе будут автоматически перечислены. Так, занеся в ячейки В3: В5 значение 1, 2, 6 соответственно, получим:

 Организация ветвлений, имеющих три ветви или больше, можно осуществить также с помощью функции ЕСЛИ. Рассмотрим это на примере вычисления значения функции

 Пусть значение аргумента, для которого нужно вычислить значение функции, находится в ячейке А1, а значение функции будем вычислять в ячейке В1.

 В ячейку В1 заносим формулу

= ЕСЛИ (А1 <0; SIN (A1); ЕСЛИ (А1 <= 1; ATAN (A1); СТЕПЕНЬ (А1; 1/3))

 Вычисления по этой формуле будет осуществляться следующим образом:

· Вычисляется значение логического выражения А1 <0.

· Если значением этого выражения является ИСТИНА, то вычисляется значение выражения SIN (A1). Вычислено значение помещается в ячейку B1. На этом процесс вычисления завершается.

· Если значением этого выражения является ЛОЖЬ (а это значит, что А1³0), то вычисляется значение выражения ЕСЛИ (А1 <= 1; ATAN (A1); СТЕПЕНЬ (А1; 1/3)). Его вычисления осуществляется следующим образом:

· Вычисляется значение выражения А1 <= 1, то есть, проверяется выполнение условия А1 <= 1.

· Если условие выполняется [8], то есть значением выражения является ИСТИНА, то вычисляется ATAN (A1) и найденное значение помещается в ячейку В1.

· Если условие не выполняется [9], то есть значением выражения является ЛОЖЬ, то вычисляется A1 1/3  и найденное значение помещается в ячейку В1.

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

Синтаксис: И ( ЛВ1 ;  ЛВ2 ...)

Здесь  ЛВ1, ЛВ2  и т. д.  являются логическими выражениями. Количество аргументов, входящих в функцию И, не должна превышать 30.

Семантика: если значения всех аргументов является ИСТИНА, то и функция имеет значение ИСТИНА. В противном случае, значение функции является ЛОЖЬ.

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

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

Синтаксис: ИЛИ ( ЛВ1 ;  ЛВ2 ...)

Здесь  ЛВ1, ЛВ2  и т. д.  являются логическими выражениями. Количество аргументов, входящих в функцию И, не должна превышать 30.

Семантика: если значение хотя бы одного из аргументов является ИСТИНА, то и функция имеет значение ИСТИНА. В противном случае, значение функции является ЛОЖЬ.

Логическая функция НЕ изменяет значение своего аргумента на противоположное.

Синтаксис: НЕ ( ЛО )

Здесь  ЛО  является логическим выражением.

Семантика: если значение логического выражения  ЛО  является ИСТИНА, то значение функции не является ЛОЖЬ. Если значение логического выражения  ЛО  является ЛОЖЬ, то значение функции не является ИСТИНА. 


[1] Напомним, что функция - это закон (правило), по которому одному числу ставится в соответствие другое число. Функцию можно отождествить с механизмом, перерабатывает одно число в другое. В общем случае, функция - это закон, по которому нескольким объектам ставится в соответствие некоторый объект.

[2] К сожалению, разделение функций на категории при использовании Мастера функций и при использовании встроенного в MS Excel справочника несколько отличается.

[3] Важность - это, конечно, относительное понятие.

[4] Такое место в алгоритме называется разветвлением, а различные выражения - ветвями. В данном случае речь идет о разветвления на две ветви. В общем случае можно говорить о разветвления на несколько ветвей.

[5] ИСТИНА и ЛОЖЬ - это данные логического типа - четвертый тип данных в Excel. Ранее мы говорили о трех типах данных: текст, числа и формулы.

[6] Синтаксис - это правила записи конструкции. Семантика - это смысл конструкции.

[7] есть ее семантика

[8] Это соответствует выполнению условия 0 £ А1 £ 1.

[9] Это соответствует условию А1> 1.

 

Змiст

Тема 1. Основные понятия информатики.

Тема 2. Устройство компьютера и принципы его работы.

Тема 3. Периферийные устройства

Тема 4. Программное обеспечение

Тема 5. Операционная система Windows 95/98:

Тема 6. Операционная система Windows 95/98:

Тема 7. Операционная система Windows 95/98: Настройка

Тема 8. Стандартные программы Windows 95/98:

Тема 9. Стандартные программы Windows 95/98:

Тема 10. Редактор MS Word: Основы работы с редактором

Тема 11. Редактор MS Word: Форматирование документов

Тема 12. Редактор MS Word: Работа с таблицами.

Тема 13. Редактор MS Word: Дополнительные возможности

Тема 14. Электронные таблицы MS Excel: Основы работы

Тема 15. Электронные таблицы MS Excel: Работа с формулами Вот формул

Тема 16. Электронные таблицы MS Excel: Построение диаграмм. Географические карты

Тема 17. Электронные таблицы MS Excel: Сводные таблицы. Таблицы данных.

Тема 18. Электронные таблицы MS Excel:

Тема 19. Электронные таблицы MS Excel Средство Поиск развязку


Нові надходження

Всього підручників:

292