Задача №7. Электронные таблицы. Абсолютная и относительная адресация. Графики и диаграммы.

Microsoft Excel (в дальнейшем просто — Excel) — это программа выполнения расчетов и управления так называемыми электронными таблицами.

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

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

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

Для написания формул используют адреса ячеек и знаки арифметических операций (+, -, *, /, ^). Формула начинается знаком =.

В Excel предусмотрены стандартные функции, которые могут быть использованы в формулах. Это математические, логические, текстовые, финансовые и другие функции. Однако, на экзамене Вам могут встретиться только самые простые функции: СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение).

Диапазон ячеек обозначается следующим образом: A1:D4 (все ячейки прямоугольника от A1 до D4.

Адреса ячеек бывают относительными, абсолютными и смешанными.

Они по-разному ведут себя при копировании формулы из ячейки в ячейку.

Если в ячейке B2 мы напишем формулу =D1+3, то таблица воспримет это как «взять значение ячейки на две правее и на одну выше текущей, и прибавить к нему 3».

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

Если нам не нужно, чтобы адрес пересчитывался при копировании формулы, мы можем его «закрепить» в формуле — поставить знак $ перед буквой и индексом ячейки: =$D$1+3. Такой адрес называется абсолютным. Такая формула не будет изменяться при копировании:

Если же мы хотим, чтобы при копировании формулы автоматически пересчитывался, к примеру, только индекс ячейки, а буква оставалась неизменной, мы можем «закрепить» в формуле только букву (или наоборот): =$D1+3. Такой адрес называется смешанным. При копировании формулы будет меняться только индекс в адресе ячейки:

Электронные таблицы. Копирование формул.

В ячейке C2 записана формула =$E$3+D2. Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?

1) =$E$3+C1 2) =$D$3+D2 3) =$E$3+E3 4) =$F$4+D2

Место расположения формулы меняется с C2 на B1, т.е. формула сдвигается на одну ячейку влево и на одну ячейку вверх (буква «уменьшается» на единицу и индекс уменьшается на единицу). Значит, так же изменятся все относительные адреса, а абсолютные (закрепленные знаком $) останутся неизменными:

В ячейке В11 электронной таблицы записана формула. Эту формулу скопировали в ячейку А10. В результате значение в ячейке А10 вычисляется по формуле х—Зу, где х — значение в ячейке С22, а у — значение в ячейке D22. Укажите, какая формула могла быть написана в ячейке В11.

Место расположения формулы меняется с B11 на A10, т.е. буква «уменьшается» на 1 и индекс уменьшается на 1.

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

Редактор электронных таблиц MS Excel. Функциональные возможности и особенности работы (Реферат)

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

Поиск перечня доступных функций в Excel

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

Откройте вкладку «Формулы» и нажмите на кнопку «Вставить функцию» либо разверните список с понравившейся вам категорией функций.

Вместо этого всегда можно кликнуть по значку с изображением «Fx» для открытия окна «Вставка функции».

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

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

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

Электронные таблицы Excel

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

Основные элементы окна MS Excel

Основными элементами рабочего окна являются:

1. Строка заголовка (в ней указано название программы) с кнопками для управления окном программы и окном документа (свернуть, свернуть в окно или развернуть в полноэкранный режим, закрыть);
2. Строка главного меню (каждый пункт меню представляет собой набор команд, объединенных общей функциональной ориентацией), а также окно для поиска справочной информации.
3. Панели инструментов (стандартные, форматирование и т. д.).
4. Строка формул, содержащая поле «Имя» и кнопку «Вставить функцию» (fx) в качестве элементов, предназначена для ввода и редактирования значений или формул в ячейках. Поле Имя отображает адрес текущей ячейки.
5. Рабочая область (активная рабочая таблица).
6. Полосы прокрутки (вертикальная и горизонтальная).
7. Набор меток (листовых меток) для перемещения между листами.
8. Строка состояния.

Команда удаления Очистить содержимое есть также в контекстном. С помощью этой команды можно удалить только содержимое ячеек. Тот же результат достигается просто нажатием клавиши Del . Если пользователь случайно удалил нужную информацию, то командой Отменить очистку меню Правка � пользователь отменит команду удаления.

Табличный процессор Microsoft excel 97

1. финансовые функции;
2. функции даты и времени;
3. арифметические и тригонометрические (математические) функции;
4. статистические функции;
5. функции ссылок и подстановок;
6. функции базы данных (анализ списка);
7. текстовые функции;
8. логические функции;
9. информационные функции (проверка свойств и значений);
10. инженерные функции;
11. внешние функции.

Смешанная ссылка – обозначение ячейки в виде номера строки и столбца: $A1 и A200. Она используется, когда необходимо зафиксировать либо номер стобца, либо строки.

Структура документов

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

Отдельные рабочие листы одного документа расположены друг под другом. С помощью именного указателя, расположенного в нижней части экрана, можно переходить с одного листа на другой. На именном указателе находятся корешки рабочих листов, расположенные в порядке возрастания номеров: Лист1, Лист2 и т.д. ( Рис. 33 ).

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

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

Электронные таблицы Excel

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

Электронные таблицы Excel

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

1. Назначение и возможности таблиц Excel

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

Excel является приложением операционной системы Windows.

Использование шрифтов различных размеров и различных гарнитур;

деление экрана на отдельные окна и вывод большого количества страниц

2. Загрузка электронных таблиц Excel

Загрузка ЕХ производится щелчком по значку на панели Office, либо в меню «Пуск», «Программы», либо двойным щелчком по значку приложения на Рабочем столе (если таковой имеется).

Справа показа стандартный значок данного приложения.

Документы, созданные в Excel имеют расширение xls

3. Рабочее поле таблиц

Рабочее поле ЕХ представляет собой сетку из ячеек, каждая из которых имеет адрес: латинскую букву (или буквы), определяющую номер столбца и номер строки. Иначе, адрес называют ссылкой на ячейку.

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

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

· для набора числовых данных, которые подлежат обработке,

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

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

4. Общие сведения о книгах и листах Microsoft Excel

Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи. По умолчанию новая рабочая книга называется — КНИГА 1. Обычно пользователь даёт собственное название. Одна книга может содержать до 256 рабочих листов.

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

Имена листов отображаются на ярлычках в нижней части окна книги (см. рис 10).

Для перехода с одного листа на другой следует указать соответствующий ярлычок. Название активного листа выделено жирным шрифтом. По умолчанию они названы ЛИСТ 1, ЛИСТ 2, и т.д.

Переход на другой лист книги

Левым щелчком мыши укажите ярлычок листа, содержащий нужные данные.

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

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

5. Использование формул для организации таблиц

сложение +

вычитание —

умножение *

деление /

возведение в степень ^

знак процента %

объединение символов в одну последовательность (амперсант) &

оператор диапазона (двоеточие):

В формулах могут быть использованы операторы сравнения:

> (больше), < (меньше),

>= (больше либо равно),

Операции в скобках имеют приоритет над другими операциями.

Для набора десятичной дроби используется запятая. Например, 5,6; -7,09 и т.д.

Формулы могут содержать только числовые данные. Например, = (7,89 — 6,554) * 3

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

Например, формула может выглядеть так: = 3* В2 — D2.

(Содержимое ячейки В2 умножается на три и от результата вычитается содержимое ячейки D2)

Для получения результата по формуле нужно обязательно нажать Enter

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

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

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

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

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

Имеющиеся функции классифицированы по областям применения. Список областей находится в подменю мастера функций. Он содержит:

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

Для внесения изменений, исправлений в содержимое ранее набранных ячеек нужно:

1. Навести курсор на нужную ячейку и щёлкнуть левой кнопкой мыши

2. Левым щелчком мыши установить курсор в строке формул

3. Исправить ошибку или внести нужные изменения обычным способом,

6. Главное меню Excel

Главное меню активизируется щелчком мыши или при помощи клавиши Alt

Главное меню расположено на первой строке рабочего окна электронных таблиц EX (см. рис. 9) и предназначено для выполнения всех основных процедур.

Полное описание всех возможностей ЭТ можно уточнить используя справочную систему EX.

— параметры страницы; область печати; предварительный просмотр; печать

(эти пункты подменю доступны лишь при подключенном принтере)

ПРАВКА — позволяет отменить последнее действие; отменить отмену; осуществляет операции с буфером поиск и замену данных; заполнение и очистку текущей таблицы

ВИД — позволяет представить внешний вид рабочего стола ЕХ по желанию пользователя и выбрать нужные панели инструментов.

ВСТАВКА предлагает пользователю возможность вставить строку, столбец, новый лист, диаграмму, макрос. С её помощью можно осуществить в нужном месте разрыв страницы, вызвать мастер функции на экран (он может быть отдельной клавишей на экране), вызвать рисунок в текущую страницу и т.п.

СЕРВИС позволяет настроить параметры рабочего поля ЕХ, защитить лист от несанкционированного вмешательства и даёт некоторые др. возможности, в том числе

ПОДПУНКТ ОРФОГРАФИЯ — позволяет осуществить проверку правильности написания текста в текущей таблице показывает зависимости ячеек в текущей таблице;

при выборе пункта ПОДПУНКТ АВТОЗАМЕНА осуществляет исправление наиболее часто возникающих ошибок в тексте на правильное правописание.

ДАННЫЕ содержит подпункты: сортировка, фильтр, форма, проверка и некоторые другие.

ОКНО позволяет выполнить действия с окнами таблиц: разделить окна, скрыть от пользователей, расположить по желанию пользователя определённым образом и т.д.

7. Панель Стандартная

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

Перечислим ярлыки слева направо (см. рис. 9):

СОЗДАТЬ, ОТКРЫТЬ, СОХРАНИТЬ, РАСПЕЧАТАТЬ, ПРЕДВАРИТЕЛЬНЫЙ ПРОСМОТР ФАЙЛА,

ПРОВЕРКА ОРФОГРАФИИ,

ВЫРЕЗАТЬ В БУФЕР, СКОПИРОВАТЬ В БУФЕР, ВСТАВИТЬ ИЗ БУФЕРА,

УСТАНОВИТЬ ФОРМАТ ПО ОБРАЗЦУ,

ОТМЕНИТЬ ПОСЛЕДНЕЕ ДЕЙСТВИЕ, ПОВТОРИТЬ УДАЛЁННОЕ ДЕЙСТВИЕ,

ДВЕ СЛЕДУЮЩИЕ КНОПКИ — РАБОТА С ГИПЕРССЫЛКОЙ И web СТРАНИЦАМИ.

КНОПКА АВТОСУММИРОВАНИЯ, МАСТЕР ФУНКЦИЙ.

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

Следующие клавиши, находящиеся правее позволят пользователю соответственно:

создать диаграмму;

отобразить данные на географической карте;

поместить на рабочий лист графический объект.

Правее расположена клавиша масштабирования рабочего окна (также как и в Wordе)

Изменение масштаба окна

В раскрывающемся окне Масштаб, выберите нужный размер или введите число от 10 до 400. Такую процедуру можно выполнить по отношению только к выделенному блоку.

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

8. Процедура выделения диапазона ячеек

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

Активной будет та ячейка, которая была отмечена курсором

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

нажать Ctrl и не отпуская её нажать левую клавишу «мыши»

Выделение всего рабочего листа

курсор поставить в левый верхний пустой прямоугольник над номером первой строки

При работе с помощью клавиатуры нужно нажать Ctrl+Shift+Пробел.

9. Использование ссылок в Excel

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

Примеры: А4, — ячейка на пересечении столбца А и четвёртой строки.

A1:A5 — диапазон ячеек с А1 по А4.

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

Ячейка, содержащая формулу, называется зависимой ячейкой — ее значение зависит от значения другой ячейки.

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

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

Ссылка на активную ячейку выводится на экран в специальном поле «Имя», расположенном слева от строки формул.

Рассмотрим формулу для вычисления корней квадратного трехчлена ax 2 + bx + c.

Когда коэффициенты а, b и с расположены в ячейках А1, В1 и С1, формулы для вычисления корней имеют следующий вид:

= (-В1 + КОРЕНЬ (В1*В1—4*А1*С1))/2/А1

= (-В1 — КОРЕНЬ (В1*В1—4*А1*С1))/2/А1

Когда коэффициенты а, b и с расположены на листах 7, 8 и 9 (все в ячейках А1), формулы для вычисления корней имеют вид:

= (- Лист 8! А1 + КОРЕНЬ (Лист8! А1^ 2-4*Лист7! А1*Лист9! А1))/2/Лист7! А1

= (- Лист 8! А1 — КОРЕНЬ (Лист8! А1^2-4* Лист7! А1*Лист9! А1))/2/Лист7! А1

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

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

Иногда удобнее вместо ссылки на ячейку (в поле «Имя») ввести конкретное название, имя данной ячейки. Например, «Вклад» или «Актив». Такие ячейки удобнее использовать в формулах, они понятнее при чтении. При использовании данных из разных листов книги, конкретные имена ячеек тоже являются более удобными. На рисунке ячейка А1 переименована в ячейку «Актив»

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

При создании формулы ссылки обычно изменяются относительно расположения ячейки, содержащей формулу. На рисунке ячейка B3 содержит формулу =С2. Ячейка С2 находится на одну ячейку выше и правее ячейки B3. Такая ссылка называется относительной.

При копировании формулы, которая содержит относительную ссылку, и вставке её в другое место, ссылки будут указывать на другие ячейки. В следующем примере формула из ячейки B3 копируется в ячейку B4. В ячейке B4 будет уже содержаться формула =С3, которая опять ссылается на одну ячейку выше и правее ячейки B4.

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

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

Например, если имеется формула, умножающая содержимое ячейки A5 на ячейку C1 (=A5*C1), то при копировании формулы в другую ячейку изменятся обе ссылки. Для создания абсолютной ссылки на ячейку C1, поставьте знак доллара ($) перед той частью, которая не должна изменяться. Чтобы создать абсолютную ссылку на ячейку C1, поместите знак доллара таким образом:

Обратите внимание на то, что знак $ стоит и перед номером строки и перед именем столбца.

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

При перемещении формулы ссылки, расположенные внутри формулы, не изменяются.

Циклические ссылки

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

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

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

Как работать с формулами в Microsoft Excel: инструкция для новичков

В Excel предусмотрены стандартные функции, которые могут быть использованы в формулах. Это математические, логические, текстовые, финансовые и другие функции. Однако, на экзамене Вам могут встретиться только самые простые функции: СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение).

Полезные сведения → Как объединить ячейки → Как вставить значения → Аргументы функции → Работа с форматами → Функция ЕСЛИ → Как удалить пробелы → Функция впр vlookup→ Работа с таблицами