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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Наметившийся личный финансовый кризис вновь заставил меня устроится на работу в оффлайне. На сей раз техническим консультантом в местный райсовет. Задачи случается решать различные и вот на днях пришлось мне сотрудничать с нашей бухгалтерией...

Началось всё с того, что нужно было настроить работу одной из бухгалтерских систем отчётности. Это я сделал, а затем меня спросили, знаю ли я Excel. Экселя я особо не знал, но подумал, что по ходу дела разберусь, поэтому согласился помочь. А сделать нужно было таблицу для распечатки корешков по зарплате:)

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

Поэтому, задумавшись над таким положением вещей, я и решил написать статью, прочитав которую, можно было бы вникнуть в основы работы с электронными таблицами. В статье все примеры будут показаны на базе бесплатного компонента OpenOffice Calc, но они применимы и для Microsoft Office Excel.

Рабочие книги и их структура

Рабочими книгами в сфере электронных таблиц называют файлы, в которых эти таблицы хранятся. Для пакета Microsoft Office стандартными форматами файлов Excel будут XLS или XLSX , а для OpenOffice Calc - ODS .

По умолчанию рабочая книга содержит три листа, на каждом из которых имеется таблица с ячейками. Изначально всего существовало по 256 строк и столбцов на каждом листе (всего 65536 ячеек), однако, в современных табличных процессорах размеры листа гораздо большие (хотя и конечные):

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

На одном рабочем листе Вы можете создать фактически неограниченное количество таблиц с различными расчётами. Однако, на практике чаще всего один лист содержит всего одну таблицу.

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

1. Панель меню . Может быть выполнена в виде классической панели с раскрывающимися списками функций, а может реализовываться в виде вкладок (например, ленточный интерфейс Microsoft Office Excel 2007). Содержит доступ ко всем возможностям и настройкам программы.
2. Панель форматирования . Обычно отдельная панель или вкладка, на которой находятся инструменты форматирования текста и внешнего вида ячеек.
3. Навигационный список . Обычно находится в левом верхнем углу над рабочим листом и отображает адреса текущих выделенных ячеек. Также может быть использован для быстрого перехода к ячейке с заданным адресом (вводите адрес и жмёте Enter).
4. Поле ввода формул . Специальное поле, в котором можно задать как простое содержимое выделенной ячейки, так и специальную формулу для вычисления этого содержимого.
5. Строка состояния . Отображает дополнительную полезную информацию о типе выбранной ячейки, её текущем значении и другие служебные данные.

Ячейки электронных таблиц

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

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

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

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

Самая главная закавыка с ячейками кроется в первой вкладке окна "Формат ячеек" (в OpenOffice Calc она называется "Числа", а в Microsoft Office Excel - "Число"). Дело в том, что здесь задаётся тип данных ячейки и во многих готовых таблицах он не всегда стандартный. Если у Вас, например, введённое число превращается в дату или нормально не отображается текст, то проблема как раз в этих параметрах.

Также советую обратить внимание на кнопки на панели инструментов, которые позволяют увеличивать/уменьшать разрядность чисел в ячейках или включать денежный формат. Эти кнопки автоматически меняют тип данных (не нужно лезть в меню) для выделенных ячеек, выводя знаки после запятой или название валюты по умолчанию (задаётся в языковых параметрах Панели управления компьютера):

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

Формулы

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

Для примера заполним ячейки первой строки произвольными цифрами. Для этого просто выделяем ячейку и в ней или в строке ввода формулы пишем числа. Наиболее часто нам нужно получить сумму чисел в определённых ячейках, поэтому в одной из свободных ячеек (пускай это будет A2) нам необходимо ввести формулу:

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

Поскольку у нас всего три ячейки с цифрами, сумму которых нам нужно получить, то мы можем воспользоваться элементарными арифметическими действиями. Просто перечисляем по порядку адреса всех ячеек через знак "плюс". Для завершения ввода нажимаем Enter или кнопку с зелёной галочкой слева от поля ввода формулы.

Отдельно стоит сказать об адресации ячеек. Каждая ячейка имеет адрес вида: "буква столбца""цифра строки". Однако, это только один из видов ссылок - относительный.

Относительные ссылки могут автоматически меняться при изменении количества строк или столбцов. Например, в ячейке A2 у нас сейчас имеется формула: "=A1+B1+C1". Теперь, если мы вставим новую строку над первой, все наши ячейки опустятся вниз, но сохранят свои значения, а в формуле (которая теперь будет в ячейке A3) номер строки автоматически сменится на второй: "=A2+B2+C2":

Если же Вам нужно точно привязать формулу к конкретной ячейке, чтобы её значение не менялось, то Вам следует использовать абсолютные ссылки. Абсолютный адрес ячейки отличается только тем, что перед каждой её координатой Вы добавляете значок "доллара", например: $A$1. Если же значок "$" добавлять только к одной из координат, то мы получим смешанную ссылку, "привязанную" к номеру строки или столбца.

1. Ссылка на ячейку другого листа той же рабочей книги:

• Calc: =ИмяЛиста.АдресЯчейки (например: =Лист2.A1);
• Excel: =ИмяЛиста!АдресЯчейки (например: =Лист2!A1).

1. Ссылка на ячейку на листе другой открытой рабочей книги:

• Calc: -;
• Excel: =[ИмяКниги]ИмяЛиста!АдресЯчейки (например: =[Книга2]Лист1!A1).

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

• Calc: ="file:///ПутьКФайлу/ИмяФайла"#$ИмяЛиста.АдресЯчейки (например: ="file:///H:/source.ods"#$Лист1.A1);
• Excel: ="ПолныйПутьКФайлу\[ИмяКниги(файла)]ИмяЛиста"!АдресЯчейки (например: ="D:\Отчёты\[Книга1.xls]Лист1"!A1).

Calc не имеет отдельного вида формул для получения ссылок на ячейки в другом, открытом в данный момент, файле. Вместо этого Вам нужно использовать третий вариант с полным видом ссылки. При этом, обратите внимание на слеши в путях. В Excel, как в Проводнике Windows, используются обратные слеши, тогда как в Calc используются абсолютные ссылки в стиле UNIX-подобных систем!

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

Принцип диапазона в том, что мы указываем адреса первой и последней смежных ячеек через двоеточие. Табличный же процессор автоматически получает значения всех ячеек, которые находятся между заданными точками и производит над ними указанные нами действия (в нашем случае суммирование).

В русскоязычных версиях Excel все доступные действия формул тоже русифицированные, тогда как в Calc сохранены оригинальные английские названия (правда, снабжённые русским описанием). Исходя из описаний Вы, в принципе, можете найти любые действия в любом табличном процессоре, но ниже я приведу соответствия наиболее используемых формул:

Наиболее полный же список соответствий Вы можете найти на официальном WIKI-ресурсе OpenOffice .

Стилизация и распечатка таблиц

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

Во всех ячейках числа будут вводиться вручную (или браться из других файлов с ведомостями), а в трёх будут автоматически рассчитываться при помощи элементарных формул и арифметических действий: СУММ или SUM для ячеек "Всего насчитано" и "Всего удержано", а также "Всего насчитано"-"Всего удержано".

После ввода всех полей наша табличка будет иметь примерно следующий вид:

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

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

Таблица приобретёт более красивый внешний вид, однако, нам нужно как-то узнать, насколько широкой её делать, чтобы она уместилась на печатный лист. Для этого нужно активировать разметку страницы. Это можно сделать несколькими способами, однако, самый универсальный - из меню "Файл" вызвать функцию "Предварительный просмотр", а затем закрыть окно предпросмотра:

Теперь, когда разметка у нас готова, отрегулируем ширину ячеек так, чтобы они все уместились на одной странице. Теперь осталось немного. Нам нужно объединить несколько ячеек в левом верхнем углу для записи в них имени получателя. Для этого выделим четыре ячейки, вызовем их контекстное меню и выберем пункт "Объединить ячейки" (для Excel) или меню "Формат" - "Объединить ячейки" (для Calc).

Последний штрих - добавление рамок для нашей таблицы. Снова выделяем все используемые ячейки и в контекстном меню выбираем пункт "Формат ячеек". Переходим на вкладку "Обрамление" (Calc) или "Граница" (Excel) и настраиваем внешний вид рамок (для каждого элемента границы можно задать свой стиль):

В итоге мы получим красивую, готовую к распечатке табличку! При желании можно изменить ещё и фон строк с текстом, а также скопировать и вставить ниже несколько копий нашего зарплатного "корешка", чтобы на одном печатном листе их было несколько.

Выводы

В нашей статье мы рассмотрели только самые базовые действия с электронными таблицами. На практике у Вас может возникнуть множество вопросов. В Excel уже встроена хорошая справочная система, в которой можно найти большинство ответов. Для Calc же этим целям соответствует русскоязычный WIKI-портал .

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

P.S. Разрешается свободно копировать и цитировать данную статью при условии указания открытой активной ссылки на источник и сохранения авторства Руслана Тертышного.

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

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

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

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

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

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

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

Расчеты с использованием электронных таблиц (формулы) Применение формул при использовании ЭТ. 7 класс

«Запишите математические формулы в виде формул электронной таблиц»

Просмотр содержимого документа
«План конспект »

Конспект урока по ФГОС

Тема урока. Электронные таблицы

Цель урока: сформировать умения создания, редактирования, форматирования и выполнения простейших вычислений в электронных таблицах

Задачи урока:

Образовательные:

Практическое применение изученного материала.

Закрепление знания общих принципов работы табличного процессора MS EXCEL и умения составить таблицу для решения конкретной задачи.

Формирование представления о вычислениях в электронных таблицах как важной, полезной и широко применяемой на практике структуре.

Развивающие:

Развитие навыков индивидуальной и групповой практической работы.

Развитие способности логически рассуждать, делать эвристические выводы.

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

Воспитательные:

Воспитание творческого подхода к работе, желания экспериментировать.

Развитие познавательного интереса, воспитание информационной культуры.

Профессиональная ориентация и подготовка к дальнейшему самообразованию к будущей трудовой деятельности.

Тип урока: комбинированный.

Форма проведения урока: беседа, работа в группах, индивидуальная работа.

Программное и техническое обеспечение урока:

мультимедийный проектор;

компьютерный класс;

программа MS EXCEL.

Просмотр содержимого документа
«прил 1»

Вписать названия элементов интерфейса программ

Просмотр содержимого документа
«прил 2»

Тест по теме электронные таблицы MS Excel

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

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

упорядоченного хранения и обработки текстовых данных;

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

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

Электронная таблица представляет собой:

совокупность нумерованных строк и обозначенных латинскими буквами столбцов;

совокупность обозначенных латинскими буквами строк и нумерованных столбцов;

совокупность пронумерованных строк и столбцов;

совокупность строк и столбцов, именуемых произвольным образом.

Строки электронной таблицы:

именуются пользователем произвольным образом;

нумеруются.

В общем случае столбцы электронной таблицы:

обозначаются буквами латинского алфавита;

нумеруются;

обозначаются буквами русского алфавита;

именуются пользователем произвольным образом.

Для пользователя ячейка электронной таблицы обозначается:

именем столбца и номером строки, на пересечении которых располагается ячейка;

адресом машинного слова оперативной памяти, отведенного под ячейку;

специальным кодовым словом;

именем, произвольно задаваемым пользователем.

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

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

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

Выберите правильные обозначения ячеек :

Просмотр содержимого презентации

Поле имени

Строка заголовка

Меню программы

Стандартная панель

Панель форматирования

Строка формул

Текущая ячейка

Ярлыки листов

Линейки прокрутки

Строка состояния

Кнопки прокрутки ярлыков листов

Ярлыки листов

Количество

Карандаш

Стоимость

В магазине необходимо приобрести 5 альбомов по цене 15 р, 4 ручки по цене 10 р, 2 набора карандашей по цене 35 р и 10 тетрадей по цене 12 р. При этом с собой у вас 300 р.

Количество

Карандаш

Стоимость

Формулы в электронных таблицах

Формула всегда начинается со знака = (равно). Она может содержать числа, адреса ячеек или диапазонов, имена функций, соединенные знаками операций +, –, * (умножить), / (разделить), ^ (возвести в степень) и скобками. Например, =3*4/5 или =D4/(A5–0.77) +СУММ(C1:C5).

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

Для вставки имени ячейки в формулу проще всего щелкнуть на той ячейке, имя которой надо вставить в формулу. Имя появится в том месте строки формул, где находился текстовый курсор.

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

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

Финансовые

Назначение функций

Вычисление процентной ставки, ежемесячных и амортизационных отчислений.

Дата и время

Отображение текущего времени, дня недели, обработка значений

Математические

даты и времени.

Статистические

Вычисление абсолютных величин, стандартных тригонометрических

Вычисление среднего арифметического, дисперсии, среднеквадратического отклонения, наибольшего и наименьшего

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

чисел выборки, коэффициентов корреляции.

Вычисление значения определенного диапазона; создание гиперссылки на сетевые документы или веб-документы.

Работа с базой данных

Выполнение анализа информации, содержащейся в списках или базах данных.

Текстовые

Преобразование регистра символов текста, усечение заданного количества символов с правого или левого края текстовой строки, объединение текстовых строк.

Логические

Обработка логических значений.

Информационные

Передача информации о текущем статусе ячейки, объекта или среды

Инженерные

из Excel в Windows.

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

Ввод функций

Перед вводом функции убедитесь, что ячейка для ее размещения является активной. Нажмите клавишу [ = ] .

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

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

Если необходимая функция не представлена в списке, щелкните на кнопке Вставка функции строки формул или выберите команду Другие функции.

Мастер функций

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

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

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

Подарки

Серебро, груды

Вес за единицу, пудов

Соболя, штук

Количество

Парча, тюков

Вес всего, пудов

Фрукты, ящики

Итого:

груз ≤ 45

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

Основные типы и форматы данных

В работе с электронными таблицами можно выделить три основных типа данных: числа, текст и формулы.

Числа . Для представления чисел могут использоваться несколько различных форматов (числовой, экспоненциальный, дробный и процентный ). Существуют специальные форматы для хранения дат (например, 25.09.2003) и времени (например, 13:30:55), а также финансовый и денежный форматы (например, 1500,00р.), которые используются при проведении бухгалтерских расчетов.

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

Экспоненциальный формат применяется, если число, содержащее большое количество разрядов, не умещается в ячейке. В этом случае разряды числа представляются с помощью положительных или отрицательных степеней числа 10. Например, числа 2000000 и 0,000002, представленные в экспоненциальном формате как 2 × 10 6 и 2 × 10 -6 , будут записаны в ячейке электронных таблиц в виде 2,00Е+06 и 2,00Е-06.

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

Текст . Текстом в электронных таблицах является последовательность символов, состоящая из букв, цифр и пробелов. Например, последовательность цифр "2004" - это текст. По умолчанию текст выравнивается в ячейке по левому краю. Это объясняется традиционным способом письма (слева направо).

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

Например, формула =А1+В1 обеспечивает сложение чисел, хранящихся в ячейках А1 и В1, а формула =А1*5 - умножение числа, хранящегося в ячейке А1, на 5. При изменении исходных значений, входящих в формулу, результат пересчитывается немедленно.

В процессе ввода формулы она отображается как в самой ячейке, так и в строке формул (рис. 1.1). После окончания ввода, которое обеспечивается нажатием клавиши {Enter}, в ячейке отображается не сама формула, а результат вычислений по этой формуле.

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

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

Ввод в формулы имен ячеек можно осуществлять выделением нужной ячейки с помощью мыши.

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

Для быстрого копирования данных из одной ячейки сразу во все ячейки определенного диапазона используется специальный метод: сначала выделяется ячейка и требуемый диапазон, а затем вводится команда [Заполнитъ-вниз ] (вправо, вверх, влево ).

Контрольные вопросы

1. Какие типы данных могут обрабатываться в электронных таблицах?

2. В каких форматах данные могут быть представлены в электронных таблицах?

1. Задание с кратким ответом. Запишите формулы:

- сложения чисел, хранящихся в ячейках А1 и В1;
- вычитания чисел, хранящихся в ячейках A3 и В5;
- умножения чисел, хранящихся в ячейках С1 и С2;
- деления чисел, хранящихся в ячейках А10 и В10.

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

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

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

Так, при копировании формулы из активной ячейки С1, содержащей относительные ссылки на ячейки А1 и В1, в ячейку D2 имена столбцов и номера строк в формуле изменятся на один шаг соответственно вправо и вниз. При копировании формулы в ячейку ЕЗ имена столбцов и номера строк в формуле изменятся на два шага соответственно вправо и вниз и т. д. (табл. 1.3).

Создадим в электронных таблицах фрагмент таблицы умножения. В столбцах А и В разместим числа от 1 до 9, а в столбце С - их произведения.

Для этого введем в ячейки А1 и В1 число 1, в ячейку С1 - формулу =А1*В1, а в ячейки А2 и В2 - формулы =А1+1 и =В1+1 с относительными ссылками. Тогда для заполнения таблицы достаточно будет просто скопировать формулы в нижележащие ячейки (табл. 1.4).

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

Так, при копировании формулы из активной ячейки С1, содержащей абсолютные ссылки на ячейки $А$1 и $В$1, значения столбцов и строк в формуле не изменятся (табл. 1.5).

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

Пусть названия устройств размещены в ячейках столбца А, их цены в условных единицах - в ячейках столбца В, цены в рублях будут вычисляться в ячейках столбца С, а значение курса условной единицы к рублю хранится в ячейке Е2. Тогда в ячейку С 2 необходимо ввести формулу =В2*$Е$2, содержащую абсолютную ссылку, и скопировать ее в нижележащие ячейки столбца С (табл. 1.6).

Смешанные ссылки . В формуле можно использовать смешанные ссылки, в которых координата столбца относительная, а строки - абсолютная (например, А$1), или, наоборот, координата столбца абсолютная, а строки - относительная (например, $В1) (табл. 1.7).

В качестве примера использования в формуле смешанной ссылки можно рассмотреть пересчет цен из условных единиц в рубли по двум курсам (доллара и евро). Пусть в созданной нами таблице цен устройств компьютера в ячейке Е2 хранится курс доллара к рублю, а в ячейке F2 - курс евро к рублю. Тогда в ячейку С2 необходимо ввести формулу =$В2*Е$2, содержащую смешанные ссылки, и скопировать ее в нижележащие ячейки столбца С, а затем - в соседние ячейки столбца D (табл. 1.8).

Контрольные вопросы

1. Как изменяется при копировании в ячейку, расположенную в соседнем столбце и строке, формула, содержащая относительные ссылки? Абсолютные ссылки? Смешанные ссылки?

Задания для самостоятельного выполнения

2. Задание с кратким ответом. Какой вид приобретут формулы, хранящиеся в диапазоне ячеек С1:СЗ, при их копировании в диапазон ячеек Е2:Е4?

3. Практическое задание. Проверьте в электронных таблицах правильность ответов на предыдущее задание.

Встроенные функции

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

Суммирование. Одной из наиболее часто используемых операций является суммирование значений диапазона ячеек. Для этого необходимо выделить диапазон, причем для ячеек, расположенных в одном столбце или строке, достаточно для вызова функции суммирования чисел СУММ() щелкнуть по кнопке Автосумма å на панели инструментов Стандартная .

Результат суммирования будет записан в ячейку, следующую за последней, ячейкой диапазона в столбце (например, =СУММ(А2:А4)), строке (например, =СУММ(С1:Е1)) или прямоугольном диапазоне ячеек (например, =СУММ(СЗ:Е4)) (рис. 1.2).

Рис. 1.2. Суммирование значений диапазонов ячеек

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

Степенная функция. В математике широко используется степенная функция у = х n , где х - аргумент, a n - показатель степени (например, у = х 2 , у = х 3 и т. д.). Ввод функций в формулы можно осуществлять с помощью клавиатуры или с помощью Мастера функций , который предоставляет пользователю возможность вводить функции с использованием последовательностей диалоговых панелей.

Например, если в ячейке В1 хранится значение аргумента х функции, то вид функции, введенной с клавиатуры (ячейка В2), будет =B1^2, а введенной с помощью мастера функций (ячейка ВЗ) - СТЕПЕНЬ(В1;2) (рис. 1.3).

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

Заполнение таблицы можно существенно ускорить, если использовать операцию Заполнить . Сначала в первую ячейку строки аргументов вводится наименьшее значение аргумента (например, в ячейку В1 вводится число -4), а во вторую ячейку вводится формула, вычисляющая следующее значение аргумента с учетом величины шага аргумента (например, =В1+1). Далее эта формула вводится во все остальные ячейки таблицы с использованием операции Заполнить вправо .

Аналогично, в первую ячейку строки значений функции вводится формула вычисления функции (например, в ячейку В2 вводится формула =В1^2), далее эта формула вводится во все остальные ячейки таблицы с использованием операции Заполнить вправо (табл. 1.9).

Задания для самостоятельного выполнения

4. Задание с кратким ответом. Какие значения будут получены в ячейках А5, F1 и F4 после суммирования значений различных диапазонов ячеек (см. рис. 1.2)? Проверить в электронных таблицах.

5. Задание с кратким ответом. Какие значения будут получены в ячейках В2 и ВЗ после вычисления значений степенной функции (см. рис. 1.3)? Проверить в электронных таблицах.

6. Задание с кратким ответом. Какие значения будут получены в ячейках В2 и ВЗ после вычисления значений квадратного корня (см. рис. 1.4)? Проверить в электронных таблицах.

7. Практическое задание. Построить таблицу значений функции у = Ö x. на отрезке с шагом 1.