3. Вставьте новые ячейки, строки, столбцы.

Вставка новых ячеек на рабочем листе выполняется при помощи команд из меню Вставка. Чаще всего требуется вставка дополнительной строки или столбца. Для этого в указанном меню присутствуют команды Строка и Столбец, которыми мы пользовались в одном из предыдущих уроков. Новая строка всегда вставляется над текущей ячейкой таблицы, а столбец -- слева от нее. Обратите внимание, что формат ячеек вставленной строки будет таким же, как у ячеек, которые находятся выше вставленных. Соответственно столбцы перенимают формат у стоящих слева полей. Поскольку вставляются пустые ячейки, то значения и формулы не переносятся. Для вставки нескольких строк выделите столько строк, сколько вы хотите вставить, а затем выполните команду Строка. Точно так же можно вставить несколько столбцов. Если выделить диапазон ячеек и выполнить команду Вставка/Ячейки, то на экране появится диалог, изображенный на рис. 7.3. С помощью опций этого диалога можно указать, куда сдвигать выделенные ячейки: вправо, вниз или должны быть вставлены целиком строка или столбец.

рис. 7.3

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

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

При операции вставки новая строка таблицы появляется на экране плавно, и результат вполне очевиден. Аналогичным образом отражается выполнение и других операций, например удаления или переноса строки. Стоит обратить внимание еще на две важные команды, которые присутствуют в меню Правка. Это команды Отменить и Повторить. Если вы случайно удалили нужные ячейки или скопировали содержимое в неправильное место -словом, если вы ошиблись, отмените результат выполнения последней команды с помощью команды Правка/Отменить. В зависимости от того, что вы отменяете, изменяется и название команды Отменить. Например, для отмены вставки меню Правка команду Отменить вставку (рис. 7.4).

Отменить неправильную команду можно комбинациями [Ctrl-Z] или [Alt-Backspace], a также нажатием на кнопку, которая находится на стандартной панели инструментов. При необходимости вы сможете сделать "откат" своих действий на какой угодно уровень. Команда Правка/Повторить позволяет восстановить отмененное действие или повторить последнюю выполненную команду. Для быстрого выбора команды повтора воспользуйтесь комбинацией [Ctrl-Y]). Если при этом вы услышите звуковой сигнал, значит, программа не может повторить последнюю команду.

Вы выполнили на отметку «Удовлетворительно»

Дополнительные задания.

Задание 7.1 Составьте таблицу расчета премии за экономию горючесмазочных материалов (ГМС).

Рис.7.5 Исходные данные дополнительного задания 7.1

Формула для расчета.

Премия = базовая ставка*0,30 при условии, что План расходования ГМС > Фактически израсходовано ГМС.

Задание 7.2. Составьте таблицу Сведения о задержках книг за январь 2006 год

рис. 7.6 Исходные данные дополнительного задания 7.1

Содержание отчета

Номер практического занятия, тема.

Цель занятия.

Ход работы.

Ответы на контрольные вопросы.

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

1 .Как очистить ячейки? Для этого: выделить нужный диапазон и нажать клавишу Del. Выделить блок ячеек, выполнить команду Правка/Очистить и в открывшемся списке выбрать нужную команду.

2. Как удалить строки? Выделить строки, а затем выполнить соответствующую команду из контекстного меню.

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

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

Практическая работа №8

Тема: Расчеты в Excel

Цель работы: Научиться вводить и редактировать формулы.

Пояснение к занятию.

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

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

Ссылки представляют собой включаемые в формулу адреса ячеек или диапазоны ячеек. Ссылки на ячейки задаются обычным образом, т. е. в виде Al, Bl, C1. Например, для того чтобы получить в ячейке A3 сумму ячеек Al и А2, в нее достаточно ввести формулу =А1+А2. Диапазон ячеек, который, как уже отмечалось, представляет собой некоторую прямоугольную область рабочего листа, однозначно определяется адресами ячеек, расположенными в диаметрально противоположных углах диапазона. Разделенные символом ":" (двоеточие), эти две координаты составляют адрес диапазона. На рис. 29 выделен диапазон, имеющий адрес C3:D7.

Таким образом, чтобы определить в формуле ссылку на этот диапазон, нужно ввести в нее адрес в указанном виде. Например, чтобы получить сумму значений ячеек из указанного диапазона, используйте формулу =СУММ(СЗ:07). В частном случае, когда диапазон состоит целиком из нескольких столбцов, например от В до Е, его адрес записывается в виде В:Е. Аналогично если диапазон целиком состоит из строк с 5-й по 18-ю, то он имеет адрес 5:18. Кроме того, при записи формул можно использовать объединение нескольких диапазонов и/или ячеек, разделяя их символом ";" (точка с запятой), например C3:D7;E5;F3:G7.

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

* выделите ячейку, в которую нужно ввести формулу;

* начните ввод формулы, нажав клавишу [=] (равно);

* щелкните мышью на ячейке A1;

* введите символ "+";

* щелкните мышью на ячейке В2;

* закончите ввод формулы, нажав клавишу [Enter].

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

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

* дважды щелкните мышью на ячейке, чтобы корректировать формулу непосредственно в этой ячейке;

* выберите ячейку и нажмите клавишу [F2];

* выберите ячейку и щелкните мышью в строке формул.

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

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

Арифметические операторы используются для обозначения основных математических операций над числами. Результатом выполнения арифметической операции всегда является число. Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ. С адресными операторами вы только что познакомились. Использование остальных мы рассмотрим в дальнейших уроках.

Функции в Excel в значительной степени облегчают проведение расчетов и взаимодействие с электронными таблицами. Наиболее часто применяется функция суммирования значений ячеек. При оформлении нашей таблицы по расчету заработной платы мы тоже использовали ее для подсчета итогов по строкам и столбцам. Напомним, что эта функция имеет название СУММ, а в качестве аргументов служат диапазоны суммируемых ячеек (рис. 8.2). Кроме того, для ускоренного вызова этой функции вы можете воспользоваться кнопкой автосуммирования, которая находится на стандартной панели инструментов.

рис. 8.2

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

Задание

Самостоятельно изучить методические рекомендации по проведению конкретного практического занятия.

Подготовить формы отчета.

Подготовить ответы на контрольные вопросы.

Ход работы:

1. Функция CP3HAЧ()Q

Кроме функции суммирования Excel позволяет обрабатывать данные с помощью более 400 функций. Для примера воспользуемся функцией CP3HAЧ()Q для подсчета среднего значения окладов в ячейке С9. Любую функцию можно ввести непосредственно в строке формул с помощью клавиатуры, однако для упрощения ввода и снижения количества ошибок в Excel имеется Мастер функций. Вызвать окно диалога Мастера можно следующими способами:

* с помощью команды Вставка/Функция;

* комбинацией клавиш [Shift-F3];

Первый диалог Мастера функций организован по тематическому принципу. В левом списке находятся названия групп. Щелкнув мышью на нужной группе, в правой части вы получите список имен функций, содержащихся в данной группе. Например, функцию СУММ() вы сможете отыскать в группе "Математические", а в группе "Дата и время" находятся функции ЧИСЛО(), МЕСЯЦ(), ГОД(), СЕГОДНЯО, которыми мы пользовались при оформлении шаблона. Кроме того, для ускорения выбора функций Excel "помнит" имена 10 недавно использованных функций в соответствующей группе. Необходимая нам функция CP3HAЧ() содержится в группе "Статистические". Обратите внимание, что в нижней части окна отображается краткая справка о назначении функции и ее аргументах. Если вы нажмете кнопку "Справка" в нижней части диалогового окна, то Excel откроет соответствующий раздел Справочной системы. Теперь нажмите кнопку "Далее>" для перехода ко второму диалогу Мастера функций. В этом диалоге необходимо задать аргументы функции для вычисления среднего значения. В нашем примере достаточно указать диапазон СЗ:С7, для которого вычисляется среднее значение. Этот диапазон можно ввести непосредственно в поле первого аргумента, а можно выделить с помощью мыши.

Обратите внимание, что Мастер сразу же отображает значение функции в правом верхнем углу окна. После нажатия кнопки "Готово" в ячейке С9 появится среднее значение окладов всех сотрудников (рис. 8.3).

рис. 8.3

Если при вводе функции аргументы были заданы неправильно, то после нажатия кнопки "Готово" появится соответствующее сообщение об ошибке (рис. 8.4).

рис. 8.4

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

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

2. Рассчитать с помощью этой функции среднее значение для диапазона СЗ:С7

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

рис. 8.5

Вы выполнили на отметку «Удовлетворительно»

Дополнительные задания.

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

Заполните таблицу в Excel.

День недели	Прогноз	Совет
Понедельник	Пасмурно
Вторник	Солнечно
Среда	Облачно
Четверг	Пасмурно
Пятница	Ветрено
Суббота	Пасмурно
Воскресенье	Солнечно

рис. 8.6

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

Задание 8.2 В магазине Монетка, если у покупателя есть дисконтная карта, то ему делают скидку в 5%. Создайте следующую таблицу (рис.8.7)

рис. 8.7

Содержание отчета

Номер практического занятия, тема.

Цель занятия.

Ход работы.

Ответы на контрольные вопросы.

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

1. Что представляют собой ссылки? Ссылки представляют собой включаемые в формулу адрес ячеек или диапазоны ячеек. Задаются виде: А1, В1, С1.

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

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

4. Что произойдет с формулой, если удалить ячейку, на которую ссылается эта формула? Ошибка.

Если при вводе функции аргументы были заданы неправильно, то после нажатия кнопки "Готово" появится соответствующее сообщение об ошибке

5. Как найти максимальное, минимальное или среднее значение одного или нескольких диапазонов? Кроме функции суммирования Excel позволяет обрабатывать данные с помощью более 400 функций. Для примера воспользуемся функцией CP3HAЧ()Q для подсчета среднего значения окладов в ячейке С9. Любую функцию можно ввести непосредственно в строке формул с помощью клавиатуры, однако для упрощения ввода и снижения количества ошибок в Excel имеется Мастер функций. Вызвать окно диалога Мастера можно следующими способами:

* с помощью команды Вставка/Функция;

* комбинацией клавиш [Shift-F3];

Первый диалог Мастера функций организован по тематическому принципу. В левом списке находятся названия групп. Щелкнув мышью на нужной группе, в правой части вы получите список имен функций, содержащихся в данной группе. Например, функцию СУММ() вы сможете отыскать в группе "Математические", а в группе "Дата и время" находятся функции ЧИСЛО(), МЕСЯЦ(), ГОД(), СЕГОДНЯО, которыми мы пользовались при оформлении шаблона. Кроме того, для ускорения выбора функций Excel "помнит" имена 10 недавно использованных функций в соответствующей группе. Необходимая нам функция CP3HAЧ() содержится в группе "Статистические". Обратите внимание, что в нижней части окна отображается краткая справка о назначении функции и ее аргументах. Если вы нажмете кнопку "Справка" в нижней части диалогового окна, то Excel откроет соответствующий раздел Справочной системы. Теперь нажмите кнопку "Далее>" для перехода ко второму диалогу Мастера функций. В этом диалоге необходимо задать аргументы функции для вычисления среднего значения. В нашем примере достаточно указать диапазон СЗ:С7, для которого вычисляется среднее значение. Этот диапазон можно ввести непосредственно в поле первого аргумента, а можно выделить с помощью мыши.

Практическая работа №9

Тема: Теория относительности адресов

Цель работы: Разобраться в основных способах адресации ячеек.

Пояснение к занятию.

Как вы уже знаете, ссылки на ячейки (или диапазоны ячеек) в формулах Excel задаются в виде адреса ячейки (или диапазона ячеек). Однако следует иметь в виду, что в Excel существуют два основных способа адресации ячеек: относительные и абсолютные ссылки. Относительная ссылка показывает, что адрес ячейки вычисляется в относительной системе координат с началом в текущей ячейке. Относительные ссылки имеют вид Al, B1 и т.п. Именно в таком виде мы задавали адреса ячеек в предыдущих уроках. В случае абсолютной ссылки адрес ячейки вычисляется в абсолютной системе координат и не зависит от текущей ячейки. Абсолютные ссылки имеют вид $А$1, $В$1 и т.д. Таким образом, для указания абсолютного адреса при записи формулы необходимо использовать символ $. Заметим, что способ ссылки не влияет на результат вычисления по формуле. Различия между относительными и абсолютными ссылками проявляются только при копировании формул из одной ячейки в другую. При перемещении или копировании абсолютные ссылки в формулах не изменяются, а относительные ссылки автоматически обновляются в зависимости от нового положения. При этом взаимосвязь между ссылками ячеек в новых формулах оказывается подобной взаимосвязи ячеек в исходной формуле. Поскольку в таблицах часто приходится копировать однотипные формулы, такой способ адресации оказывается очень полезным. По этой же причине в Microsoft Excel данный режим адресации используется по умолчанию, однако в некоторых случаях необходимо использовать абсолютную адресацию ячеек.

Задание

Самостоятельно изучить методические рекомендации по проведению конкретного практического занятия.

Подготовить формы отчета.

Подготовить ответы на контрольные вопросы.

Ход работы:

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

Напомним, что в этой таблице данные вводятся только в колонки "№ п/п", "Фамилия И.О.", "Оклад" и "Премия", а ячейки в остальных колонках рассчитываются по соответствующим формулам.

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

рис. 9.1

Затем для сотрудника Васильева в ячейку D5 введем формулу расчета премии =C5*D2. B результате премия станет равна 300, что составляет 20% от оклада 1500 (рис. 9.2).

рис. 9.2

Теперь введем формулу расчета премии для Семеновой в ячейку D6. Поскольку премия для нее рассчитывается по подобной формуле, то ее можно скопировать из предыдущей ячейки D5. Это можно сделать любым из описанных в предыдущих уроках способом. Однако после этой операции в ячейке D5 получится ошибочный результат. Это неудивительно, поскольку при копировании формулы Excel изменил адрес ячейки D2 на D3, чего в нашем случае делать не следовало (рис. 9.3).

2.Замена относительного адреса на абсолютный.

Чтобы избежать подобной ошибочной ситуации, в формуле ячейки D5 следует заменить относительный адрес D2 на абсолютный $D$2, что можно сделать непосредственно вводом символа $ в строку формулы, но лучше воспользоваться встроенной возможностью Excel. Для этого установите указатель на ячейку D5 и дважды нажмите кнопку мыши. Затем выделите в ячейке ссылку D2 и нажимайте клавишу [F4] до тех пор, пока ссылка не примет нужный вид $D$2. Для завершения редактирования нажмите клавишу [Enter]. Теперь при копировании формулы для Семеновой и остальных сотрудников все будет проходить корректно (рис. 9.4).

рис. 9.4

Следует отметить, что в Excel можно использовать смешанные ссылки, например А$1 или $A1. Тогда часть ссылки, не содержащая знака $, будет обновляться при копировании, а другая часть, со знаком $, останется без изменения. Например, А$1 означает, что при копировании формул будет изменяться только адресация строк ячеек, а $А1 - только столбца.

Дополнительные задания.

Задание 9.1 Магазин кожи и меха «Олимп» делает скидку на весь товар прошлого 2005 года в 10% рассчитайте новую цену со скидкой.( рис.9.5)



Товар	Модель года	Цена	Цена со скидкой
Шуба из ласки	2005	19543
Дубленка - стингер	2006	17000
Пальто воженное	2005	12000
Куртка - мехова	2006	9000
Сумка замшевая	2006	1500
Куртка кожаная	2005	15000
Шуба из норки	2006	76000

рис. 9.5

Задание 9.2. Подготовьте расчетно-платежную ведомость, как показано на рис. 9.6.

рис. 9.6

Оклад рассчитывается как произведение Минимальной заработной платы на Разряд. При этом используйте абсолютную ссылку на минимальную заработную плату.

Надбавка вычисляется с учетом стажа: тем, кто работает 5 и более лет к окладу +10%.

Начислено: Оклад + Надбавка;

На руки: Начислено- Подоходный налог.

Содержание отчета

Номер практического занятия, тема.

Цель занятия.

Ход работы.

Ответы на контрольные вопросы.

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

1. Что такое относительная адресация? Относительная ссылка показывает что адрес ячейки вычисляется в относительной системе координат с начала в текущей ячейке.

2. Что такое абсолютная адресация? При абсолютной ссылки текущей ячейке адрес ячейки вычисляется в абсолютной системе координат и не зависит от текущей ячейки. Абсолютные ссылки имеют вид $A$A, $B$1 и т.д.

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

4. Чем отличаются относительные ссылки от абсолютных? В абсолютных стоит знак $

5. Как объединить ячейки? Выделить ячейки нажать правую кнопку мыши объединить ячейки

Практическая работа №10

Тема: Именованные блоки

Цель работы: Научиться присваивать имена блокам ячеек.

Пояснение к занятию.

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

Задание

Самостоятельно изучить методические рекомендации по проведению конкретного практического занятия.

Подготовить формы отчета.

Подготовить ответы на контрольные вопросы.

Ход работы:

1. Присвойте имя ячейки

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

Теперь в формуле ячейки D5 можно заменить адрес ячейки $D$2 на ее имя. В результате формула примет вид =С5*Проц.премии.

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

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

2. Чтобы автоматически заменить все ссылки соответствующими именами:

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

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

* в списке "Применить имена" выберите необходимые имена.

Имена в Excel можно определять не только для отдельных ячеек, но и для диапазонов (в том числе несмежных). Для присвоения имени диапазон достаточно выделить, а затем ввести название в поле имени.

3. Кроме того, для задания имен диапазонов, содержащих заголовки, удобно использовать специальную команду Создать в меню Вставка/Имя. Воспользуемся этой командой для создания имени "Оклад" в соответствующем столбце нашей таблицы. Для этого выделим его вместе с заголовком, как показано на рис. 10.3, и выполним данную команду, указав в диалоговом окне опцию "В строке выше".

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

Кстати, этим же списком можно воспользоваться для быстрого перехода на ячейку (или диапазон) с нужным именем. Аналогично можно присвоить имена для остальных столбцов таблицы. Заметим, что имя "Оклад" можно использовать в формуле расчета премии. Вставив в ячейку D5 формулу Оклад*Проц.премии, убедимся, что расчет выполняется правильно. Такая запись формулы читается легче и намного понятнее, чем формула вида =C5*$D$2, поэтому применение имен в значительной степени облегчает работу с таблицами, в которых используется большое количество формул ссылок. Более того, эта формула корректно копируется и для других сотрудников, поскольку при вычислении премии для каждого работающего значение оклада берется из соответствующей строки диапазона "Оклад". Для изменения формулы имени воспользуйтесь командой Присвоить в меню Вставка/Имя (рис. 10.5).

Выберите в диалоговом окне имя, формулу которого вы хотите изменить, а затем исправьте содержимое поля "Формула" в нижней части окна. Чтобы удалить имя, выберите его в списке и нажмите кнопку "Удалить".

Вы выполнили на отметку «Удовлетворительно»

Дополнительные задания.

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

Расчет температуры	максимальное значение	Среднее значение	минимальное значение
Время суток	месяцы
	ян.	фев.	март	апр.	май	июнь	июль
Утро
День
Вечер
Ночь
Среднее значение температуры

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

Содержание отчета.

Номер практического занятия, тема.

Цель занятия.

Ход работы.

Ответы на контрольные вопросы.

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

Как быстро присвоить имя ячейки?

Как автоматически заменить все ссылки соответствующими именами?

Как удалить имя блока?

4. Как перейти от одной рабочей книги к другой?

5. Из скольких строк и столбцов состоит лист рабочей книги EXCEL?

Практическая работа №11

Тема: Работа над ошибками

Цель работы: Научиться строить выражения в формулах.

Пояснение к занятию.

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

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

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

Задание

Самостоятельно изучить методические рекомендации по проведению конкретного практического занятия.

Подготовить формы отчета.

Подготовить ответы на контрольные вопросы.

Ход работы:

1. Найти ошибки в формулах и ячейках данных.

Для примера рассмотрим небольшую таблицу (рис. 11.1).

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

Теперь укажем на ячейку СЗ и выполним команду Влияющие ячейки из меню Сервис/Зависимости или при помощи соответствующей кнопки на панели зависимостей. Синими стрелками Excel покажет, что на результат вычисления ячейки СЗ оказывают, влияние ячейки В2 и ВЗ (рис. 11.3), что соответствует действительности.

Команда Зависимые ячейки является обратной операцией и показывает, на какие ячейки влияет данная ячейка. В нашем примере значение ячейки СЗ влияет на ячейку С4 (рис. 11.4)

Полезно знать, что для перехода на ячейку, находящуюся на другом конце стрелки, достаточно поместить указатель на стрелку и дважды щелкнуть кнопкой мыши. Для очистки всех стрелок на рабочем листе служит команда Убрать все стрелки Теперь запишем в ячейку ВЗ значение 0. В результате в ячейках СЗ и С4 появится сообщение об ошибке #ДЕЛ/0!, которое говорит о том, что произошло деление на ноль. Для определения источника ошибки выделим ячейку С4 и воспользуемся командой Источник ошибки (рис.11.5)

Красные стрелки указывают на ячейки, которые являются источниками ошибок. В нашем случае такой ячейкой будет СЗ, поскольку ошибка первоначально возникла именно в ней. Синие стрелки указывают на ячейки В2 и ВЗ, которые влияют на ошибочные вычисления и служат потенциальными источниками ошибки для ячейки СЗ. Теперь очевидно, что деление на ноль произошло из-за нулевого содержимого ячейки ВЗ.

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

2. Устраните циклические ссылки.

В завершение урока рассмотрим методы, позволяющие устранить циклические ссылки. Циклической ссылкой называется последовательность ссылок, которая создается в случае, когда формула ссылается, прямо или косвенно, на свою ячейку. Например, если в ячейку А1 ввести =А1+1, то такая формула будет содержать циклическую ссылку. В режиме обычных вычислений Excel подобные формулы не обрабатывает и выдает соответствующее предупреждение. Однако такие формулы могут случайно остаться на рабочем листе, и сообщение о присутствии циклических ссылок будет появляться каждый раз при загрузке рабочей книги. Чтобы устранить эту ошибку, посмотрите на строку состояния. Если рабочий лист содержит циклические ссылки, то в строке состояния после слова "Цикл" будет отображаться адрес одной из ячеек, входящих в замкнутую последовательность. Перейдите в указанную ячейку и в меню Правка выберите команду Перейти. В диалоговом окне нажмите кнопку "Выделить", установите переключатель в положение "Влияющие ячейки" или "Зависимые ячейки" и укажите положение "на всех уровнях" (рис. 11.6).

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

Вы выполнили на отметку «Удовлетворительно»

Дополнительные задания.

Задание 11.1 В MS Excel.Создайте таблицу и отсортируйте таблицу по Части света (по возрастанию), не нарушая нумерации. Используя функцию ЕСЛИ выведите те страны средняя продолжительность жизни которых более 80 лет.

№	Страна	Средняя продолж-ть жизни	Часть света
1	Андорра	83,49	Европа
2	Сан-Марино	81,43	Европа
3	Япония	80,94	Азия
4	Сингапур	80,42	Азия
5	Австралия	80,13	Австралия и Океания
6	Швейцария	79,99	Европа
7	Швеция	79,97	Европа
8	Канада	79,83	Северная Америка
9	Исландия	79,8	Европа
10	Италия	79,4	Европа
11	Израиль	79,02	Азия
12	Германия	78,42	Европа
13	Бельгия	78,29	Европа
14	Великобритания	78,16	Европа

рис.11.7

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

Содержание отчета

Номер практического занятия, тема.

Цель занятия.

Ход работы.

Ответы на контрольные вопросы.

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

Как определить зависимость между ячейками?

Как определить источник ошибки?

Что называется циклической ссылкой?

Как Excel отличает число от текста, текст от функции?

Практическая работа №12

Тема: Работа со списками

Цель работы: Научиться работать с базами данных.

Пояснение к занятию.

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

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

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

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

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

* все ячейки столбца должны содержать однотипные данные. С этой целью рекомендуется применять один формат для всех ячеек столбца;

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

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

Для поиска данных воспользуйтесь командой Найти, которую можно вызвать из меню Правка или комбинацией [Ctrl-F]. B поле "Что" диалогового окна (рис. 12.1) необходимо ввести шаблон, по которому будет производиться поиск последовательности символов на рабочем листе. В шаблон поиска можно включать любые буквы, цифры, знаки препинания.

Кроме того, в шаблоне можно использовать символы подстановки "*" или "?". Символ "*" означает, что на его месте в шаблоне поиска может находиться любая последовательность символов, а на месте "?" - любой единичный символ.

Задание

Самостоятельно изучить методические рекомендации по проведению конкретного практического занятия.

Подготовить формы отчета.

Подготовить ответы на контрольные вопросы.

Ход работы:

1. Ввести в шаблоне "А*" Будут найдены все слова, начинающиеся на "А", а шаблону "Ию?ь" соответствуют слова "Июнь" и "Июль". Если вы знаете, в каких столбцах или строках находится нужная информация, то процесс поиска можно значительно ускорить. Для этого перед вызовом функции поиска выделите диапазон (например, столбцы), в котором следует производить поиск данных.

2. Если в книге отсутствует выделение ячеек или листов, то выполняется просмотр всех ячеек текущего листа. Кроме того, в раскрывающемся списке "Просматривать" окна поиска можно задать направление поиска (по строкам или по столбцам), а в поле "Область поиска" указать элементы рабочего листа, в которых следует искать данные: в формулах, значениях или примечаниях.

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

Процесс поиска начинается после нажатия кнопки "Найти далее". Очередная ячейка, которая содержит удовлетворяющую шаблону последовательность символов, становится активной. Чтобы изменить направление поиска (например, вернуться к предыдущей найденной ячейке), нажмите клавишу [Shift] и, не отпуская ее, кнопку "Найти далее". Если в окне поиска нажать кнопку "Заменить", то произойдет переход в режим замены данных, который можно вызвать командой Заменить в меню Правка или комбинацией [Ctrl-H].

4. Чтобы произвести замену данных, удовлетворяющих шаблону поиска, заданному в поле "Что" диалогового окна замены (рис. 12.2), введите нужную последовательность в поле "Заменить на".

5. Для удаления искомых последовательностей нужно оставить поле "Заменить на" пустым. Непосредственная замена происходит при нажатии кнопки "Заменить". Если вы желаете выполнить замену в автоматическом режиме по всему рабочему листу, используйте кнопку "Заменить все".

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

7. Строки нашей таблицы отсортируйте по фамилиям сотрудников.

Установим курсор на любую ячейку списка и выполним команду Сортировка. В открывшемся диалоговом окне необходимо установить сортировку по возрастанию для поля "Фамилия И.О." (рис. 12.3).

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

Однако при этом сохранился и порядковый номер, поскольку это поле также является элементом списка, и Excel автоматически включил его в диапазон сортировки (рис. 52). Чтобы исправить эту "оплошность", достаточно выделить столбец "№ п/п", выбрать режим сортировки в пределах указанного выделения (рис. 12.5), а затем выполнить сортировку по возрастанию для указанного поля.

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

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

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

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

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

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

Как и Microsoft Word, Excel благодаря режиму автозамены может автоматически исправлять опечатки при наборе текста. Например, если ввести в ячейку текст "опсле", то Excel автоматически заменит его на "после". Добавить или изменить элементы и параметры автозамены можно с помощью команды Автозамена в меню Сервис (рис. 12.9). Более того, автозамену можно также использовать для ускорения ввода часто встречающихся текстовых последовательностей.

Вы выполнили на отметку «Удовлетворительно»

Дополнительные задания.

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

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

В ячейке A4 задайте текущую дату функцией СЕГОДНЯ. Исходные данные представлены на рис.12.11

Движение Товара	Отделы магазина
	Одежда	Трикотаж	Ткани	Обувь	Парфюмерия	Всего по магазину
Остаток на начало дня	13875,5 р.	9785,0 р.	5678,5р.	10670р.	2579 р.	?
Поступило за день	27800,3р.	5670,7 р.	4300,0р.	3900,0р.	1501 р.	?
Продано за день	28600,5р.	10345,0р.	4890,6р.	8760 р.	1384 р.	?
Остаток на конец дня	?	?	?	?	?	?

рис.12.11. Исходные данные для дополнительного задания 12.2

Содержание отчета

Номер практического занятия, тема.

Цель занятия.

Ход работы.

Ответы на контрольные вопросы.

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

Что представляет собой список?

Что такое база данных?

Какие существуют рекомендации по организации данных?

Чтобы найти данные какой командой нужно воспользоваться?

Как произвести замену данных?

Что можно сделать с помощью операции Сортировка данных?

Что такое режим Автоввода?

Что такое режим Автозамены?

Практическая работа №13

Тема: Форма для работы со списками

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

Пояснение к занятию.

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

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

При вводе и правке данных в форме сделанные изменения вносятся в ячейки листа, соответствующие полям текущей записи. Для перехода к следующей или предыдущей записи можно использовать кнопки "Далее" и "Назад", а также полосу прокрутки, которая находится в середине формы. Для добавления новой записи нажмите кнопку "Добавить" и введите значения полей. При повторном нажатии на эту кнопку новая запись будет добавлена в конец списка. Обратите внимание, что при вставке новой записи в нее автоматически копируются формулы из предыдущей строки. Для удаления записи из списка используйте кнопку "Удалить". Форму удобно использовать для поиска записей, удовлетворяющих заданным критериям (условиям). С этой целью нажмите кнопку "Критерии" и задайте условия поиска или условия сравнения. Теперь при помощи кнопок "Далее" или "Назад" можно выполнять переход по записям, которые соответствуют указанным критериям.

Задание

Самостоятельно изучить методические рекомендации по проведению конкретного практического занятия.

Подготовить формы отчета.

Подготовить ответы на контрольные вопросы.

Ход работы:

1. Введите в поле "Оклад" условие >1000 (рис. 13.2)

То переход будет осуществляться только по тем записям, у которых в поле "Оклад" находится значение больше 1000.

2. Поработайте с автофильтром.

Фильтры позволяют просматривать только те записи в таблице, которые удовлетворяют определенным условиям. При этом записи, которые не удовлетворяют условиям, скрыты во время работы фильтра. В Microsoft Excel доступны два способа фильтрации данных: автофильтр и расширенный фильтр.

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

Работать с автофильтром довольно просто. Например, чтобы отобрать только те записи, в которых значение оклада больше 1000, следует щелкнуть на кнопке у заголовка "Оклад", выбрать в открывшемся списке "Условие...", а затем ввести критерий в появившемся окне (рис. 13.4). После нажатия кнопки "ОК" фильтр вступит в действие (рис. 13.5).

Если автофильтр активен, то кнопка открытия списка окрашивается в синий цвет, а в строке состояния появляется соответствующее сообщение. Номера строк также окрашиваются в синий цвет, причем номера не попавших в фильтр строк не отображаются. Для одного столбца можно определить несколько условий поиска. Например, чтобы отобрать записи, у которых значение оклада находится в диапазоне от 1000 до 1400, необходимо задать условие, как показано на рис. 13.6. При этом в условиях поиска можно использовать символы подстановки "*" и "?". Условия могут быть объединены одним из двух способов: по "И" либо по "ИЛИ". Чтобы отфильтровать заданное число строк с максимальными или минимальными значениями ячеек текущего столбца, воспользуйтесь условием отбора "Первые 10...". Это особенно может пригодиться при составлении различного рода рейтингов.

Наконец, записи можно отфильтровать просто по конкретному значению, которое присутствует в столбце, а также по пустому иди непустому значению.

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

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