Гіпермаркет Знань>>Інформатика>>Інформатика 11 клас>> Інформатика: Поняття електронної таблиці. Автозаповнення.

Методичні рекомендації до предмету Інформатика 11 клас.

Тема «Дидактический материал на тему: Электронные таблицы».

Розгляд теми: Поняття електронної таблиці. Автозаповнення.

Оглавление

Введение

Урок 1 Начинаем работать с Excel

1.1. Запуск программы
1.2. Главное окно программы
1.3. Структура документа
1.4. Выделение элементов таблицы
1.5. Заполнение ячеек
1.6. Автоматический ввод данных в диапозон ячеек
1.7. Формат содержимого ячеек

Урок 2 Работа с формулами

2.1. Общие сведения
2.2. Массивы формул
2.3. Функции
2.4. Условная функция (функция ЕСЛИ())
2.5. Сообщения об ошибках

Урок 3 Операции с элементами таблицы

3.1. Вставка элементов таблицы
3.2. Удаление элементов таблицы
3.3. Удаление содержимого элементов таблицы
3.4. Перемещение и копирование данных
3.5. Поиск и замена данных

Урок 4 Оформление таблицы

4.1. Изменение размеров столбцов и строк
4.2. Объединение ячеек
4.3. Выравнивание содержимого ячеек
4.4. Форматирование шрифта
4.5. Границы и заливка
4.6. Автоформат

Урок 5 Графики и диаграммы

5.1. Создание диаграммы
5.2. Построение графика функции
5.3. Оформление диаграммы

Урок 6 Базы данных в Excel

6.1. Создание Базы Данных
6.2. Сортировка данных
6.3. Формы данных
6.4. Установление диапазона критериев фильтрации
6.5. Автофильтр
6.6. Расширенный фильтр

Урок 7 Дополнительные возможности

7.1. Работа с окнами
7.2. Закрепление областей
7.3. Присвоение имени ячейке, диапазону или формуле
7.4. Создание примечаний

                                                                                       Введение

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

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

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

Автор не претендует на полноту изложения материала и не рассматривает приемы создания конкретных документов.

Данное пособие может быть использовано как дополнние к курсу "Информационные технологии" или другому подобному курсу.

Разрешено свободное распространение при условии сохранения целостности текста.

Разрешено полностью изменять содержание раздела "Задания".

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

                                                               Урок 1. Начинаем работу с Excel

Урок 1 Начинаем работать с Excel

1.1. Запуск программы
1.2. Главное окно программы
1.3. Структура документа
1.4. Выделение элементов таблицы
1.5. Заполнение ячеек
1.6. Автоматический ввод данных в диапозон ячеек
1.7. Формат содержимого ячеек


                                                                         1.1. Запуск программы

Как и любую другую программу пакета Microsoft Office, Excel можно запускать несколькими спосабами:

1. в меню Пуск выберите пункт Программы, а затем Microsoft Excel


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

                                                                   1.2. Главное окно программы


 
Легко заметить, что основные панели инструментов и кнопки на них аналогичны тем, что вы могли видеть в других приложениях пакета Microsoft Office:

• строка меню - сгруппированные команды для работы с программой и таблицей;
• панель инстрвументов Стандартная содержит кнопки, предназначенные для работы с файлами (создание, открытие, сохранение, печать); кнопки элементов редактирования (вырезать, копировать, вставить) и т.д.;
• на панели инструментов Форматирования находятся кнопки, предназначенные для форматирования ячеек и их содержимого;
• в строке формул отображается содержимое активной ячейки. Можно редактировать данные непосредственно здесь;
• указатель ячейки - прямоугольник с утолщенной границей, окружающий по периметру активную ячейку;
• горизонтальная и вертикальная полоса прокрутки предназначены для быстрого перемещения по документу. Длина бегунка полосы соотносится с размером листа.
• ярлычки листов. Щелкая по этим закладкам, вы будете выводить на экран разные листы;
• при помощи кнопок прокрутки листов можно просмотреть все листы Книги, а также перейти к первому или последнему листу;
• помощник позволяет в случае необходимости вывести справочную информацию о некоторых функциях программы Excel.

                                                                          1.3. Структура Документа

Файл Excel называется Рабочая книга и имеет расширение .xls. Каждая Рабочая книга состоит из одного или многих листов . Листы одной рабочей книги обычно связаны между собой тематически. В случае необходимости рабочая книга может содержать десятки и даже сотни рабочих листов. Каждый рабочий лист имеет название. Это как бы отдельная электронная таблица. Для перемещения по листам открытой книги нужно щелкнуть по ярлычку рабочего листа.

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

Столбцы в таблице обозначаются латинскими буквами: А, В, С, … Если букв не хватает, используют двухбуквенные обозначения АА, АВ, и далее. Максимальное число столбцов в таблице – 256.

Строки в таблице нумеруются целыми числами. Максимальное число строк, которое может иметь таблица – 65536.

Ячейки в Excel располагаются на пересечении столбцов и строк. Каждая ячейка имеет свой адрес. Он формируется как объединение адресов столбца и строки без пробела между ними. Таким образом, первая ячейка может иметь адрес А1, последняя – IV65536.


 
                                                                1.4 Выделение элементов таблицы

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

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

3. Для выделения нескольких несмежных ячеек (групп ячеек) следует выделить одну ячейку (группу), нажать клавишу Ctrl и, не отпуская ее, выделить другие ячейки.

4. Чтобы выделить целый столбец (строку) таблицы, необходимо щелкнуть мышью по его имени. Для выделения нескольких столбцов или строк следует щелкнуть на имени первого столбца или строки и, не отпуская клавиши мыши, растянуть выделение на всю область.

5. Для выделения нескольких листов необходимо нажать клавишу Ctrl и, не отпуская ее, щелкать на ярлыках листов.

                                                                            1.5. Заполнение ячеек

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

Чтобы отредактировать данные в ячейке, необходимо:

1. сделать ячейку активной и нажать клавишу F2 или дважды щелкнуть по ячейке мышью;
2. в ячейке появится текстовый курсор, который можно передвигать клавишами управления курсором;
3. отредактировать данные;
4. выйти из режима редактирования нажатием клавиши Enter .

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

                                                1.6 Автоматический ввод данных в диапозон ячеек

Если необходимо заполнить диапозон ячеек в строке (столбце) одними и теми же данными необходимо применить средство Автозаполнение:

1. В первую ячейку введите данные.
2. Наведите указатель мыши на маркер заполнения (квадратик в правом нижнем углу Указателя ячейки).
3. Зажав левую кнпку мыши, переместите маркер, чтобы отметить диапозон ячеек для заполнения.
4. Выделив часть строки (столбца) отпустите кнопку мыши.

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

1. В первую ячейку введите число или дату.
2. В соседнюю ячейку введите следующее число или дату.
3. Выделите две заполненные ячейки.
4. Наведите указатель мыши на маркер заполнения.
5 Зажав левую кнпку мыши, переместите маркер, чтобы отметить диапозон ячеек для заполнения.
6. Выделив часть строки (столбца) отпустите кнопку мыши.

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

Пример 1.1. Используя режим Автозаполнения создать таблицу Пифагора (таблицу умножения).

1. Заполните четыре ячейки значениями и выделите их (рис 1а).
2. Используя режим Автозаполнение отметьте диапозон ячеек по строкам (рис 1б).
3. Расширьте диапозон ячеек по столбцам (рис 1в).
          


1.6. Формат содержимого ячкйки

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

1. Общий. Используется для отображения как текстовых так и числовых значений. Общий числовой формат используется по умолчанию. В большинстве случаев отображается то, что вводится в ячейку, имеющую общий формат.
2. Числовой. Является наиболее общим для представления чисел.
3. Денежный. Вариант числового формата. Отличме в том, что к числу добавляется знак денежной единицы.
4. Финансовый. Вариант денежного формата. Отличие в том, числа выравниваются по разделителю дробной и целой части числа.
5. Дата. Используется для отображения разных форм представления даты.
6. Время. Используется для отображения разных форм представления времени.
7. Процентный. Значение ячейки умножается на 100 и выводится с символом процента.
8. Текстовый. Данные отображаются как строки, независимо от их содержания.

Для изменения задания или изменения формата содержимого ячейки необходимо:

1. выделить ячейки;
2. выбрать команду Ячейки меню Формат ;
3. в диалоговом окне Формат ячеек выбрать вкладыш Число (рис.21);
4. в списке Числовые форматы выбрать тип формата содержимого ячейки, а в полях справа – параметры формата;
5. в поле Образец будет отображаться пример содержимого ячейки в выбранном формате;
6. щелкнуть ОК .


 
                                                        Вопросы для самоконтроля

1. Как запускается Excel и создается ярлык для этой программы?
2. Какрвы главные элементы главного окна программы?
3. Какова структура документа Excel?
4. Как вставить лист, удалить лист, переименовать лист?
5. Как выделить одну ячейку?
6. Как выделить строку (столбец)?
7. Как обозначаются столбцы и строки?
8. Как определить адрес ячейки?
9. Где располагается и для чего используется Поле имени ?
10. Как ввести данные в ячейку?
11. Какими способами можно зафиксировать данные?
12. Как отменить ввод данных в ячейку в процессе набора?
13. Как редактируются данные в ячейке?
14. Что такое режим Автозаполнение и как его можно использовать?
15. Как задается (изменяется) формат содержимого ячейки?

                                                        Урок 2. Работа с фомулой

Урок 2 Работа с формулами

2.1. Общие сведения
2.2. Массивы формул
2.3. Функции
2.4. Условная функция (функция ЕСЛИ())
2.5. Сообщения об ошибках

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

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

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

1. " + " (сложить),
2. " - " (вычесть),
3. " * " (умножить),
4. " / " (разделить) и
5. " ^ " (возвести в степень).

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

Примеры формул: =2*(А1+В2); =F7*С4+B1; =(А1+В1)^3

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

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

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

Для обращения к группе ячеек используются специальные символы:

1. : (двоеточие) – формирует обращение к блоку ячеек. Через двоеточие указывается левая верхняя и правая нижняя ячейки блока. Например : С2: D4 – обращение к ячейкам С2, С3, С4, D2, D3, D4.
2. ; (точка с запятой) – обозначает объединение ячеек. Например, А1;:В2;D4:D7 – обращение к ячейкам А2, В2, D4, D5, D6, D7.

Пример 2.1. Составить таблицу расчета стоимости компьютера.


 
1. В ячейку В2 заносится текущее значение курса.
2. В ячейки В5:В11 вносится цена товара в $.
3. В ячейку С5 записывается формула =В5*$B$2 (ссылка на ячейку В5 - относительная адресация; ссылка на ячейку В2 - абсолютная алресация).
4. Ячейки В6:В11 заполняются в режиме Автозаполнение.
5. К ячейкам В5:С12 применен формат Финансовый., причем для ячеек В5:В12 выбрано обозначение $ Английский, а для ячеек С5:С12 выбрано обозначение р.
6. В ячейки В12 и С12 записана функция СУММ. Для этого достаточно нажать кнопку Автосумма.

                                                                                 2.2. Массивы формул

Массивы формул удобно использовать для введения однотипных формул и обработки данных в виде таблиц. Например, для вычисления квадратов, размещенных в ячейках B1:E1 , вместо ввода формул в каждую ячейку можно ввести одну формулу – массив для всех ячеек. Microsoft Excel добавляет вокруг массива формул фигурные скобки { }, по которым его можно отличить.

Для создания массива формул необходимо:

1. выделить ячейки, в которых должен находиться массив формул;
2. ввести формулу обычным способом, указав в качестве аргументов группу ячеек-аргументов;
3. для завершения ввода вместо клавиши Enter нажать комбинацию клавиш Ctrl+Shift+Enter. 

Для редактирования массива формул необходимо:

1. выделить ячейки, в которых находится массив;
2. щелкнуть мышью внутри строки формул и отредактировать формулу;
3. для завершения нажать комбинацию клавиш Ctrl+Shift+Enter.

                                                                                       2.3 Функции

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

Примеры:

=СУММ(В5:В11) - сумма ячеек В5, В6, В7, В8, В9, В10, В11;
=КОРЕНЬ(D6) - извлечение корня квадратного из содержимого ячейки D6;
=EXP(A1*LN(B1))

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

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

1. выделить ячейку для формулы;
2. вызывать Мастер функций с помощью команды Функция меню Вставка или кнопки Вставка функции;
3. в диалоговом окне Мастер функций, в поле Категория необходимо выбрать тип функции, затем в списке Функция выбрать нужную функцию;
4. щелкнуть кнопку ОК ;


 
5. В полях Число1, Число2 и т.д. следующего окна ввести аргументы функции (числовые значения или ссылки на ячейки);


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

                                                             2.4. Условная функция (функция ЕСЛИ())

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

Синтаксис

ЕСЛИ ( <логическое выражение> ; <значение если истина> ; <значение если ложь>)

Логическое выражение (условие) - это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100: В1>B2 и т.д.

Значение если истина - это значение, которое возвращается, если логическое выражение равно ИСТИНА, т.е. условие выполнено.

Значение если ложь - это значение, которое возвращается, если логическое выражение равно ЛОЖЬ или условие не выполнено.

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

Пример 2.2. С помощью электронной таблицы найти корни квадратного уравнения.

1. В ячейки А3, В3, С3 внесем значение коэффициентов a, b, c соответственно.
2. В ячейку D3 запишим формулу вычисления дискриминанта: =B3^2-4*A3*C3
3. В ячейку Е3 запишим формулу вычисления x1: =(-B3+КОРЕНЬ(D3))/(2*А3)
4. В ячейку F3 запишим формулу вычисления x2: =(-B3-КОРЕНЬ(D3))/(2*А3)
5. Если есть необходимость, скопируем формулы в режиме Автозаполнение на несколько строк.


 
                                                                 2.5. Сообщения об ошибках

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

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

2. #ИМЯ? – не верно введено имя ячейки (чаще всего возникает из-за того что имя столбца указывается русскими буквами) или имя функции, в формулу введен текст, не заключенный в двойные кавычки:

3. #ЗНАЧ! – использован недопустимый тип аргумента. Вместо числового или логического (ИСТИНА или ЛОЖЬ) значения введен текст, и Microsoft Excel не может преобразовать его к нужному типу данных.

4. #ДЕЛ/0! – в формуле делается попытка деления на нуль. Для устранения ошибки проверьте написание формулы (возможно, неправильно поставлены скобки) или используйте функцию ЕСЛИ();

5. #ЧИСЛО! – в функции с числовым аргументом используется неприемлемый аргумент, или нарушены правила задания операторов, принятые в математике;

6. #Н/Д – является сокращением термина “Неопределенные Данные”. Это значение помогает предотвратить использование ссылки на пустую ячейку. Введите в ячейки листа значение #Н/Д , если они должны содержать данные, но в настоящий момент эти данные отсутствуют. Формулы, ссылающиеся на эти ячейки, тоже будут возвращать значение #Н/Д вместо того, чтобы пытаться производить вычисления. ;

7. #ПУСТО! – появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. ;

8 #ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку. Ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек. ;

9. #ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку. Ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек.

                                                               Вопросы для самоконтроля

1. Как записывается формула в Excel?
2. Какие операторы используются при записи формул?
3. Как можно изменить порядок вычисления в формуле?
4. Что такое относительная и абсолютная адресация?
5. Как задаются группы адресов ячеек?
6. Как задается массив формул?
7. Как вводится функция?
8. Как проверяются условия при вычислении?
9. Каковы основные ошибки при вычислении формул?


                                                       Урок 3. Операции с элементами

Урок 3 Операции с элементами таблицы

3.1. Вставка элементов таблицы
3.2. Удаление элементов таблицы
3.3. Удаление содержимого элементов таблицы
3.4. Перемещение и копирование данных
3.5. Поиск и замена данных

                                                 3.1. Вставка элементов таблицы

Для вставки ячеек следует:

1. выделить ячейки, на месте которых будут вставлены новые.
2. вызвать команду Ячейки из меню Вставка
3. В появившемся диалоговом окне Добавление ячеек выбрать тип вставляемого элемента:
4. щелкнуть ОК .


 
Для вставки строк или столбцов необходимо:

1. выделить строки или столбцы, на месте которых будут вставлены новые;
2. в меню Вставка выбрать пункт Строки или Столбцы.

                                                3.2. Удаление элементов таблицы

Для удаления элементов таблицы следует:

1. выделить их
2. в меню Правка выбрать команду Удалить. Столбцы и строки удаляются без запросов;
3. при удалении ячеек в диалоговом окне Удаление ячеек вариант удаления элемента;
4. нажать кнопку ОК


 
                                     3.3. Удаление содержимого элементов таблицы

Для удаления содержимого ячеек без удаления их самих следует:

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


 
• Все – очистить все;
• Форматы – устанавливается формат ячеек по умолчанию;
• Содержимое – очистка только содержимого ячеек;
• Примечание – очистка ячеек от примечаний.

Для удаления только содержимого ячеек также можно выделить их и нажать клавишу Delete .

                                       3.4. Перемещение и копирование данных

Табличный редактор Excel позволяет быстро и просто перемещать и копировать данные. Для этого:

1. выделите диапозон ячеек, подлежащих перемещению или копированию;
2. наведите на границу выделенного диапозона указатель мыши так, чтобы он принял форму стрелки;
3. удерживая нажатой клавишу мыши, передвинте границу диапозона в нужном направлении. Одновременно на экране появится подсказка - желтый прямоугольник, где отображается новый адрес диапозона. Чтобы скопировать содержимое ячеек, необходимо при перемещении удерживать клавишу Ctrl.

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

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

1. выделить ячейки, содержимое которых следует скопировать;
2. вызвать команду Копировать меню Правка или щелкнуть кнопку Копировать (вокруг группы ячеек появится пунктирная линия, указывающая, что фрагмент помещен в буфер);
3. выделить ячейки, в которые следует вставить данные;
4. вызвать команду Вставить меню Правка или щелкнуть кнопку Вставить.

Фрагмент из буфера скопируется в указанное место.

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

Для перемещения содержимого ячеек необходимо:

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

Фрагмент из буфера будет вставлен в указанное место.

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

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


 
                                                        3.5. Поиск и замена данных

Чтобы найти некоторую текстовую строку или число, необходимо:

1. в меню Правка выбрать пункт Найти;
2. в поле Что диалогового окна Найти указать, что необходимо найти ;
3. для того, чтобы Microsoft Excel учитывал при поиске регистр символов (нижний – строчные буквы, верхний – прописные) следует установить флажок Учитывать регистр;
4. для того, чтобы Microsoft Excel искал только те ячейки, все содержимое которых отвечает шаблону поиска, следует установить флажок Ячейка целиком;
5. в поле Просматривать необходимо выбрать направление поиска по срокам или по столбцам;
6. в поле Область поиска следует выбрать формулы , значения или примечания (поиск будет вестись только в указанных компонентах ячеек);
7. щелкнуть кнопку Найти далее (после чего будет выделено первое слово, отвечающее условиям поиска);
8. для поиска следующего фрагмента, отвечающего условиям, повторно щелкнуть кнопку Найти далее;
9. для замены выделенных символов необходимо щелкнуть кнопку Заменить, после чего появится диалоговое окно Заменить, в поле Заменить на, которого необходимо указать новое значение и нжать кнопку Заменить или Заменить все (в последем случае выделенные символы будут заменены во все документе).



                                                         Вопросы для самоконтроля

1. Как добавить ячейки?
2. Как удалить ячейки ?
3. Как вставить строку (столбец)?
4. Как удалить строку (столбец) ?
5. Как можно переместить выделенный фрагмент?
6. Как можно скопировать выделенный фрагмент?
7. Как изменяются формулы при копировании и перемещении?
8. Что такое Специальная вставка?
9. Как можно найти или заменить данные?

                                                 Урок 4. Оформление таблицы

Урок 4 Оформление таблицы

4.1. Изменение размеров столбцов и строк
4.2. Объединение ячеек
4.3. Выравнивание содержимого ячеек
4.4. Форматирование шрифта
4.5. Границы и заливка
4.6. Автоформат

                                             4.1. Изменение размеров столбцов и строк

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

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

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



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

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

                                                            4.2. Объединение ячеек

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

1. выделить группу ячеек;
2. нажать кнопку Объединить и поместить в центр на панели инструментов Форматирование.

Для того, чтобы убрать объединение необходимо:

1. выделить объединенную ячейку
2. в меню Формат выбрать команду Ячейки
3. в открывшемся диалоговом окне Формат ячеек, закладка Выравнивание, убрать галочку объединение ячеек.

                                                    4.3. Выравнивание содержимого ячеек

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

- выравнивать по левому краю;
- выравнивать по центру;
- выравнивать по правому краю;

Для указания других характеристик выравнивания необходимо:

1. выделить ячейки, которые следует отформатировать;
2. в меню Формат выбрать команду Ячейки;
3. выбрать закладку Выравнивание;
4. установить нужные параметры выравнивния


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

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

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

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

В группе флажков Отображение можно включить следующие режимы:

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

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

                                                          4.4. Форматирование шрифта

В большенстве случаев для установления шрифта достаточно кнопок форматирования шрифта на панели инструментов Форматирование:



Для указания других характеристик шрифта необходимо:

1. выделить группу ячеек;
2. в меню Формат выбрать команду Ячейки;
3. выбрать закладку Шрифт;
4. установить нужные характеристики шрифта;
5. нажать клавишу ОК


 
В списке Шрифт выбирается тип шрифта;
в поле Начертание выбирается начертание шрифта;
в поле Размер – размер шрифта в пунктах.
в поле Подчеркивание– тип линии подчеркивания:

• Нет – подчеркивание не используется;
• Одинарное, по значению– подчеркивание символов одинарной линией;
• Двойное, по значению– подчеркивание символов двойной линией;
• Одинарное, по ячейке– подчеркивание одинарной линией по ширине ячейки;
• Двойное, по ячейке– подчеркивание двойной линией по ширине ячейки;

в поле Цвет– цвет символов;

в рамке Эффекты можно установить флажки:

• зачеркнутый– зачеркивание текста одинарной линией;
• верхний индекс– размер символов уменьшается, текст располагается выше;
• нижний индекс– размер символов уменьшается, текст располагается ниже;

                                                                 4.5. Границы и заливка

Таблицы в Excel можно обрамить рамкой и заполнить различными цветами. Для обрамления необходимо:

1. выделить ячейки, которые необходимо обрамить;
2. в меню Формат выбрать команду Ячейки;
3. выбрать закладку Граница;
4. в поле тип линии выбрать тип линии рамки;
5. в списке цвет выбрать цвет линии;
6. для обрамления выделенных ячеек извне следует щелкнуть кнопку внешние ;
7. для обрамления внутренних границ ячеек следует щелкнуть кнопку внутренние ;
8. для снятия обрамления выделенных ячеек следует щелкнуть кнопку нет ;
9. с помощью группы кнопок Отдельные можно устанавливать и убирать отдельные линии; это также можно делать щелчком мыши в образце обрамления, представленного в окне;
10. щелкнуть ОК.


 
Создавать рамки можно также с помощью скрытого списка Границы - :

Элементы таблицы можно заштриховать различным цветом и узором. Для этого необходимо:

1. выделить ячейки;
2. выбрать в меню Формат команду Ячейки;
3. выбрать вкладыш Вид;
4. в палитре цветов выбрать цвет;
5. в списке Узор выбрать узор;
6. щелкнуть кнопку ОК.


 
Для изменения цвета фона можно щелкнуть по стрелке рядом с кнопкой  Заливка на панели инструментов Форматирование и в палитре цветов выбрать нужный цвет;

                                                                4.6. Автоформат

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

1. выделить диапозон ячеек, подлежащий форматированию;
2. выбрать в меню Формат команду Автоформат;
3. в диалоговом окне Автоформат выбрать готовый шаблон;
4. нажать кнопку ОК.


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

                                                          Вопросы для самоконтроля

1. Как изменить ширину столбца?
2. Как высоту строки?
3. Как можно объединить ячейки?
4. Как снять объединение ячеек?
5. Как выравнивается содержимое ячеек?
6. Как задать произвольную ориентацию содержимого ячейки?
7. Как устанавливаются границы в таблице?
8. Как выполняется заливка ячеек?
9. Как применяется Автоформат?


                                                      Урок 5. Графики и диаграммы

Урок 5 Графики и диаграммы

5.1. Создание диаграммы
5.2. Построение графика функции
5.3. Оформление диаграммы

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

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

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

Для создания диаграммы необходимо:

1. на рабочем листе выделить данные, по которым следует построить диаграмму, включая ячейки, содержащие заголовки полей. Если включить в диапазон ячейки, содержащие заголовки полей, то эти заголовки будут отображаться на диаграмме как пояснительные надписи.
2. выбрать команду Диаграмма меню Вставка или щелкнуть кнопку Мастер диаграмм;
3. в диалоговых окнах Мастера диаграмм следует выбрать тип, формат и другие параметры диаграммы;
4. для перехода к следующему шагу используется кнопка Далее;
5. для построения диаграммы на любом шаге можно щелкнуть кнопку Готово, тогда Мастер диаграмм самостоятельно закончит построение диаграммы.

Построение диаграммы рассмотрим на примере.

Пример 5.1. Построить диаграмму результатов голосования.

1. Строим таблицу результатов и выделяем нужные данные


 
2. Щелкним по кнопке Мастер диаграмм.

3. На первом шаге Мастера диаграмм выбираем тип (круговая) и вид (разрезанные сектора) диаграммы.


 
4. На вторм шаге Мастера контролируем диапозон ячеек (при необходимости его можно изменить).

 
5. На третьем шаге Мастера даем название диаграммы, убираем галочку Добавить легенду во вкладке Легенда и во вкладке Подписи данных в поле подписи значений отмечаем категория и доля.


 
6. На последнем, четвертом шаге Мастера отмечаем где будет размещаться диаграмма на отдельном листе или вместе с таблицей и нажимаем кнопку Готово.


 
7. Перетаскиваем диаграмму в нужное место листа.

 
Результат работы Мастера диаграмм

                                                              5.2. Построение графика функции

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

Пример 5.2. Построить график функции f(x) = x^2 + 3 sin(2x) - 2 на интервале [-3;3] с шагом 0,2

1. Строим таблицу значений аргумента и функции. Для этого вносим в ячейки В5 и В6 соответственно значения -3 и -2,8. Остальные ячейки колонки заполняем в режиме Автозаполения. В ячейку С5 записываем формулу =B5^2+3*sin(2*B5)-2. Остальные ячейки заполняем в режиме Автозаполения.


 
2. Щелкним по кнопке Мастер диаграмм и на первом шаге Мастера диаграмм выбираем тип - Точечный, вид - Точечная диаграмма со значениями, соединенными сглаживающими линиями.


 
3. Второй шаг Мастера диаграмм пропускаем. Для этого нажмем сразу кнопку Далее.
4. На третьем шаге даем название диаграммы; на закладке Линии сетки устанавливаем галочку основные линии в поле Ось Х (категории); на закладке Легенда убераем галочку Добавить легенду.


 
5. На последнем, четвертом шаге, нажимаем кнопку Готово.
6. Перетаскиваем график в нужное место страницы.

                                                         5.3. Оформление диаграммы

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

Для изменения фона диаграммы необходимо:

1. щелкнуть по области диаграммы (она будет выделенв);
2. нажать кнопку Заливка на панели инструментов Форматирование или Рисования и выбрать цвет или способ заливки.

Для изменения размеров обласи построения диаграммы необходим:

1. подвести указатель мыши к диаграмме(должна появиться подсказка Область построения диаграммы) и щелкнуть клавишей мыши;
2. перетянуть маркеры размера.

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

Для изменения атрибутов текста заголовка необходимо:

1. щелкнуть по заголовку (выделить заголовок);
2. используя клавиши  установить нужные атрибуты.

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

1. щелкнуть по одной из подписей (выделить);
2. используя клавиши  , установить нужные атрибуты.

Для изменения текста надписи необходимо:

1. выделить заголовок или подписи данных;
2. отредактировать текст стандартным образом.

Для изменения цвета элементов диаграммы необходимо

1. щелкнуть по одному из элементов диаграммы (выделить);
2. щелкнуть по элементу, цвет которого надо изменить;
3. нажать кнопку Заливка на панели инструментов Форматирование или Рисования и выбрать цвет или способ заливки.

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


 
1. Вбрать из списка в окне панели Диаграммы объект, который будете изменять;
2. Вызвать соответствующий мастер форматирования, нажав кнопку .

                                                          Вопросы для самоконтроля

1. Что надо для построения диаграммы?
2. Как используются заголовки таблицы в диаграммах?
3. Какие шаги выполняет Мастер для построения диаграммы?
4. Как строятся графики математических функций?
5. Как изменить размеры диаграммы?
6. Как как изменить цветовую схему диаграммы?
7. Как изменить атрибуты надписей на диаграмме?
8. Как изменить текст надписей на диаграмме?


                                                         Урок 6. Базы данных в Excel

Урок 6 Базы данных в Excel

6.1. Создание Базы Данных
6.2. Сортировка данных
6.3. Формы данных
6.4. Установление диапазона критериев фильтрации
6.5. Автофильтр
6.6. Расширенный фильтр

                                                       6.1. Создание Базы данных

База данных (БД) – таблица, состоящая из однотипных записей (строк). Информация в БД имеет постоянную структуру. Программа Excel позволяет содавать простые реляционные (табличные) БД. Столбцы таблицы являются полями записи в БД. Под имена полей выделяется первая строка таблицы. Например, если БД считать сведения об учениках класса (школы), то полями записи могут быть N, Фамилия,т Имя, Отчество, Адрес, Телефон, Дата рождения и т.д. Для работы с базой данных необходимо сначала создать соответствующую таблицу. Если выделить ячейку в таблице и выбрать одну из команд обработки баз данных в меню Данные, Excel автоматически определяет и обрабатывает всю таблицу. Данные, расположенные в столбцах и строках рабочего листа, обрабатываются как набор полей, которые образуют записи.


 
                                                        6.2. Сортировка данных

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

1. выделить одну ячейку таблицы и вызвать команду Сортировка меню Данные;
2. в поле списка Сортировать по выбрать поле, по которому будут отсортированы данные, и тип сортировки: по возрастанию – цифры сортируются по возрастанию, текст – в алфавитном порядке. По убыванию – сортировка в обратном порядке;
3. в поле Затем по указать поле, по которому будут отсортированы данные, имеющие одинаковые значения в первом ключевом поле;
4. в в поле В последнюю очередь по указаь поле, по которому будут отсортированы данные, имеющие одинаковые значения в первых двух ключевых полях.


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

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

                                                                  6.3. Формы данных

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


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

• Добавить – очищает поля для ввода новой записи базы данных. Если снова щелкнуть кнопку Добавить , то введенные данные будут добавлены, как новая запись, в конец базы данных.

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

• Вернуть – восстанавливает отредактированные поля в выведенной записи, удаляя сделанные изменения. Чтобы восстановить запись, необходимо сделать это перед нажатием клавиши Enter или перед переходом к другой записи.

• Назад – выводит предыдущую запись в списке. Если был определен критерий с помощью кнопки Критерии , то кнопка Назад выведет предыдущую запись из тех, которые удовлетворяют заданному критерию.

• Далее – выводит следующую запись базы данных.

• Критерии – очищает поля перед вводом критериев сравнения с операторами сравнения для поиска необходимого подмножества записей.

• Правка – служит для выхода из режима ввода критериев. Доступна только после нажатия кнопки Критерии.

• Очистить – удаляет существующий критерий из окна диалога. Доступна только после нажатия кнопки Критерии.

• Закрыть – закрывает форму данных.

Для добавления записи к базе данных необходимо:

1. выделить ячейку в таблице, к которой следует добавить запись;
2. в меню Данные выбрать команду Форма;
3. щелкнуть кнопку Добавить;
4. заполнить поля новой записи;
5. для перемещения к следующему полю нажать клавишу Тab;
6. после ввода данных нажать клавишу Enter для добавления записи;
7. после добавления всех необходимых записей, щелкнуть кнопку Закрыть.

Новые записи будут добавлены в конец базы данных.

                                                    6.4. Установление диапазона критериев фильтрации

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

1. Критерии вычисления – это критерии, которые являются результатом вычисления формулы. Например, диапазон критериев =F7=СУММ($В$2:$В$22) выводит на экран строки, имеющие в столбце F значения большие, чем сумма величин в ячейках В2:В22. Формула должна представлять из себя логическое выражение (условие). При фильтрации будут доступные только те строки, значения которых будут придавать формуле значения ИСТИНА .

2. Критерии сравнения – это набор условий для поиска, используемый для извлечения данных при запросах по примеру. Критерий сравнения может быть последовательностью символов (константой) или выражением (например, Цена > 700 ).

Для поиска с помощью Формы данных записей, отвечающих критерию, необходимо:

1. выделить ячейку в таблице;
2. в меню Данные выбрать команду Форма;
3. щелкнуть кнопку Критерии;
4. в полях редактирования ввести критерии для поиска данных;
5. для вывода на экран первой записи, отвечающей критерию, щелкнуть кнопку Далее;
6. для вывода на экран предыдущей записи, отвечающей критерию, щелкнуть кнопку Назад;
7. для поиска записей в списке по другим критериям щелкнуть кнопку Критерии и ввести новые критерии;
8. по окончанию щелкнуть кнопку Закрыть.

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

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

                                                                          6.5. Автофильтр

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов. С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты. Например, если в скрытом списке поля Дата рождения , то будут выведены только записи, у которых в соответствующем поле содержится значение 07-апр-88.


 
Если в поле списка выбрать пункт Условие … , то появится окно Пользовательский автофильтр.


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

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


 
Чтобы вывести все данные таблицы, необходимо вызвать команду Отобразить все или отменить команду Автофильтр.

                                                                     6.6. Расширенный фильтр

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

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

• скопировать результат в другое место – копирует отфильтрованные данные на другой рабочий лист или на другое место на этом же рабочем листе;

• Исходный диапазон – поле, определяющее диапазон, который содержит список, подлежащий фильтрации;

• Диапазон условий – поле, определяющее диапазон ячеек на рабочем листе, который содержит необходимые условия;

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

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

Для установки сложных критериев необходимо:

1. вставить несколько строк в верхней части рабочего листа;
2. в одном из вставленных пустых строк ввести имена столбцов, по которым следует отфильтровать таблицу;
3. при использовании критериев сравнения, имена критериев должны быть идентичны именам столбцов, которые проверяются;
4. в строках, расположенных под строкой с именами проверяемых столбцов, ввести критерии, которым должны соответствовать ячейки проверяемых столбцов;
5. выбрать в меню Данные пункт Фильтр, затем – Расширенный фильтр, и в диалоговом окне ввести условия фильтрации.


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

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

Чтобы снова вывести все записи следует в меню Данные выбрать пункт Фильтр, затем пункт Отобразить все.

                                                       Вопросы для самоконтроля

1. Что представляет из себя БД?
2. Как создать БД в Excel?
3. Для чего нужны Формы данных?
4. Как работать с Формой данных?
5. Как настраиваются критерии поиска?
6. Как выполняется команда Автофильтр?
7. Что такое Пользовательский фильтр?
8. Что такое Расширенный фильтр?
9. Как устанавливаются сложные критерии?
10. Как отменить результаты фильтрации?

                                                 Урок 7. Дополнительные возможности

Урок 7.  Дополнительные возможности

7.1. Работа с окнами
7.2. Закрепление областей
7.3. Присвоение имени ячейке, диапазону или формуле
7.4. Создание примечаний

                                                                 7.1. Работа с окнами

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

1. Установить указатель мыши на горизонтальную или вертикальную вешку разбиения (он примет вид двунаправленной стрелки) и перетянуть в нужное место. Размеры подокон можно изменять, перетягивая вешку разбиения.

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

 
Чтобы отменить разделение окон, следует в меню Окно выбрать команду Снять разделение или дважды щелкнуть на вешке разбиения.

С помощью команды Новое меню Окно можно создать дополнительное окно для активной рабочей книги, чтобы просматривать различные части рабочей книги одновременно. В этом случае в заголовке окна после имени рабочей книги через двоеточие указывается номер нового окна. Например, второе окно рабочей книги Ex6 будет названо Ex6:2.

                                                               7.2. Закрепление областей

Иногда, при работе с таблицами большого размера, необходимо закрепить одну часть таблицы. Например, закрепить заголовок таблицы, чтобы он всегда присутствовал на экране при прокрутке. Для закрепления только вертикальных или только горизонтальных заголовков следует:

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

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

3. в меню Окно выбрать команду Закрепить области.

Для закрепления и вертикальных, и горизонтальных заголовков необходимо:

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

2. в меню Окно выбрать команду Закрепить области.

Закрепление окон отменяется командой Снять закрепление областей меню Окно.

                                                 7.3. Присвоение имени ячейке, диапазону или формуле

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

Пример 7.1. При решениии квадратного уравнения

 
в ячейку D3 можно записать формулу =b1^2-4*a1*c1
в ячейку E3 можно записать формулу =(-b1+КОРЕНЬ(D1))/(2*a1)
в ячейку F3 можно записать формулу =(-b1-КОРЕНЬ(D1))/(2*a1)

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

1. выделить ячейку, диапазон ячеек или несколько диапазонов, которым следует присвоить имя;
2. щелкнуть мышью в поле имени в левой части строки формул;
3. набрать имя для ячейки или диапазона;
4. нажать клавишу Enter.

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

Чтобы присвоить имя формуле необходимо:

1. в меню Вставка выбрать пункт Имя, а затем пункт Присвоить;
2. в поле Имя ввести имя для формулы;
3. в поле Формула ввести формулу (она должна начинаться со знака "=");
4. щелкнуть кнопку Добавить, после чего имя формулы появится в списке;
5. для удаления имени из списка следует выделить его и щелкнуть кнопку Удалить;
6. после ввода всех имен щелкнуть ОК.

Чтобы вставить формулу с собственным именем, необходимо:

1. в меню Вставка выбрать пункт Имя, а затем пункт Вставить;
2. в списке Имя выбрать имя формулы и щелкнуть ОК.

                                                      7.4. Создание примечаний

Excel позволяет добавлять текстовые примечания к ячейкам. Это особенно полезно в одном из следующих случаев:

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

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

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

Примечание присоединится к ячейке и будет появляться при наведении на него указателя мыши. Для изменения текста примечания следует выделить соответствующую ячейку и в меню Вставка выбрать пункт Изменить примечание. Также для этого удобно использовать контекстное меню.

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


 
                                                       Вопросы для самоконтроля

1. Для чего нужно разбиение окна?
2. Как разделить окно на два (на четыре)?
3. Как снять разделение окна?
4. Для чего фиксируется область таблицы ?
5. Как закрепить горизонтальную (вертикальную) область?
6. Как снять закрепление области?
7. Как использовать заголовки в качестве имен?
8. Как задается имя ячейке, диапозону ячеек?
9. Как задается имя формуле?
10. Для чего нужно примечание?
11. Как добавить примечание?

Надіслано учителем інформатики Міжнародного ліцею "Гранд" Чебаном Л.І.