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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Список литературы

• 1. https://ru.wikipedia.org
• 2. https://ru.wikibooks.org
• 3. Справка Excel

Содержимое ячейки воспринимается программой Excel как формула, если оно начинается со знака «=». Формула может содержать числовые константы , функции Excel и ссылки на ячейки. Ввод формулы заканчивается нажатием клавиши или щелчком на кнопке Ввод в строке формул. В ячейке выводится результат вычисления, а при активизации ячейки в строке формул отображается введенная формула. Примечание . Чтобы увидеть формулы в ячейках таблицы , нужно в диалоговом окне Сервис Параметры на вкладке Вид в области Параметры окна установить флажок Формулы . Для возвращения к обычному виду ячеек необходимо сбросить этот флажок. Правило использования формул в программе Excel состоит в том, что если вычисляемое значение зависит от других ячеек таблицы, то всегда следует использовать формулу со ссылками на эти ячейки. Ссылка задается указанием адреса ячейки. На рисунке 5.1 показан пример вычисления в ячейке С2 по формуле: = A2*B2

1. ввести адрес ячейки с клавиатуры;
2. по ходу ввода формулы щелкать на нужной ячейке.

1. активизировать ячейку С2 ;
2. ввести с клавиатуры знак "=";
3. щелкнуть в ячейке А2 ;
4. ввести с клавиатуры знак " * ";
5. щелкнуть в ячейке В2 ;
6. нажать .

Копирование формул

Другие способы копирования формул:

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

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

Правило использования формул в программе Excel состоит в том, что если вычисляемое значение зависит от других ячеек таблицы, то всегда следует использовать формулу со ссылками на эти ячейки. Ссылка задается указанием адреса ячейки. На рисунке 5.1 показан пример вычисления в ячейке С2 по формуле: = A2*B2

ввести адрес ячейки с клавиатуры;

по ходу ввода формулы щелкать на нужной ячейке.

Второй способ является более быстрым и удобным.

Так для ввода указанной формулы, следует последовательно выполнить следующие действия:

активизировать ячейкуС2 ;

ввести с клавиатуры знак "=";

щелкнуть в ячейке А2 ;

ввести с клавиатуры знак " * ";

щелкнуть в ячейке В2 ;

нажать .

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

Копирование формул

Копирование формулы в смежные ячейки производится методом автозаполнения , т.е. протягиванием маркера заполнения ячейки с формулой на соседние ячейки (по столбцу или по строке). Это самый удобный и быстрый способ копирования.

Другие способы копирования формул:

копировать ячейки;

	Цена	Количество	Стоимость
				Исходная формула
				Формула после копирования
				Формула после копирования
				Формула после копирования

Рис. 5.2

Относительные и абсолютные ссылки

Корректировка ссылок при копировании ячеек выполняется по умолчанию. Такие ссылки называются относительными . При изменении позиции формулы изменяется и ссылка.

Если же необходимо, чтобы при копировании формулы ссылка на определенную ячейку оставалась неизменной, то такую ссылку следует определить как абсолютную . Для указания абсолютной адресации используется символ «$» .

На рисунке 5.3 показан пример вычисления налога по формуле:

	Цена	Количество	Стоимость	Налог

Рис. 5.3

Чтобы определить ссылку как абсолютную, нужно после щелчка на ячейке (в данном примере это ячейка – А2 ) нажать клавишу <F4 >. Адрес ячейки в формуле автоматически дополнится символами«$» перед именем столбца и номером строки.

Лабораторная работа № 12

Тема:Вычисления в электронных таблицах.
Применение итоговых функций

Время на выполнение – 2 часа

Цель работы: научиться применять формулы в Excel, знать понятие относительной и абсолютной адресации, использовать итоговые функции для вычисления значений.

Основные сведения по теме

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

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

Рис. 40. В ячейке отображается только результат работы формулы,
а сама формула видна в строке формул

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

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

· во-первых, адрес ячейки можно ввести вручную;

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

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



Рис. 41. Диалоговое окно в развернутом и свернутом виде

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

- Формулы представляют собой выражения, по которым выполняются вычисления на листе. Формула начинается со знака равенства (=). Ниже приведен пример формулы, умножающей 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 отобразит под ячейкой динамически раскрывающийся список возможных функций, аргументов и имен, соответствующих этим буквам или триггеру. После этого в формулу можно вставить один из элементов раскрывающегося списка.

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