Отображение формул в электронных таблицах.

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

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

Ссылки на ячейки.

Формула может содержать ссылки, то есть адреса ячеек, содержимое которых используется в вычислениях. Это означает, что результат вычисления формулы зависит от числа, находящегося в другой ячейке. Ячейка, содержащая формулу, таким образом, является зависимой. Значение, отображаемое в ячейке с формулой, пересчитывается при изменении значения ячейки, на которую указывает ссылка.

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

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

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

- Формулы представляют собой выражения, по которым выполняются вычисления на листе. Формула начинается со знака равенства (=). Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5.

Формула может также содержать такие элементы, как: функции (Функция. Стандартная формула, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы в ячейках листа, особенно, если они длинные или сложные.), ссылки, операторы (Оператор. Знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок.) и константы (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Выражение и результат вычисления выражения константами не являются.).

Что такое функция в электронной таблице и ее типы? Приведите примеры.

- В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач.

Все функции в Excel характеризуются:

Названием;

Предназначением (что, собственно, она делает);

Количеством аргументов (параметров);

Типом аргументов (параметров);

Типом возвращаемого значения.

В качестве примера разберем функцию «СТЕПЕНЬ»

Название: СТЕПЕНЬ;

Предназначение: возводит указанное число в указанную степень;

Количество аргументов: РАВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!);

Тип аргументов: оба аргумента должны быть числами, или тем, что в итоге преобразуется в число. Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместо одно из них написать логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает «ЛОЖЬ» равно 0, а истину - любое другое ненулевое значение, даже −1 равно «ИСТИНА». То есть логические значения в итоге преобразуются в числовые;

Тип возвращаемого значения: число - результат возведения в степень.

Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячкейке и нажать Enter, в ячейке будет число 1024. Здесь 2 и 10 - аргументы (параметры), а 1024 - возвращаемое функцией значение.

Какие способы ввода формулы в ячейку существуют?

- Быстрое копирование формул Можно быстро ввести одну и ту же формулу в диапазон ячеек. Выберите диапазон, для которого вычисляется формула, введите формулу, а затем нажмите сочетание клавиш CTRL+ВВОД. Например, если в диапазон ячеек C1:C5 ввести формулу =СУММ(A1:B1), а затем нажать сочетание клавиш CTRL+ВВОД, Excel введет формулу в каждую ячейку диапазона, используя A1 в качестве относительной ссылки (Относительная ссылка. Адрес ячейки в формуле, определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется. Относительные ссылки задаются в форме A1.).

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

- Использование всплывающих подсказок При хорошем знании аргументов (Аргумент. Значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами.) функции можно использовать всплывающие подсказки для функций, которые появляются после ввода имени функции и открывающейся скобки. Щелкните имя функции, чтобы просмотреть справку по этой функции, или щелкните имя аргумента, чтобы выбрать соответствующий аргумент в формуле.

www. сайт

I вариант

А) А12С

Б) В1256

В) 123С

Г) В1А

2. В электронных таблицах выделена группа ячеек А1:В3. Сколько ячеек входит в этот диапазон?

А) 6

Б) 5

В) 4

Г) 3

А1*2

А1+В1

А) 5

Б) 10

В) 15

Г) 20

4.В ЭТ нельзя удалить:

А) столбец

Б) строку

В) имя ячейки

Г) содержимое ячейки

5.Основным элементом ЭТ является:

А) ячейка

Б) строка

В) столбец

Г) таблица

А) А2+В4

Б) =А1/С453

В) =С245*М67

Г) =О89-К89

7.При перемещении или копировании в ЭТ абсолютные ссылки:

А) не изменяются;

В) преобразуются в зависимости от нового положения формулы;

8. Диапазон – это:

А) все ячейки одной строки;

Б) совокупность клеток, образующих в таблице область прямоугольной формы;

В) все ячейки одного столбца;

Г) множество допустимых значений.

9. Электронная таблица – это:

А) прикладная программа для обработки кодовых таблиц;

Б) устройство персонального компьютера, управляющее его ресурсами;

В) прикладная программа, предназначенная для обработки структурированных в виде таблицы данных ;

Г) системная программа, управляющая ресурсами персонального компьютера при обработке таблиц.

А) =А2*$ С$ 2;

Б) =$A $2*C 2;

В) =A 3*$ C $2;

Г) = A 2*C 3.

Тестирование по теме: "Электронная таблица Microsoft Excel ".

II вариант

1.Укажите правильный адрес ячейки:

А) 12А

Б) В89К

В) В12С

Г) О456

2. В электронных таблицах выделена группа ячеек А1:С2. Сколько ячеек входит в этот диапазон?

А) 6

Б) 5

В) 4

Г) 3

3.Результатом вычислений в ячейке С1 будет:

А1*3

А1+В1

А) 5

Б) 10

В) 15

Г) 20

4.В ЭТ формула не может включать в себя:

А) числа

Б) имена ячеек

В) текст

Г) знаки арифметических операций

5.В ЭТ имя ячейки образуется:

А) из имени столбца

Б) из имени строки

В) из имени столбца и строки

Г) произвольно

6.Укажите неправильную формулу:

А) =О45*В2

Б) =К15*В1

В) =12А-В4

Г) А123+О1

7.При перемещении или копировании в ЭТ относительные ссылки:

А) не изменяются;

Б) преобразуются вне зависимости от нового положения формулы;

В) преобразуются в зависимости от нового положения формулы;

Г) преобразуются в зависимости от длины формулы.

8. Активная ячейка – это ячейка:

А) для записи команд;

Г) в которой выполняется ввод данных.

9. Электронная таблица предназначена для:

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

Б) упорядоченного хранения и обработки значительных массивов данных;

В) визуализации структурных связей между данными, представленными в таблицах;

Г) редактирования графических представлений больших объемов информации.

10. Какая формула будет получена при копировании в ячейку D 3, формулы из ячейки D 2:

А) =А2*С2;

Б) =$ A $2* C 3;

В) =$A $2*$C $3;

Г) = A 2*C 3.

Практическое задание
Подведение итогов

1. Предлагаю учащимся проверить знание определений, изученных на предыдущем уроке. Для этого использую презентацию «Проверь себя». Зачитываю определение, учащиеся называют номер его. Переходим по гиперссылке. Читаем определение, убеждаемся в правильности или неправильности выбора.
2. Формулирую принцип относительной адресации : адреса ячеек, используемые в формулах, определены не абсолютно, а относительно места расположения формулы.
3. Например: в таблице на <Рисунке1> формулу в ячейке С1 ЭТ воспринимает следующим образом: сложить значение ячейки, расположенной левее на две клетки со значением из ячейки, расположенной на одну клетку левее данной формулы.

Рисунок 1

Формулы в электронной таблице
В самом простом варианте электронная таблица может быть использована как калькулятор. Любая формула, которая вводится в ячейку, начинается со знака = . Все символы, введенные после этого знака (числа, адреса ячеек, арифметические знаки и др.) будут восприниматься как элементы формулы. Например, для вычисления выражения

Рисунок 2

в любую ячейку таблицы достаточно ввести следующую строчку
=(5+18^0,5 – 4*3)/(16^(1/2)+27^(1/3))-3e-2.
Результат будет выведен в этой же ячейке. Приоритет операций такой:

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

В качестве операторов в формулах используются следующие символы: + = - * / & < > ^
Задания для практической работы на компьютере:
Введите в любую ячейку электронной таблицы формулы и сравните полученный результат с ответом

Рисунок 3

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

Рисунок 4

На рисунке видно, что цена и количество размещены соответственно в ячейках С2 и D2 , в ячейке Е2 размещен результат умножения, а в строке формул показана формула, в соответствии с которой вычисляется произведение чисел, находящихся в ячейках С2 и D2 , то есть =C2*D2
Для того, чтобы вместо результата в ячейке увидеть формулу, необходимо перейти в режим отображения формул: Сервис – Параметры – Вид – Установить флажок отображать «Формулы»
Проверим себя.
Адресация в формулах. Задание на слайде 8:

Какое число появится в ячейке C6, если в нее будет введена формула =(B2+С2*В1+D1)/D2*А3? Ответ: 8

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

Рисунок 5

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

Рисунок 6

Теперь можно ввести новые понятия и определения.

Если в электронной таблице необходимо записать формулу с сохранением структуры, то есть так чтобы при изменении позиции ячейки с формулой менялся и адрес ячейки, на которую ссылается формула, то применяется относительная ссылки .
Если при изменении позиции ячейки с формулой адрес ячейки, на которую есть ссылка в формуле, должен остаться неизменным, то применятся абсолютная ссылка .
При создании относительной ссылки (она используется по умолчанию) в формуле указывают букву столбца и номер строки (=C2*D2 ), при создании абсолютной ссылки перед номером столбца и строки размещают символ «$» (значок доллара). Например, в формуле =B3/$D$2 ссылка на ячейку D2 является абсолютной.

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

Иногда возникает необходимость для столбца использовать относительную ссылку, а для строки - абсолютную или наоборот. В таких случаях ссылка называется смешанной.
Пример применения смешанных ссылок представлен в «Таблице умножения». В 2 -й строке и в столбце А расположены числа в диапазоне от 10 до 20. На пересечении соответствующих строк и столбцов находится произведение этих чисел. Для быстрого заполнения этой таблицы при написании формулы в ячейке B3 была использована смешанная ссылка как на ячейки 2 -й строки, так и на ячейки столбца А .

Рисунок 7

Мини-исследование. Определение типа ссылки в формуле
Определите, какая формула введена в ячейку В3 , если после этого таблица умножения создается копированием формулы вниз, а затем после выделения диапазона В3:В12 вправо. Ответ: =$A3*B$2
Проверяем себя. Абсолютная и относительная ссылки.
В таблице выполняется команда копирования ячейки С1 в D1. Какое число будет в ячейке D1 после завершения команды? Ответ: 8
Ввод и редактирование формул
Особенности ввода формулы.
При вводе формулы в ячейку первым символом должен быть знак равенства «=». Затем, в зависимости от вида формулы вводятся либо символы (скобки, знаки арифметических действий, числа, адреса ячеек и др.), либо с помощью курсора мыши или стрелок навигации указывают на ячейки, которые принимают участие в формуле.
Если при вводе формулы необходимо создать абсолютную или смешанную ссылку, то для этого удобно использовать функциональную клавишу F4 . Многократное нажатие этой клавиши приводит к появлению или исчезновению знака $ .
Для исправления ошибок или проверки правильности формулы целесообразно перейти в режим редактирования ячейки с формулой. В этом режиме все ячейки, на которые есть ссылки в формуле, выделены разноцветными рамками и для изменения адресов достаточно курсором мыши перетащить эти рамки к нужным ячейкам. Если необходимо поменять тип ссылки, то в тексте формулы выделяется необходимый адрес и используется клавиша F4 .
Закрепляем изученное на уроке.
Заданы значения переменных x=5; y=6; z=10. Распределение переменных по ячейкам указано в таблице. Необходимо вычислить математическое выражение, в которое входят эти переменные. Для этого в электронной таблице в любой свободной ячейке записать формулу со ссылками на ячейки с переменными и сравнить полученный результат с ответом.

Алгоритм выполнения для 1-го примера.

Разместите в ячейках A2 - 5, B3 - 6; C1 - 10
В любую свободную ячейку введите формулу =(А2^2+B3^2+C1^2+64)^0,5
Нажмите Enter и в вместо формулы в ячейке появится число 15

Остальные примеры проделайте самостоятельно.

Готовимся к ЕГЭ
1. В ячейке A1 электронной таблицы записана формула С2+$C3. Какой вид приобретет формула после копирования содержимого ячейки A1 в B1?

D2+$D3
D2+$C3
D3+$C3
C2+$C3

2. В ячейке C2 электронной таблицы записана формула B4+$D3. Какой вид приобретет формула после копирования содержимого ячейки C2 в B1?

A3+$D3
B2+$D2
A3+$D2
B3+$D3

3. Содержимое ячейки С1 сначала скопировали в ячейку D1, а затем в D2. Какое число появится в ячейке D 2?

1. Электронные таблицы. Формулы в MSExcel

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

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

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

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

проведения однотипных расчетов над большими наборами данных;

автоматизации итоговых вычислений;

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

подготовки табличных документов;

построения диаграмм и графиков по имеющимся данным.

Одним из наиболее распространенных средств работы с документами, имеющими табличную структуру, является программа MicrosoftExcel.

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

Основные понятия электронных таблиц

Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц. Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от А до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536 (максимально допустимый номер строки).

Ячейки и их адресация

На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (в этом порядке), на пересечении которых она расположена, например: А1 или DE234. Обозначение ячейки (ее номер) выполняет функции ее адреса. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках.

Диапазон ячеек

На данные, расположенные в соседних ячейках, можно ссылаться в формулах, как на единое целое. Такую группу ячеек называют диапазоном. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например: А1:С15.

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

Ввод текста и чисел

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

Форматирование содержимого ячеек.

Текстовые данные по умолчанию выравниваются по левому краю ячейки, а числа - по правому. Вкладки этого диалогового окна позволяют выбирать формат записи данных (количество знаков после запятой, указание денежной единицы, способ записи даты и прочее), задавать направление текста и метод его выравнивания, определять шрифт и начертание символов, управлять отображением и видом рамок, задавать фоновый цвет.

Копирование содержимого ячеек

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

Построение диаграмм и графиков

В программе Excel термин диаграмма используется для обозначения всех видов графического представления числовых данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.

Диаграмма представляет собой вставной объект, внедренный на один из листов рабочей книги. Она может располагаться на том же листе, на котором находятся данные, или на любом другом листе (часто для отображения диаграммы отводят отдельный лист). Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных немедленно изменяет свой вид.

электронный таблица ячейка диаграмма