Учебно-практическое пособие «офисные компьютерные технологии». Часть II (стр. 5 )

ü  В таблице 1:

·  Переместите строки "Тюмень" и "Владивосток" в новое место между строками "С. Петербург" и "Н. Новгород".

·  Используя операцию заполнения смежных блоков, заполните пустую строку (созданную при выполнении задания из предыдущего параграфа) данными из строки "Новосибирск".

·  Очистите одну из строк "Новосибирск", а затем удалите ее.

ü  Создайте таблицу 2, содержащую сведения о руководителях филиалов. Шапка таблицы представлена ниже. При создании таблицы столбцы "Филиал" и "Руководитель филиала" скопируйте из таблицы 1.

Сведения о руководителях филиалов.

Таблица 2.

Руководитель филиала	Филиал	Год рождения	Стаж работы	Дом. адрес	Дом. телефон

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

8.  С чего начать создание новой рабочей книги?

9.  Как ввести данные в ячейку? Что произойдет, если вводимые данные не поместятся в ячейку?

10.  Как сохранить \ открыть файл рабочей книги? Что такое свойства файла и для чего они используются?

11.  Какие степени защиты файлов от несанкционированного доступа предусмотрены в Excel? Как установить \ снять пароль?

12.  Каким образом осуществляется поиск файла, как задаются условия поиска?

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

14.  Как изменить размеры ячеек? Что следует сделать для возвращения ячейкам их стандартных размеров?

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

16.  Каким образом можно объединить ячейки рабочего листа? Какой адрес имеет объединенная ячейка?

17.  Как переместить \ скопировать блоки таблицы? В чем преимущество операции заполнения смежных блоков перед копированием? Чем операция очистки отличается от операции удаления, как ее выполнить?

3.  ЭЛЕМЕНТАРНЫЕ РАСЧЕТЫ В EXCEL

3.1.  Общие сведения

3.1.1.  Операции Excel

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

Формула начинается со знака "=", в ее состав могут входить соединенные знаками операций константы, заданные своими адресами или именами ячейки, функции Excel. Для изменения приоритета выполнения операций допускается использование скобок. Список операций, определенных в Excel, приведен в таблице 3.1.

Операции Excel

Таблица 3.1.

Знак операции	Описание	Пример
Арифметические операции
+	сложение	=E4+3,28
-	вычитание	=D5-45
*	умножение	=15*D5*G7
/	деление	=F1/J3
%	взятие одного процента	=L3%
^	возведение в степень	=4^0,5
Операции отношения
=	равно	=A1=3
>	больше	=5>3
<	меньше	=D4<2
>=	больше или равно	=D4>=15
<=	меньше или равно	=F2<=-8
<>	не равно	=2<>3
Операции связи
:	диапазон	=СУММ(A3:D8)
;	объединение	=СУММ(C1;H3;A5)
(пробел)	пересечение	=СУММ(A1:E10 C3: G5)
Операция конкатенации (сцепления) строк текста
&		=“В ячейке D7 записано “&D7

3.1.2.  Вставка и редактирование формул

Максимальная длина формулы, которую можно вставить в ячейку, составляет 1024 символа. Для ввода формулы в активную ячейку:

·  Начните набор формулы, нажав клавишу "=".

·  Введите формулу.

·  Закончите ввод формулы, нажав клавишу Enter или щелкнув мышью по кнопке с галочкой в строке формул.

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

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

·  Сделайте ячейку активной и нажмите F2.

·  Сделайте ячейку активной и щелкните мышкой по строке формул.

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

¿  Практикум

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

3.1.3.  Установка режима вычислений

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

·  Выполните команду Параметры меню Сервис.

·  Перейдите на панель Вычисления.

·  Установите режим Вручную и включите флажок Пересчет перед сохранением.

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

3.2.  Автосуммирование по столбцам и строкам

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

Для автоматической вставки формулы суммирования в ячейку:

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

·  Щелкните по кнопке с изображением знака суммы.

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

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

·  сделайте текущей ячейку, в которую следует вставить формулу;

·  щелкните по кнопке S - Excel выделит интервал для суммирования бегущей линией;

·  если вас этот интервал не устраивает, выделите мышкой новый интервал;

·  нажмите Enter.

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

¿  Практикум

ü  В табл. 1, используя кнопку автоматической суммы:

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

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

·  Подведите общий итог, сначала просуммировав итоги по кварталам, затем по филиалам. Проверьте сходимость результата.

ü  Просмотрите вид формул, вставленных в ячейки столбца "Итого по филиалам" и строки "Итого по кварталам".

3.3.  Формат данных, основные типы форматов

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

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

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

·  Инструмент Формат с разделителями устанавливает для ячейки числовой формат с целой и дробной частями, причем в целой части сотни, тысячи, миллионы и т. д. будут отделены пробелами, например: ,4356.

·  Инструмент Денежный формат устанавливает для ячейки представление числа в форме денежной единицы, заданной в стандарте Windows (диалог Язык и стандарты панели управления Windows).

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

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

¿  Практикум

ü  Вставьте в одну из свободных ячеек рабочего листа число 17236,3475.

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

· ,348

· ,35р.

·  1750%

ü  Задайте денежный формат для ячеек таблицы 1, содержащих числовые данные.

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

3.4.  Имя ячейки

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

·  выделите область электронной таблицы, которой присваиваете имя;

·  выполните команду Имя меню Вставка, затем команду Присвоить;

·  в поле Имя наберите имя;

·  выполните ОК.

Имя может быть в длину до 255 символов и содержать буквы, цифры, символы подчеркивания, точки, вопросительные знаки, символы “\” и “/”. Первым символом должна быть буква, или символ подчеркивания, или обратная косая черта “\”. Допускается кириллица. Не допускаются имена, совпадающие с адресами ячеек.

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

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

3.5.  Абсолютная и относительная адресации

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

Пусть ячейка A1 содержит формулу =B5/C8. Скопируем формулу в ячейку В3 (адрес строки увеличится на 2, а адрес столбца увеличится на 1). Соответственно изменятся адреса влияющих ячеек, входящих в формулу. Т. е. в ячейке B3 мы получим формулу =C7/D10.

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

В нашем примере, если в формуле ячейки А1 записать =B$5/$C8, то после переноса в ячейке B3 будем иметь: =C$5/$C10.

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

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

Таблица 2.

	А	В	С	D
1	Район	Кол-во безработных	%	%
2	Ленинский	15 678	28,96	28,96
3	Промышленный	20326	#ДЕЛ/0!	37,55
4	Заднепровский	18128	#ДЕЛ/0!	33,49
5	Всего по городу	54 132	#ДЕЛ/0!	100

Для подсчета процентов по Ленинскому району в ячейку С2 вставим формулу =B2/B5*100. Для подсчета по остальным районам распространим методом заполнения смежных блоков формулу из ячейки С2 в ячейки С3,С4 и С5. В результате, в этих ячейках мы получим сообщение Excel о том, что произошло деление на ноль. В самом деле, при переносе формулы из ячейки С2 в ячейку С3, она примет вид =B3/B6*100. А ячейка В6 пуста, что равносильно содержанию в ней нулевого значения.

Для корректного выполнения расчетов в данном случае следует воспользоваться абсолютной адресацией для ячейки В5. Вставим в ячейку D2 формулу =B2/$B$5*100. Теперь после распространения формулы на ячейки D3, D4, D5 результат будет правильным.

В формулах для подсчета процентов можно вместо адреса $B$5 использовать имя ячейки (конечно предварительно назначенное), например Всего. Тогда формула будет иметь вид =В2/Всего*100.

¿  Практикум

ü  Отработайте рассмотренный выше пример с расчетом процентов.

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

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

18.  Каким образом осуществляется вставка и редактирование формул?

19.  Назовите операции, относящиеся к арифметическим операциям, операциям отношения и связи.

20.  Какое действие выполняет операция конкатенации?

21.  Как выполнить автосуммирование по столбцам и строкам, не прибегая к операции вставки формулы?

22.  Каким образом можно быстро распространить автосуммирование на большое количество строк \ столбцов?

23.  Чем отличаются ручной и автоматический режимы вычислений, как их установить?

24.  Что подразумевается в Excel под термином "формат данных"?

25.  Какие вы знаете типы форматов? Как их установить?

26.  Что такое имя ячейки? Как оно присваивается и как его можно использовать?

27.  Чем отличаются абсолютные и относительные адреса ячеек?

28.  Когда целесообразно применять абсолютные, а когда относительные адреса?

29.  Имя представляет собой абсолютный или относительный адрес?

4.  создание диаграмм

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

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

4.1.  Создание диаграмм

¿  Практикум

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

Курс доллара

Банк	Сбербанк	СБС-Агро	Фермер	Рос. кредит	Автобанк
Курс покупки, руб.	29,2	29,2	29,3	29,1	29,1
Курс продажи, руб.	29,5	29,55	29,89	29,37	29,35

Порядок выполнения задания