Создание и редактирование таблиц

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

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

Запуск процессора Excel 2007 можно осуществить несколькими способами:

Элементы экрана

Верхняя строка окна приложения ). В ней слева указывается имя программы Microsoft Excel и название рабочей книги Книга 1 (либо открытого файла).

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

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

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

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

Рабочий экран Excel разделен по вертикали на столбцы, а по горизонтали на строки. Столбцам присваиваются имена, соответствующие буквам латинского алфавита, либо числам (в зависимости от выбранного стиля с помощью Главное офисное меню > Параметры Excel > Формулы), а именами строк являются только числа. Области имен столбцов и строк располагаются в верхней (столбцы) и в левой (строки) части и называются заголовками. Пользуясь Excel, можно создать таблицы размером до 256 столбцов (название последнего – IV) и 65536 строк.

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

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

На экране Excel размещаются две полосы: вертикальная – у правого края экрана и горизонтальная – у нижнего края. С помощью этих полос удобно осуществлять быстрое перемещение по таблице.

Расположенная в нижней части экрана строка состояния содержит ряд полезных при создании таблицы сведений. На этой строке выводится информация о выбранной команде или выполняемой операции. Правая часть строки состояния показывает значение суммы числовых данных в активной ячейке и состояние включения цифровой клавиатуры (клавиши Num Lock>).

Ввод данных в таблицу

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

Закончить ввод данных в текущей строке можно нажатием:

По окончании ввода текстовые данные выравниваются по левому краю ячейки, числовые – по правому (по умолчанию). Если выравнивание требуется изменить, нужно воспользоваться командой Главная > Выравнивание или Формат Ячейки, вкладка Выравнивание (контекстное меню). При вводе нецелочисленных данных десятичные знаки определяются с помощью запятой. Если следует изменить десятичный разделитель на точку, надо последовательно выполнить настройки с помощью Панели управления.

Ввод формул

Ввод формулы обязательно должен начинаться со знака равенства "=" и завершаться клавишей <Enter>.

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

Когда в одной формуле объединяются несколько операций, операторы выполняются в следующем порядке:

Операторы с одинаковым приоритетом выполняется слева направо.

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

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

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

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

Организация ссылок

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

Для ввода в формулу значения из фиксированной ячейки (адрес которой при копировании или перемещении формулы остается неизменным) используются абсолютные ссылки (рис. 1.3 рис 1.3). При их обозначении в написании адреса ячейки добавляется знак доллара. (Например, $A$20, $IA200).

В случае изменения только одного значения адреса и фиксации другого используются смешанные ссылки (рис. 1.4 рис 1.4). Знаком $ фиксируется только имя столбца (например, $А9) или имя строки (например, E$6). Для ввода смешанных и абсолютных ссылок используется клавиша <F4> (курсор в этом случае помещается либо внутрь ссылки, либо после нее).

Автоматическое вычисление сумм

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

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

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

Редактирование табличных документов

Перемещение по таблице

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

Выделение фрагментов таблицы

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

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

Выделение всей строки: щелкнуть мышью по номеру строки или установить подсветку в любой ячейке данной строки и нажать клавиши <Shift><Пробел>,

Выделение всей колонки: щелкнуть мышью на имени столбца или выделить любую ячейку данного столбца и нажать клавиши <Ctrl><Пробел>.

Выделение всей таблицы: щелкнуть мышью по левому углу бордюра (по кнопке расположенной между заголовками строк и столбцов) и нажать клавиши <Ctrl><Shift><Пробел>.

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

Редактирование табличных данных

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

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

Для удаления символов можно использовать клавиши <Delete> и <Backspace>, а для управления режимами Вставка / Замещение клавишу <Insert>. По завершению редактирования нажать клавишу <Enter> или кнопку с галочкой в строке формул.

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

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

Для вставки строки в таблицу установить курсор в любую ячейку строки, перед, которой будет осуществляться вставка, и задать команду Главная $$$\to$$$ Вставить $$$\to$$$ Строки на лист.

Для вставки столбца в таблицу установить курсор в любую ячейку столбца, перед, которой будет осуществляться вставка, и задать команду Главная $$$\to$$$ Вставить $$$\to$$$ Столбцы на лист.

Для удаления строк или столбцов из таблицы установить курсор в любую ячейку, принадлежащую строке или столбцу, которые надо удалить, и задать команду Главная $$$\to$$$ Удалить строки с листа (Удалить столбцы с листа). Можно отметить действие последней команды или последний ввод данных. Для этого надо задать команду Панель быстрого доступа $$$\to$$$ Отменить.

Можно осуществить повтор последней выполненной операции. Для этого следует задать команду Панель быстрого доступа $$$\to$$$ Повторить.

Копирование данных и формул

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

Существует три способа копирования:

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

Автозаполнение

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

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

Для создания списка задать команду Данные $$$\to$$$ Проверка данных, вкладки Списки. Выбрать Новый список и ввести элементы этого списка. Нажать кнопку Добавить.

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

Режимы вычислений

Выбор ручного или автоматического режима вычислений

Сразу после создания таблицы действует автоматический режим вычислений. Он означает, что при вводе любого значения в любую ячейку таблицы происходит автоматический просмотр всей таблицы и пересчет всех содержащихся в ней формул. Такой режим очень удобен для пользователя, поскольку позволяет не думать о подобных пересчетах при изменении табличных данных. Однако такой режим замедляет работу при больших объемах таблиц. В таких случаях на момент ввода или исправления данных удобней перейти в ручной режим расчета. Для этого задать команду из меню Формулы $$$\to$$$ Вычисление $$$\to$$$ Параметры вычислений $$$\to$$$ Вручную. Вводимые при этом данные не будут сказываться на результатах вычислений по формулам.

Вычисления с точностью "как на экране"

При округлении числовых данных (с использованием кнопки уменьшить разрядность) в памяти компьютера по-прежнему сохраняются неокругленные числа. В этом случае последующие вычисления, например, суммирование округленных данных может привести к кажущимся ошибкам – суммируются не данные, отображенные на экране, а хранящиеся в памяти числа. Данная проблема может быть решена следующим образом: задать команду Главное офисное меню $$$\to$$$ Параметры Excel и выберите категорию Дополнительно. В разделе При пересчете этой книги выберите нужную книгу и установите флажок Задать точность как на экране.

Однако такое решение не может считаться универсальным, поскольку в ряде случаев требуется потеря точности, например, для вычисления сумм, но необходима полная точность, например, для расчета средневзвешенных величин. В этом случае для выполнения округления нужна не кнопка Уменьшить разрядность, а функция =ОКРУГ(). Для ее использования следует установить подсветку в ячейку, содержащую округляемую величину и ввести =ОКРУГЛ(РАСЧЕТНАЯ ФОРМУЛА; ТОЧНОСТЬ).

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

Форматирование таблиц

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

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

Для изменения ширины и высоты строки можно использовать одно из следующих действий:

Вводимый в ячейку длинный текст можно, например, расположить в несколько подстрок, увеличив тем самым высоту строки – для этого надо выделить соответствующую ячейку, затем задать команду щелчок правой кнопки мыши: Формат $$$\to$$$ Ячейки, выбрать вкладку Выравнивание и включить параметр Переносить по словам.

Иногда требуется введенный в ячейку длинный текст переразместить по нескольким ячейкам по вертикали. В этом случае следует выделить диапазон ячеек, в котором должен разместиться введенный текст, а затем задать команду Главная $$$\to$$$ Заполнить и указать Выровнять.

Для улучшения читаемости таблицы часто приходится использовать различные шрифтовые выделения: подбирать вид и размер шрифта, а также специальные эффекты (курсив, подчеркивание, полужирный шрифт). Указанные шрифтовые выделения могут быть распространены на всю таблицу, на выделенные ячейки или даже на выделенную часть отдельной ячейки. Далее можно: использовать кнопки панели форматирования для выбора вида шрифта, размера, начертания или задать команду Главная $$$\to$$$ Ячейки $$$\to$$$ Формат $$$\to$$$ Формат ячеек и последующего выбора вкладки Шрифт, где могут быть заданы все те же шрифтовые эффекты.

Изменение вида выравнивания

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

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

Для размещения текста по центру области из нескольких ячеек поступают следующим образом. Текст вводится в крайнюю ячейку области, затем, начиная с данной ячейки надо выделить всю область и задать команду (после нажатия правой кнопкой мыши) Формат ячеек $$$\to$$$ вкладка Выравнивание, установить параметр Объединить.

Обрамление элементов таблицы

Сетка, показывающая на экране ячейки таблицы, является вспомогательной и не отображается при печати таблицы. Задать или отменить показ на экране сетки можно, включив / выключив параметр Разметка страницы команды Параметры листа $$$\to$$$ Сетка $$$\to$$$ Вид. Для обрамления таблицы линиями, которые реально будут изображаться при печати, следует выделить элемент (ячейку или группу ячеек), затем использовать кнопку Граница панели форматирования или команду Формат > Ячейки, вкладка граница, где можно выбрать тип и цвет линии.

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

Изменение формата вывода чисел

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

Использование возможностей автоформатирования

Можно значительно сократить время оформления таблиц, если воспользоваться заранее созданными (встроенными) шаблонами форматирования таблиц. Эти шаблоны включают различные виды представления чисел и текста, выравнивание, рамки, узоры, ширину колонок, высоту строк. Для использования встроенных форматов надо выделить необходимый интервал (обычно не включающий заголовок таблицы), а затем задать команду Формат $$$\to$$$ Автоформат и выбрать требуемый вид формата. Для отмены оформления, созданного автоформатом, снова выделите таблицу, задать команду Формат $$$\to$$$ Автоформат и выбрать из списка форматов таблицы пункт нет.

Операции над документами

Запись табличного документа на диске

При первом сохранении нового документа следует задать команду Главная $$$\to$$$ Сохранить или нажать кнопку Сохранить стандартной панели и в поле файл ввести имя файла. При необходимости можно переопределить имена папки и диска.

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

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

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

Для включения режима автоматической перезаписи редактируемого файла на диск через определенные интервалы времени задать из меню Главное офисное меню $$$\to$$$ Параметры Excel команду Автосохранение каждые, включить в поле мин указать интервал сохранения.

Для выхода из режима создания или корректировки таблицы нажать клавиши <Ctrl><F4> или задать команду Главное офисное меню $$$\to$$$ Закрыть. Если закрываемый документ не был записан предварительно на диск, то на экране появится соответствующий запрос.

Вызов существующего табличного документа

Загрузка таблицы на экран может производиться одним из следующих способов:

Создание нового табличного документа

Перед созданием нового документа рекомендуется определить количество листов в новой книге, выбрав Главное офисное меню $$$\to$$$ Параметры Excel и в поле Число листов задать нужное число листов в рабочей книге.

Для создания нового документа задать команду Главное офисное меню $$$\to$$$ Создать, указать значок Новая книга, нажать ОК.

Одновременная работа с несколькими табличными документами

Если пользователь работает одновременно с несколькими Файлами – таблицами, то для перехода от одной таблице к другой можно использовать один из следующих способов:

Для одновременного отображения на экране всех открытых документов задать команду из контекстного меню Панели задач $$$\to$$$ Расположить и выбрать один из следующих параметров: Рядом, Стопкой, Каскадом.

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

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

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

Печать

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

ЗАДАНИЕ

Варианты заданий

Вариант 1.

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

Вариант 2.

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

Вариант 3.

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

Вариант 4.

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

Вариант 5.

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

Вариант 6.

В столбце "Процент" вычисляется процентное отношение ячеек столбца "Сумма баллов" от значения ячейки строки "Всего".

Вариант 7.

Формулы в ячейках столбцов "От 10 шт. ... Диллеры" должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей колонкой.

Вариант 8.

В столбцах "Сумма" и "Средний балл" должны быть введены соответствующие формулы.

Вариант 9.

Вариант 10.

В столбце "Евро/Доллар" должна быть формула для вычисления отношения курсов доллара и марки в соответствующей строке. В строку "Средний за период" вводятся формулы для вычисления среднего значения в столбце, в строку "Процент роста" – формулы для определения процентного прироста курса на 5 марта относительно курса на 1 марта 2010г.

Вариант 11.

Произвести расчет незаполненных граф следующим образом:

Вариант 12.

Расчетная формула: гр.5=гр.3*гр.4; гр.7=гр.5*гр.6/100; гр.8=гр.5-гр.7

Вариант 13.

Выполнить все необходимые расчеты следующим образом

Вариант 14.

Расчетная формула: гр.6 = гр.З + гр.4 – гр.5.

Вариант 15.

Расчетная формула: гр.5 = (гр.З – гр.4) * 100/ гр.З

КОНТРОЛЬНЫЕ ВОПРОСЫ