Excel — это несложно (7 шагов)

Microsoft Excel 1

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

Загрузка Microsoft Excel
Пуск / Программы / Microsoft Office / Microsoft Excel 2003

Интерфейс Microsoft Excel

































Основные понятия
Книга – основной составной элемент программного окружения Excel. Файл книги Microsoft Excel обычно имеет расширение «.xls» и отображается характерным ярлыком ().
Лист – часть книги, служащая для размещения информации.
Ячейка – элемент листа для хранения данных и формул.
Строка формул – текстовое поле для ввода, редактирования и просмотра данных в активной ячейке.


Перемещение по рабочему листу
Перемещение в пределах рабочего листа можно осуществлять следующим образом:
Клавишами перемещения курсором
Щелчком мыши на соответствующей ячейке
С помощью вертикальной и горизонтальной полос прокруток
Вводом адреса ячейки в поле адреса (имя столбца набирается латинским шрифтом)
Нажатием определенных клавиш или их комбинации:
[Page Down] – переход на страницу вниз
[Page Up] – переход не страницу вверх
[Ctrl + Home] – переход в начало рабочего листа
[Ctrl + End] – переход на последнюю ячейку заполненной строки и столбца
[Ctrl + Backspace] – возврат к активной ячейке (при прокрутке документа)


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











Для выделения группы нескольких несмежных ячеек (блоков) нужно
Выделить первый блок
Нажать клавишу [Ctrl]
Удерживая нажатой клавишу [Ctrl], выделить следующие блоки











Для выделения строки
· достаточно кликнуть мышкой на ее номер.
Для выделения столбца достаточно кликнуть мышкой на его имя.
Для выделения нескольких строк и столбцов (смежных и несмежных) нужно использовать алгоритмы выделения ячеек.
Для выделения всего листа следует воспользоваться комбинацией клавиш [Ctrl + A].










Ввод информации
Ввод информации можно осуществлять только в активную ячейку. Активная ячейка выделяется черным прямоугольником, ее адрес отображается в левой части строки формул, а имя столбца и номер строки активной ячейки выделяются синей подсветкой.
В ячейку можно вводить два типа информации – константы и формулы. Константа представляет собой данные в виде числовых значений, текста, даты или времени. Числовые значения состоят только из цифр. Текстовые значения могут включать любые символы. В основном информация вводится с клавиатуры.
При вводе данных ячейка переходит в режим редактирования.
Отличительные особенности режима редактирования данных:
Слева от строки формул появляются две кнопки: Отмена (красный крестик) и Ввод (зеленая галочка)
В ячейке (или в строке формул) появляется мигающий курсор

Принять (зафиксировать) данные в ячейке можно
щелчком мыши на кнопке Принять (слева от строки формул)
клавишей [Tab]
клавишей [Enter]
клавишами перемещения курсором
щелчком мыши на любой другой ячейке

Отменить ввод или редактирование данных можно
щелчком мыши на кнопке Отмена (слева от строки формул)
клавишей [Esc]

Строка меню

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

Заголовок документа

Активная ячейка

Адрес активной ячейки

Номера строк

Активный лист книги

Имена листов

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

Горизонтальная полоса прокрутки

Вертикальная полоса прокрутки

Панели инструментов

Имена столбцов


Область задач






Последний выделенный блок с активной ячейкой

Выделенные блоки (отдельная ячейка тоже является блоком)

Рамка выделения

Выделенная область

Правый нижний угол блока

Активная ячейка (левый верхний угол блока)

Индикаторы

Рабочее поле листа




Microsoft Excel 2

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










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

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







При этом ширина столбца может быть больше или меньше размера информации в ячейке.

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

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



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

Примечание:
При вводе в ячейку, уже содержащую данные, старое содержимое ячейки теряется. Вернуть старое содержимое ячейки обратно можно одним из способов отмены ввода информации (см. занятие 1).
Итак, для редактирования (изменения, добавления, частичного удаления) информации в ячейке следует:
Сделать ячейку активной (с помощью клавиатуры или мыши)
Перейти в строку формул, щелкнув на ней мышью (в строке формул появиться мигающий курсор)
Произвести изменение информации
Произвести принятие ввода информации ([Tab], [Enter] и т.п.)
Можно непосредственно вести редактирование данных в ячейке дважды щелкнув по ней мышью (при этом мигающий курсор будет находиться в самой ячейке).
Удаление всей информации из активной (но не редактируемой) ячейки и выделенного блока производится клавишей [Delete].



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

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

Подробное форматирование
Способы вызова диалогового окна «Формат ячеек»
Команда меню «Формат / Ячейки»
Щелчок мыши на кнопке «Формат ячеек» панели инструментов «Стандартная» ()
Нажатие комбинации клавиш [Ctrl + 1] (единица нажимается на алфавитно-цифровой части клавиатуры).

Задание:










Цвет фона и текста

Границы

Отступы

Формат ячейки

Выравнивание

Начертание

Размер шрифта

Имя шрифта


Изменение размера выделенных столбцов






Автоматически измененный по ширине столбец

Старая граница раздела столбцов C и D

Новая граница раздела столбцов C и D

Подсказка о новой ширине столбца C

Обрезанный текст

В ячейке B4 информации нет (строка формул пуста)

В ячейке B3 содержится вся информация

Распространение текста на соседнюю ячейку




Microsoft Excel 3

Практическая работа № 1
Цель работы: совершенствование навыков форматирования ячеек для создания документа.
Средства: Microsoft Excel.
Методы работы:
Ввод информации
Изменение размеров ячейки
Форматирование ячейки (быстрое + подробное: вкладки «Число», «Выравнивание», «Граница»)
Копирование информации

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




Распечатка прилагается






























HYPER15Основной шрифт абзаца

Microsoft Excel 4

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


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

Демонстрация действия автозаполнения:



Аналогично производится заполнение соседних ячеек по строке.

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



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



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



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

Попробуем произвести операцию автозаполнения с названием месяца:



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


Числовые последовательности

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



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








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




Для активизации какого-нибудь листа достаточно щелкнуть указателем мыши на соответствующем ярлыке. Кроме того, имеется возможность быстрого перемещения по листам:
[Ctrl] + [Page Up] – переход к началу списка листов
[Ctrl] + [Page Down] – переход к концу списка листов
Также можно щелкнуть правой кнопкой мыши на любой из кнопок прокрутки, вызвав контекстное меню, в котором галочкой отмечено имя активного листа. Для перехода достаточно щелкнуть мышью на имени нужного листа.


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

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

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





При выборе пункта «Переименовать» название ярлыка листа выделяется и находится в ожидании ввода нового имени. Существует и более быстрый способ вхождения в режим переименования: двойной щелчок левой кнопкой мыши на ярлыке листа. При вводе нового имени ярлык листа изменяет свои размеры, подстраиваясь под размер вводимой строки. Зафиксировать введенное имя можно щелчком мыши на рабочем поле или другом ярлыке, а также нажатием клавиши [Enter].

Для организации книги Excel можно изменять порядок следования листов. Быстрый способ перемещения листа заключается в следующем: Щелкаем мышью на ярлыке листа и, удерживая кнопку мыши нажатой, начинаем перемещение в горизонтальном направлении. При этом указатель мыши принимает вид перемещаемого листочка, а появившийся над ярлыками черный треугольник является указателем нового местоположения листа. Отпустив кнопку мыши, происходит фиксация нового положения перемещаемого листа. Можно воспользоваться выбором пункта контекстного меню «Переместить/скопировать», при выборе которого появляется диалоговое окно «Переместить или скопировать». Кроме простого перемещения с помощью этого окна имеется возможность создать копию листа и поместить ее в удобное для нас место.

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








Кнопки прокрутки

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

Ярлык активного листа

Получаем нужную последовательность

Задаем закон изменения чисел

Отпускаем кнопку мыши

Перемещаем указатель вниз


Выделяем две соседние ячейки






Маркер заполнения

Отпускаем кнопку мыши

Тянем за маркер выделенного блока вправо

+

Отпускаем кнопку мыши

+

Перемещаем указатель вниз

Ввели данные в ячейку и зафиксировали их



HYPER15Основной шрифт абзаца

Microsoft Excel 5

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

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

Используемая в формуле ссылка на ячейку может быть относительной и абсолютной. Относительная ссылка связана с местом помещения формулы и при копировании или перемещении формулы автоматически изменяется. Абсолютная же ссылка не меняет своего значения при копировании.

Использование формул можно сделать еще более удобным, если присваивать имена ячейкам. При использовании присвоенного имени не надо помнить номер строки и столбца. Другое преимущество заключается в том, имя ячейки может быть использовано в любом рабочем листе данной рабочей книги.
Присваивая имена ячейкам, необходимо учитывать следующее:
Имя должно начинаться с буквы.
Имя не может быть сходным со ссылкой на ячейку – например, A2.
В качестве имени можно использовать одиночные буквы, за исключением букв R и C.
Для разделения слов или цифр внутри имени используйте символ подчеркивания (Счет_продажи) или точку (Первый.квартал), так как пробелы и некоторые специальные знаки ($, ! и др.) недопустимы.
Имя ячейки должно быть по возможности информативным, например, Сумма_налога.
Имя может содержать до 253 символов.

Для задания или изменения имени активной ячейки можно воспользоваться одним из следующих способов:
Щелкнуть на имени активной ячейки в левом верхнем углу листа, ввести имя и нажать [Enter]
Вызвать диалоговое окно «Присвоение имени» комбинацией клавиш [Ctrl + F3] или командой «Вставка / Имя ( Присвоить», ввести имя и нажать OK.

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

В Microsoft Excel формула может использовать значения в ячейках для выполнения таких операций, как сложение, вычитание, деление и умножение. Чтобы выполнить вычисления, в формуле необходимо использовать математический оператор – знак сложения (+), вычитания (-), умножения (*) и деления (/). Все математические операторы находятся на дополнительной клавиатуре.
Рассмотрим пример простейшего вычисления:

В ячейке C4 нужно рассчитать сумму расходов в рублевом эквиваленте при известном курсе валюты и денежной сумме, выраженной в долларах.
Для данного вычисления нужно перемножить данные в ячейках C2 и C3, результат поместить в C4. Рассмотрим последовательность действий, которые, несмотря на всю их простоту, нужно выполнять очень аккуратно.
Сделать активной ячейку, в которую будет помещен результат вычислений (C4).
Ввести с клавиатуры знак равенства =, чтобы начать запись формулы.
Навести указатель и щелкнуть мышью на ячейке, содержащей первый множитель (C2).
Ввести с клавиатуры знак умножения * (звездочка).
Навести указатель и щелкнуть мышью на ячейке, содержащей второй множитель (C3).
Зафиксировать ввод одним из способов (кроме клавиш перемещения курсором) – нажатием клавиши [Enter] или [Tab] или щелчком мыши на кнопке «Принять (Ввод)».



Если снова сделать ячейку C4 активной, то можно заметить разногласия между содержимым ячейки и записью в строке формул. На самом деле в ячейке находится формула, а отображается результат вычисления.
При изменении исходных данных в ячейках C2 или C3 Excel автоматически пересчитывает результат по уже введенной формуле и помещает его в C4. Таким образом, у нас получилась заготовка для перевода определенной суммы валюты в рублевый эквивалент. Изменим, к примеру, курс доллара и добавим форматирование.
Для отображения символов доллара и буквы «р.» можно использовать денежный формат числа, который выбирается во вкладке «Число» диалогового окна «Формат ячеек». Сами символы при изменении или добавлении информации вводить не надо – они отображаются после фиксации данных (в строке формул при этом отображается непосредственно само число или формула). На этой же вкладке можно определить количество знаков после запятой.

Очень часто в таблицах приходится выполнять не одно, а несколько похожих вычислений. При этом формулу достаточно ввести один раз и распространить (скопировать) ее на ячейки. Рассмотрим следующий пример:
В столбце D нужно подсчитать стоимость товара исходя из его цены и количества. Конечно, можно ввести формулу 5 раз, но существует более удобный способ, тем более что в других примерах количество однотипных вычислений может быть намного больше.
Один раз все-таки формулу ввести придется: в ячейке D3 набираем «=B3*C3» и заканчиваем ввод клавишей [Enter]. Затем, снова делаем активной ячейку D3, в которой находится только что введенная формула. Наводим указатель мыши на маркер заполнения, щелкаем кнопкой мыши и, удерживая ее в нажатом состоянии, протягиваем маркер заполнения еще на 4 ячейки вниз.


Данные в столбце D, при отпускании кнопки мыши, заполняются значениями, вычисленными по той же формуле, но в соответствующих строчках. Если мы сейчас выделим какую-нибудь ячейку столбца D, например, D6, то в строке формул можно обнаружить соответствующую формулу: «=B6*C6». Таким образом, Excel позволяет копировать однотипные формулы, автоматически изменяя ссылки на ячейки. Такие изменяемые ссылки и называются относительными, так как не привязаны к первой строке, а изменяются относительно ее.
Копирование формул можно осуществлять не только с помощью маркера заполнения. Рассмотренный способ применим только для заполнения формулами смежных ячеек. В том случае, когда вычисляемые ячейки не находятся рядом, нужно использовать стандартный способ копирования, пригодный для всех случаев копирования. Порядок действий при этом следующий:
Сделать активной ячейку, содержащую прототип формулы.
Скопировать формулу в буфер обмена, нажав кнопку «Копировать» () на панели инструментов «Стандартная» или нажав комбинацию клавиш [Ctrl] + [Insert]. Ячейка при этом должна выделиться бегущей штриховой линией.
Выделить ячейки (блоки ячеек), в которые следует скопировать формулу.
Нажать кнопку «Вставить» () на панели инструментов «Стандартная» или нажать комбинацию клавиш [Shift] + [Insert].
Формула благополучно скопируется, автоматически меняя ссылки на ячейки. Выделение бегущей штриховой линией начальной ячейки при этом сохраняется, это говорит о том, что в буфере обмена осталось содержимое данной ячейки.


Функции

В нашем последнем примере было бы неплохо рассчитать общую сумму расходов. Для этого мы должны просуммировать пять значений стоимости. С одной стороны, можно ввести в ячейку D8 формулу следующего содержания =D3+D4+D5+D6+D7 и получить соответствующий результат. С другой стороны, при 10, 50 или 100 суммирующихся ячейках эта операция не совсем удобна. Выход из сложившейся ситуации очень прост – использование встроенных функций Excel, одной из которых является получение суммы значений ячеек любого диапазона.
Так как функции применяются для расчетов и являются составной частью формулы, то их вызов осуществляется либо вводом в ячейку знака равенства (=), либо щелчком мыши на кнопке вызова функций.
Взглянув после этой ввода в ячейку знака равенства на участок экрана, где раньше располагалось имя ячейки, заметим, что вместо имени ячейки появилось имя последней использовавшейся при работе функции с ниспадающим списком. Для просмотра списка нужно щелкнуть мышью кнопку с черным треугольником. В списке находятся 10 недавно использовавшихся функций и пункт «Другие функции».

Выбор самого последнего пункта приводит к вызову диалогового окна «Мастер функций», состоящего из двух шагов.

В первом окне («Шаг 1 из 2») находятся список категорий, по которому распределено огромное количество функций Excel, и список имен функции в алфавитном порядке, принадлежащих выделенной категории. Снизу списков имеются две надписи, служащие для обозначения формата и для объяснения результата работы функции, выделенной в нижнем списке.
Для решения нашего примера нам нужна функция суммирования ячеек, которая имеет имя «СУММ» и принадлежит категории «Математические». Таким образом, сначала выделяем нужную категорию в верхнем списке и затем ищем и выделяем нашу функцию «СУММ» в нижнем списке. Прочитав пояснение к результату, удостоверимся, что наш выбор верен.
После нажатия кнопки «OK» окно переходит к следующему шагу («Шаг 2 из 2»). Это непосредственно само окно функции. В нем снова можно прочесть имя функции и краткое пояснение к результату ее работы.
В самом простейшем случае (как у нас) функция может догадаться о диапазоне тех ячеек, которые следует суммировать. Этот диапазон записан в тестовом поле под именем «Число1». В нем указаны имена первой и последней ячейки диапазона, разделенные двоеточием. Этот формат записи диапазона табличного процессора Excel. Справа от текстового поля указаны первые значения этого диапазона. Обратите внимание, что в строке формул уже записывается функция с указанными аргументами. При желании можно суммировать несколько диапазонов, которые аналогичным образом нужно записывать в текстовые поля «Число2», «Число3» и т. д. В нижней части окна функция выдает предварительный результат своей работы с выделенным диапазоном ячеек.
Если мы согласны с предложенным диапазоном, то следует нажать клавишу [Enter] или щелкнуть мышью на кнопке «OK». Если же предлагаемый диапазон нас не устраивает, то нужно кликнуть кнопку выбора диапазона ячеек справа от текстового окна. При этом окно функции свернется, оставив после себя лишь одно текстовое поле с заголовком «Аргументы функции».



Сворачивание окна удобно тем, что мы можем просмотреть таблицу, часть рабочего листа и выделить мышью тот диапазон, который нам необходим. При выделении мышью в текстовое поле и строку формул автоматически записывается диапазон ячеек. На рабочем листе указанный блок ячеек выделяется бегущей штриховой линией.
Для фиксации диапазона в функцию достаточно нажать клавишу [Enter] или щелкнуть мышью на кнопке «Принять диапазон». Во вновь раскрывшемся окне функции нажимаем кнопку «OK» для записи формулы в ячейку и осуществления расчета по ней.
Диалоговое окно «Мастер функций» можно вызвать через меню командой «Вставка / Функция» или щелчком мыши на кнопке вызов функций ([fx]). Досрочный выход из этого окна можно осуществить нажатием клавиши [Esc] или щелчком мыши на кнопке «Отмена». В этом случае формула не запишется в ячейку, сохранив ее предыдущее содержимое.


Во всех предыдущих случаях мы использовали, так называемые, относительные ссылки. Рассмотрим еще один пример, в котором появится необходимость введения другого типа ссылок – абсолютных и смешанных.
Пусть мы займемся подсчетом стоимости товара с учетом действующей скидки.
В таблице представлены код, наименование товара и его цена без скидки. Для подсчета стоимости товара со скидкой в ячейку D4 нужно ввести следующую формулу: «=C4-C4*C1». Если же теперь попытаться копировать данную формулу в ячейки диапазона D5:D7, то получится совсем неожиданный результат. Вторая цена осталась без изменений, в третьей строке произошла ошибка вычисления, а цена за четвертый товар вовсе оказалась отрицательной. Если взглянуть на формулу, записанную, например, в ячейке D7, то можно заметить, что последняя ссылка должна быть не C4, а оставаться, как и в первой формуле, C1.

В этом случае необходимо использовать абсолютные ссылки. При перемещении и копировании формулы абсолютные ссылки не изменяются (фиксируются). Для фиксации ячейки при ее записи в формуле используется знак доллара ($).
Следовательно, для того, чтобы получить верные результаты в нашем примере, в ячейке D4 вместо формулы «=C4-C4*C1» нужно ввести «=C4-C4*$C$1». Это означает, что при копировании формулы ссылка на ячейку C1 останется зафиксированной, тогда как ссылка на ячейку C4 будет изменяться (C5, C6 и т. д.), так как является относительной ссылкой.
Кроме непосредственного ввода знака $ в строку формул можно воспользоваться клавишей [F4], предварительно поставив курсор в строке формул на имя ячейки, ссылку на которую хотите сделать абсолютной. При этом знаки доллара автоматически появятся перед именем столбца и номером строки ячейки.

Рассмотрим также пример, в котором используются смешанные ссылки, то есть ссылки, в которых фиксируется только столбец или только строка. Ярким примером использования таких ссылок является получение таблицы умножения.
Получить смешанную ссылку можно также нажатием на клавишу [F4]: первое нажатие приводит к появлению абсолютной ссылки, второе – к фиксации строки, третье – к фиксации столбца, четвертое нажатие приводит ссылку в формуле к ее относительному виду.
Абсолютные и смешанные ссылки можно использовать и в функциях. Например, для создания таблицы возведения в степень (аналогичная таблице умножения) первая записанная формула будет выглядеть так: «=СТЕПЕНЬ($A2;B$1)».
Кнопка «Принять диапазон»

Выделение блока ячеек

Результат

Кнопка выбора диапазона ячеек

Запись в строке формул

Предварительный результат

Пояснение результата

Текстовые поля

Имя функции


Пояснение работы функции

Список функции соответствующей категории

Раскрывающийся список категорий



Кнопка вызова функций







Смешанная ссылка

Абсолютная ссылка

Кнопка раскрытия списка

Имя функции

HYPER13 EMBED PBrush HYPER14HYPER15

Захватываем маркер заполнения и тянем вниз

Автоматическое изменение относительных ссылок в формуле

Расчетная формула

Результат вычисления

Ввод формулы

Последовательные нажатия клавиши [F4] и изменения формата ссылки




Microsoft Excel 6



Использование диаграмм

В ряде случаев бывает затруднительно визуально оценить данные и понять их значимость, глядя на однообразные и маловыразительные колонки цифр. Для более доступного в визуальном отношении представления информации в Microsoft Excel существуют диаграммы.
Диаграммы создаются на основе данных, следовательно, прежде всего мы должны составить некоторую таблицу, данные которой и будут представлены в графическом виде. Для примера возьмем расчет урожайности винограда различных агрофирм. В таблице придется сделать несложный расчет.
Теперь займемся представлением данных в виде диаграммы. Для этого нам потребуется мастер диаграмм, вызов которого можно осуществить командой меню «Вставка \ Диаграмма» или щелчком мыши на кнопке «Мастер диаграмм» (), находящейся на панели инструментов «Стандартная». Работа мастера диаграмм состоит из четырех шагов. Рассмотрим подробнее каждый шаг, реализуя одновременно нашу задачу.

Шаг 1.
На первом шаге нам предлагается выбрать тип и вид диаграммы. Типы диаграмм представлены слева в виде списка с рисунком, а виды – непосредственно в виде графического изображения. Выбор типа и вида диаграммы осуществляется щелчком мыши. В текстовом поле ниже видов диаграмм имеется краткое пояснение. В нижнем левом углу находится кнопка «Просмотр результата». При просмотре нужно щелкнуть и удерживать в нажатом положении данную кнопку. При этом правая часть окна переименуется в образец и в нем можно наблюдать результат построения диаграммы, основанной на данных, выделенных вами или предложенной самой Excel.
На второй вкладке имеется список дополнительных, нестандартных типов диаграмм, использующихся реже стандартных.
Нам пока потребуется тип «Гистограмма» и ее первый, выделенный вид «Обычная гистограмма». Для перехода к следующему шагу следует нажать кнопку «Далее».




Шаг 2.
Второй шаг – самый важный. В нем определяются диапазоны данных, используемых в нашей диаграмме. Excel по умолчанию использует данные всей таблицы (на листе таблица выделяется бегущим штриховым прямоугольником). То есть вид диаграммы может и не соответствовать нашему конечному результату.
Во-первых, нам нужно задать диапазон данных, которые находятся на нашем листе в ячейках D2:D4. Для этого мышью на листе выделяем данный диапазон (если ячейки закрыты окном, то его можно предварительно сдвинуть, захватив за заголовок окна). После выделения и отпускания кнопки мыши в окно мастера диаграмм запишется наш диапазон.
Во-вторых, наши данные находятся в столбце (столбец D), следовательно, нужно щелкнуть размещение рядов (данных) в «столбцах».
Теперь вид диаграммы вполне соответствует нашей задаче, за исключением подписей. Соответствующие подписи для названий агрофирм можно включить в диаграмму, используя вкладку «Ряд». На этой вкладке можно также увидеть внешний вид диаграммы, количество рядов данных и их имена, диапазон значений, который мы уже задали на первой вкладке и подписи оси X.
Для решения нашей задачи достаточно использовать метку «Подписи оси X:». Для этого справа от текстового поля нужно щелкнуть мышью прямоугольник со стрелкой. При щелчке окно мастера диаграмм сворачивается и находится в режиме ожидания ввода диапазона подписей оси X. Теперь нам нужно выделить диапазон, которым являются названия агрофирм. Затем следует нажать [Enter] или кнопку «Принять диапазон».







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












Шаг 3.
Третий шаг используется для оформления диаграммы и имеет множество вкладок.
Вкладка «Заголовки» служит для того, чтобы дать названия диаграмме и подписям на осях X и Y. В соответствующие текстовые поля впишем «Урожайность», «Агрофирмы» и «Ц/га».
На вкладке «Оси» можно установить или сбросить подписи на осях. Этого мы делать не будем.
На вкладке «Линии сетки» можно убрать или добавить линии на диаграмме по осям X и Y. Оставим линии без изменения.
Вкладка «Легенда» позволяет убрать или добавить пояснения к данным (легенду), расположенные по умолчанию справа от диаграммы. Вкладка позволяет также изменить местоположение легенды относительно диаграммы. Так как пояснений на осях вполне достаточно, то на этой вкладке мы снимем флажок «Добавить легенду», то есть попросту удалим легенду.
Вкладка «Подписи данных» позволяет добавить в диаграмму подписи рядов, категорий или значений. Ввиду достаточной информативности мы тоже оставим на этой вкладке все без изменений.
Вкладка «Таблица данных» служит для добавления в диаграмму (в нижней ее части) данных, оформленных в виде таблицы. Нам это тоже не нужно.
Теперь, после задания соответствующих подписей, можно переходить к последнему, четвертому шагу.


Шаг 4.
На заключительном шаге нам требуется выбрать место (лист), где будет создана диаграмма. Предлагаются два варианта: либо на отдельном листе, который здесь же можно назвать, либо на уже имеющемся листе. Создадим диаграмму на нашем листе «лист1», для этого просто нажнем [Enter] или щелкнем мышью кнопку «Готово».
Наша первая диаграмма готова и в выделенном виде помешается на рабочий лист.


На самом деле существует множество способов изменить нашу диаграмму, что мы сейчас и рассмотрим.
Не смотря на то, что все четыре шага «Мастера диаграмм» уже пройдены, имеется возможность вернуться к любому из четырех шагов. Для этого достаточно вызвать контекстное меню диаграммы щелчком правой кнопкой мыши и выбрать один из пунктов во второй группе команд. Эти четыре команды как раз соответствуют четырем шагам «мастера диаграмм», которые мы только что прошли.
Команда контекстного меню «Формат области диаграммы» позволяет задать общие для всей диаграммы параметры, касающиеся ее вида, шрифта и некоторых специфических свойств, которых мы касаться не будем. Контекстное меню диаграммы позволяет также заключить диаграмму в отдельное окно и совершать с ним стандартные действия, как и с любым выделенным объектом.

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


Задание

















































Изменение формата области построения

Команды контекстного меню, соответствующие шагам «Мастера диаграмм»

Свернутое окно

Выделяем диапазон

Внешний вид диаграммы

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

Выбор размещения данных

Вид диаграммы по данным диапазона

Текущий диапазон данных диаграммы

Вкладка «Нестандартные»

Кнопка «Далее»

Просмотр

Нажать и удерживать

Пояснение

Виды диаграмм

Типы диаграмм



Заголовок 1 Заголовок 2HYPER15Основной шрифт абзаца

Microsoft Excel 7


Работа с данными
В занятии будут рассмотрены основные методы поиска и отбора информации, представленной в виде таблиц, а так же некоторые дополнительные сведения для наилучшего представления данных в таблице.
Основными методами поиска и отбора являются сортировка и фильтрация. Но для изучения этих методов нам снова нужны данные. В этот раз мы воспользуемся информацией о площади и населении некоторых стран западной Европы. Для создания данной таблицы повторяем форматирование ячеек, ввод формул (плотность населения рассчитывается по формуле =C4*1000/B4) и копирование формулы с помощью метода «Автозаполнение».
Можно также удалить лишние листы, переименовать «лист1» в «население» и сохранить файл под именем «Европа.xls».

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

Выборочная сортировка
Выборочная сортировка применяется в том случае, если нужно отсортировать данные по значениям не только первого, но и второго, третьего и т. д. столбцов, а также позволяет производить комбинированную сортировку. Вызов данного метода осуществляется с помощью команды меню «Данные / Сортировка», но не забудьте перед этим выделить данные таблицы (кроме заголовков). Перед нами предстает диалоговое окно с вполне понятными надписями.
Если нам нужно произвести единичную сортировку, то в первом ниспадающем списке нужно выбрать имя столбца, по которому будет производиться сортировка, отметить направление сортировки (при этом два нижних списка остаются незаполненными) и нажать кнопку «OK».
При комбинированной сортировке нужно заполнить второй, а если потребуется, то и третий списки, так же с указанием направления их сортировок. При этом надо помнить, что
Только один, первым указанный, столбец будем полностью отсортирован (основная сортировка)
Вторая сортировка вступает в силу тогда, когда имеются одинаковые значения первого указанного столбца (вторичная сортировка)
Третья сортировка выполняется тогда, когда имеются одинаковые значения второго указанного столбца при одинаковых значениях первого указанного столбца.

Задание
Попробуйте отсортировать данные по площади стран (основная сортировка) и по их названиям (вторичная сортировка)



Фильтрация
Использование фильтра позволяет выявить из всех данных таблицы только ту информацию, которая удовлетворяет определенному значению или условию. Для использования фильтра нужно
Выделить заголовки таблицы данных
Выполнить команду меню «Данные / Фильтр
·Автофильтр»
При этом в ячейках заголовка появятся кнопки ниспадающих списков, содержание которых зависит от значений соответствующих столбцов. Существуют также значения, которые определены во всех списках:
(Все) – показать все данные таблицы (отключение фильтрации по значениям данного столбца)
(Первые 10) – показать произвольное количество строк (не обязательно 10), обладающих наибольшим или наименьшим значением данного столбца
(Условие) – позволяет выявить строки, удовлетворяющие некоторому условию
(Пустые) – показать только те строки, которые не имеют значений в данном столбце
(Непустые) – показать строки с имеющейся информацией в данном столбце
При выборе конкретного значения в таблице отобразятся только те строки, у которых данные в фильтруемом столбце равны этому значению.

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

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

Задание
Выберите из списка 7 стран имеющих наименьшую населенность (Первые 10)
Выберите из списка страны, плотность населения которых превышает 100 человек на квадратный метр (Условие)


























































Кнопки ниспадающих списков

Полоса прокрутки значений


Направление сортировок

Имена столбцов, по которым сортируются данные в следующую и последнюю очередь (могут быть пустыми)

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



Заголовок 1 Заголовок 2HYPER15Основной шрифт абзаца

Microsoft Excel 8, 9, 10


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



2. Оформить таблицу в соответствии с заданным образцом:


3. Использование формул, относительных и абсолютных ссылок:


4. Построить диаграмму по данным таблицы:




5. Создайте таблицу (данные можно вводить свои), отсортируйте данные по фамилиям. Выявите: а) неуспевающих по математике; б) учащихся только на «4» и «5»



6. Сохраните книгу в папку «D:\ Мои документы \ Курсы учителей» под именем «Практика.xls»





Дополнительно
Построить график функции HYPER13 EMBED Equation.3 HYPER14HYPER15на промежутке [-5; 5] с шагом 0,5.





































































Приложенные файлы

  • doc 1
    Размер файла: 333 kB Загрузок: 0
  • doc 2
    Размер файла: 127 kB Загрузок: 0
  • doc 3
    Размер файла: 127 kB Загрузок: 0
  • doc 4
    Размер файла: 127 kB Загрузок: 1
  • doc 5
    Размер файла: 378 kB Загрузок: 0
  • doc 6
    Размер файла: 406 kB Загрузок: 0
  • doc 7
    Размер файла: 490 kB Загрузок: 0
  • doc 8910
    Размер файла: 444 kB Загрузок: 0