Вставка и удаление строк, столбцов
Введение
Существует множество различных пакетов программ по работе со цифровыми данными, но наибольшее распространение в органах внутренних дел получили электронные таблицы.
Электронные таблицы Microsoft Office Excel 2010 (далее Excel 2010) – это мощное средство создания и обработки цифровой информации и финансовых документов, которое позволяет не только автоматизировать расчеты, но также на их основе получить графическую интерпретацию.
Excel 2010 имеет новый интерфейс, который значительно облегчает работу в нем. Команды и функции находятся на ленте в проблемно-ориентированных вкладках, содержащих логические группы команд и функций. Множество диалоговых окон заменены раскрывающимися коллекциями, которые отображают доступные параметры.
В Excel 2010 можно быстро форматировать данные таблиц на листе, используя темы и конкретные стили. Темы могут совместно использоваться другими приложениями Office 2010, например Microsoft Word 2010 и Microsoft PowerPoint 2010, а стили предназначены для изменения формата элементов только в приложении Excel, например таблиц, диаграмм, сводных таблиц, фигур или диаграмм.
В Excel 2010 можно использовать новые инструменты для работы с диаграммами, облегчающие создание профессионально-оформленных диаграмм, которые могут содержать специальные эффекты, такие как объемность, прозрачность и мягкие тени. Диаграммы можно легко копировать и вставлять из документа в документ или из одного приложения в другое.
Интерфейс Excel 2010
1.1. «Лента»
Запустите Excel 2010, используя ярлык на рабочем столе или команду меню Пуск / Программы / Microsoft Office 2010 / Microsoft Excel 2010.
В основе нового интерфейса лежит так называемая «лента» (в англоязычном варианте «Ribbon») – многостраничная область, расположенная в верхней части главного окна.
Каждая вкладка «ленты» содержит набор кнопок и других управляющих элементов для работы с определенными группами инструментов Excel. При стандартной настройке «лента» содержит следующие вкладки:
«Главная» – на ней собраны инструменты, использующиеся при вводе и форматировании информации в таблицах;
«Вставка» – для вставки в таблицу какого-либо объекта (сводной таблицы, рисунка, диаграммы и т.п.);
«Разметка страницы» – для установки параметров печатной страницы;
«Формулы» – различные инструменты для вычислений в таблицах;
«Данные» – подготовка писем, конвертов методом слияния;
«Рецензирование» – проверка правописания, подготовка примечаний, рецензирование документа;
«Вид» – настройка отображения таблицы на экране монитора.
При запуске программы «лента» открыта на вкладке «Главная».
Кроме того, многие вкладки «ленты» являются контекстно-зависимыми, то есть появляются на экране только при выделении определенного объекта или установке на него курсора. Например, при добавлении диаграммы в таблицу становятся доступными вкладки «Конструктор», «Макет» и «Формат», объединенные общим заголовком «Работа с диаграммами».
Для экономии экранного пространства область «ленты», занятую кнопками, можно скрыть. Выполните двойной щелчок на активном ярлычке «ленты».
Теперь видимыми будут только закладки страниц. Щелкните на одном из ярлычков, чтобы временно развернуть соответствующую ему страницу.
Щелкните за пределами «ленты», чтобы снова свернуть страницу.
Дважды щелкните на любой закладке, чтобы вернуть «ленту» в исходное состояние.
Каждый элемент «ленты» снабжен подробной всплывающей подсказкой, которую можно вызвать, наведя курсор на соответствующий элемент.
1.2. Представление Backstage вкладки «Файл»
Основным отличием интерфейса Excel 2010 от предыдущих версий является различный доступ к основным операциям с документом. Он реализуется с помощью нового представления Backstageвкладки«Файл». Щелкните по вкладке «Файл» для просмотра представления Backstage.
В представлении Backstage осуществляется управление файлами и связанными с ними данными – создание, сохранение и отправка, настройка параметров, таких как включение или выключение автозаполнения и т. д.
Для быстрого возврата к документу из представления Backstage щелкните вкладку Главная или нажмите клавишу ESC.
Панель быстрого доступа
В верхней части главного окна находится панель быстрого доступа, предназначенная для ускорения вызова часто использующихся функций общего назначения, например, быстрое сохранение документа, отмена последнего выполненного действия и так далее.
Панель быстрого доступа можно настраивать, добавляя в неё новые команды.
В правом верхнем углу главного окна Excel расположены стандартные кнопки управления – минимизация, переключение оконного и полноэкранного режима работы, завершение работы приложения.
Основная часть таблицы
Под лентой расположена основная часть электронной таблицы.
Основную часть окна Excel занимает собственно таблица. Файл Excel называется Книгой. Книга может состоять из одного или нескольких листов. В стандартной настройке при создании Книги их три. Листы можно удалять и добавлять.
Стандартный лист имеет буквенное обозначение столбцов и цифровое строк. Лист Excel 2010 состоит из 1 048 576 строк и 16 384 столбцов, обозначения столбцов начинается с буквы А и оканчивается на XFD.
Соответственно каждая ячейка имеет собственное имя: А1, В2 и т.д.
При начальной загрузке Excel активной (выделена толстой линией) является ячейка А1. Именно в активную ячейку происходит ввод текста, чисел, формул или другой информации. Изменить активную ячейку можно щелкнув по ней мышкой или клавишами-стрелками.
Иногда необходимо выделить несколько ячеек. Такой блок называется интервалом и обозначается В2 : F5. Активной в интервале является ячейка, с которой началось выделение (ее фон остается белым).
Над таблицей в левой части расположено окно, в котором отображается имя активной в данный момент ячейки. Кроме этого, если в этом окне ввести имя другой ячейки, то эта ячейка станет активной.
Над таблицей находится строка формул. Она используется для отображения введенной в ячейку формулы (в самой таблице отображается результат вычисления) и ее редактирования.
Строка состояния
Ниже таблицы находится строка состояния. В левом углу отображаются режим, в котором сейчас находится таблица: «Ввод» – пользователь не закончил вводить информацию, «Готово» – ввод закончен.
Если в таблице выделен интервал с числовыми данными, то в строке состояния отображается среднее значение выделенных чисел, их количество и сумма.
Также в строке состояния находятся кнопки переключения режимов отображения таблицы
и регулятор, позволяющий динамически изменять масштаб отображения.
Проверьте свои знания:
1. Для чего предназначены электронные таблицы Excel?
2. Что обозначает запись «А2:Н7»?
Создание таблиц
Ввод данных и формул
Чтобы ввести информацию в ячейку необходимо выделить нужную ячейку, набрать содержимое и нажать клавишу Enter.
Замечания:
Если Вы ошиблись при вводе:
– если клавиша Enter еще не нажата, то информация пока еще не введена – используйте клавишу Backspace;
– если Enterбыла нажата – необходимо выделить нужную ячейку и нажать F2 или сделать двойной щелчок мышью.
Excel запоминает ваши последние действия при работе над таблицей, и их можно отменить. Для этого следует воспользоваться кнопками, находящимися на панели быстрого доступа 
– отменить и 
– вернуть назад отмененное действие.
Упражнение 1.
Рассмотрите варианты ввода данных различных типов:
1. введите в ячейку А2 текст: Пример;
2. введите в ячейку В4 дату: 12.08 (обратите внимание, что ввелось 12 авг);
3. введите в ячейку C4 дробное число: 3,14 (дробная часть отделяется от целой части запятой!);
4. введите в ячейку С1 число: 992, а в ячейку С2число: 551;
5. введите в ячейку С3 формулу суммы ячеек С1 и С2. Сделаем это с помощью мыши:
ü в ячейку C3 введите = (программа перешла в режим ввода формул);
ü щелкните левой кнопкой мыши по ячейке С1 (ее адрес появится в формуле);
ü введите знак математического действия +;
ü щелкните левой кнопкой мыши по ячейке C2;
ü нажмите клавишу Enter.
В результате в ячейке C3 появилась формула = C1+C2.
После нажатия клавишиEnterв ячейке появится число – итог выполнения данного действия, а в верхней части экрана – вСтроке формулвысветитсяформула = C1+C2.
Формулы в Excel автоматически пересчитываются при изменении данных в ячейках.
Убедимся в этом:
ü введите в ячейку D3 число 300, а в ячейку Е3 число 3;
ü в ячейку F3 запишите формулу =D3/E3 и нажмите Enter. Если изменить значение в какой-нибудь ячейке (D3 или E3), ячейка F3 тут же сама пересчитывается!
ü введите в ячейку D3 число 60, а в ячейку E3 число 2. Формула в ячейке F3 изменилась.
Сообщения об ошибках
В Excel 2010 существуют различные сообщения об ошибках.
ü ######## – информация не помещается в ячейку.
ü #ДЕЛ/0! – в формуле производится попытка деления на ноль.
ü #ЗНАЧ! – в формуле используется недопустимый тип аргумента (делить на текст нельзя). Примечание! Такие же ошибки появляются при использовании в формулах русских символов.
ü #ИМЯ? –Excel не может распознать имя, используемое в формуле.
ü #ЧИСЛО! – неправильно используются аргументы функции или числа в формуле.
ü #ССЫЛКА! – используется недопустимая ссылка на ячейку.
ü #ПУСТО! – ошибка при вводе ссылок на диапазон ячеек.
Сохранение таблиц
Для лучшей совместимости с различными программными системами для приложений Office 2007 разработан новый формат хранения документов, основанный на формате XML (англ. eXtensible Markup Language – расширяемый язык разметки; произносится [экс-эм-э́л]). Благодаря использованию технологий сжатия размер файлов уменьшился, а открытая спецификация формата позволяет использовать эти файлы в любой операционной среде. Файл в новом формате Excel 2007 получил расширение «xlsx». Такой же формат имеют файлы Excel 2010.
Для сохранения таблицы на вкладке «Файл» выбирается команда «Сохранить», если файл документа должен быть сохранен с тем же именем, или команда «Сохранить как», если надо задать новые параметры сохранения (изменить место сохранения, имя файла, тип файла).
Для сохранения файла в формате, совместимом с более ранними версиями Excel, в нижней части окна в выпадающем списке «Тип файла» выберите «Книга Excel 97-2003».
Файл в этом случае будет иметь расширение «xls», и работа с ним в Excel 2010 будет возможна только с некоторыми ограничениями. В заголовке окна такого документа появится надпись «Режим ограниченной функциональности» которая показывает, что данный формат не поддерживает все возможности Excel 2010.
Для сохранения документа в формате «.xlsx» в списке «Тип файла» выберите «Книга Excel».
После задания имени файла нажмите кнопку «Сохранить».
Задание.
Сохраните файл в своей папке с именем Упражнение 1.xlsx
Упражнение 2.
Подготовьте таблицу расчета продаж компьютерных товаров.
Для этого выделите интервал А1:E1и, используя кнопки группы Шрифт на вкладке Главная, установите: Шрифт – полужирный; Размер – 12 пт.
Затем, не снимая выделения с диапазона А1:Е1 в группе «Выравнивание» установите:выравнивание по горизонтали – по центру;Выравнивание по вертикали – по середине; Перенос текста – кнопка 
(для того, чтобы в одной ячейке текст отображался в две или более строки, необходимо использовать кнопку Перенос текста)
Введите данные в столбцы А – Е таблицы и отформатируйте данные согласно предложенному образцу.
Измените ширину столбцов там, где это нужно, для этого подведите указатель мыши к линии разделяющей названия столбцов (например, A и B). Указатель из крестика превратиться в стрелки 
. Удерживая нажатой левую клавишу мыши, переместите границу столбца вправо или влево, или нажмите дважды кнопку мыши.
Вставка и удаление строк, столбцов
Для вставки (удаления) строки – щелкните правой кнопкой мыши по номеру строки,например, по первой (не по клетке, а по вертикальной полосе сбоку с номерами строк!). Первая строка выделится, и появится меню – выберите команду «Вставить»или нажмите на кнопку в группе «Ячейки» на вкладке «Главная».
Вставка (удаление) столбца проделывается аналогично – только мышью надо щелкнуть по имени столбца.
Для вставки (удаления) нескольких столбцов (строк) их нужно предварительно выделить как интервал, остальное – аналогично.
Задание:
Вставьте в начало таблицы новую строку для заголовка таблицы.
Объединение ячеек
Выделите ячейки А1 : Е1и объедините их нажав на кнопку – 
«Объединить и поместить в центре» в группе «Выравнивание» на вкладке «Главная». Введите текст Заголовка таблицы:Продажа товаров.
Отформатируйте текст Заголовка:
Размер шрифта – 14; Начертание – полужирное.
Введите в таблицу, используя мышь, необходимые формулы:
ü в ячейку Е3нужно ввести формулу= C3*D3.Для этого:
ü в ячейку Е3 введите = (программа перешла в режим ввода формул);
ü щелкните левой кнопкой мыши по ячейкеС3 (ее адрес появится в формуле);
ü введите знак математического действия *;
ü щелкните левой кнопкой мыши по ячейке D3;
ü нажмите клавишу Enter.
Выделив ячейку E3, скопируйте ее на весь столбец, для этого: Щелкните по клетке Е3. Подведите указатель мыши к точке в правом нижнем углу клетки, чтобы указатель из толстого крестика превратился в тонкий (он называется маркером заполнения).
Держа нажатой левую кнопку мыши, тащите указатель вниз. Когда отпустите – клетка Е3 скопируется.
Внимание!
При копировании формул происходит автоматическое изменение ссылок на ячейки, используемые в формуле. Например, если в некоторую ячейку была записана формула =А2*В2 и эту формулу скопировать вниз, то в следующей ячейке получится формула =А3*В3, в следующей =А4*В4 и т. д. Если же эту формулу скопировать вправо, то в соседней ячейке будет =В2*С2, в следующей =C2*D2 и т. д.
Подводя итог – обычные ссылки на ячейки (А1, В2 и т. п.) изменяются при копировании формул: вниз – увеличивается на единицу цифра, вправо – буква изменяется на следующую. Такие ссылки называются относительными.
В ячейку D8 для вычисления суммы введите функцию Автосумма, для этого: выделите ячейку D8 и щелкните мышью по кнопке S на вкладке «Главная» в группе «Редактирование».
В строке формул появится =СУММ(D3:D7) – в скобках указывается интервал ячеек, которые программа предлагает просуммировать. Если этот интервал Вас устраивает, нажмите Enter. Если вам нужен другой интервал клеток для суммирования – измените его, выделив мышью необходимые ячейки.
Скопируйте способом указанным выше формулу из ячейки D8 в ячейку Е8.
Форматирование чисел
Основным отличием таблиц Excel от таблиц Word является большие возможности по отображению числовых данных. Все команды по форматированию чисел собраны в группу «Число» на вкладке «Главная».
Щелкнув по кнопке «Общий», получите все виды представления чисел. Наиболее часто используемые:
ü Общий – числа отображаются без форматирования;
ü Числовой – позволяет округлять дробные числа;
ü Денежный – позволяет добавить к числу обозначение денежных единиц (р., $ и др.);
ü Процентный – умножает число на 100 и добавляет знак % и др.
Для выбора конкретных настроек формата, воспользуйтесь командой «Другие числовые форматы» или выбрав в контекстном меню, команду «Формат ячеек» вкладка «Число».
Задание: установите в ячейках С3:С7 и Е3:Е8:
ü Формат – Денежный;
ü Обозначение – $ Английский;
ü Число десятичных знаков – 0.
Границы таблиц
Для обрамления таблиц выделите ячейки, границы которых необходимо отобразить и воспользуйтесь на вкладке «Главная» в группе «Шрифт»кнопкой«Границы».
Обрамление таблиц можно также выполнить, воспользовавшись в контекстным меню командой «Формат ячеек» и выбрав вкладку «Граница».
Задание: Выделите ячейки А2:Е8 и установите нужное обрамление таблицы:
ü сначала выберите команду «Все границы»;
ü затем «Толстая внешняя граница».
В результате таблица должна выглядеть следующим образом:
Сохраните результат в своей папке c именем Упражнение 2.
Закройте файлУпражнение 2.
Упражнение 3.
Откройте с диска, указанного преподавателем, файл Раскрываемость (меню Файл / Открыть).
Объедините ячейки А1 – E1 (см. предыдущее упражнение) и отформатируйте текст Заголовка, установив:
ü Шрифт – Cambria;
ü Размер шрифта – 12;
ü Начертание – полужирный курсив.
Установите в ячейках А2– E2:
ü Шрифт – Calibri;
ü Размер – 12;
ü Начертание – полужирный;
ü Выравнивание – по центру;
ü Перенос текста.
Отформатируйте таблицу согласно предложенному образцу:
Введите в таблицу необходимые формулы:
ü в ячейкуЕ3введите формулу= D3/C3(для вычисления процента раскрываемости);
ü скопируйте эту формулу до ячейкиЕ17;
ü в ячейкахС18иD18вычислите строку «итого» (используйте Автосуммирование);
ü установите для E3:E17 процентный формат
В результате ваша таблица должна будет выглядеть следующим образом.
Сохраните результат в своей папке под именем Упражнение 3.
Упражнение 4.
Откройте с диска, указанного преподавателем, файл Ведомость.
Подготовьте ведомость для выдачи зарплаты в соответствии с образцом. Внимание! В столбцах № 7-8 все данные должны рассчитываться по формулам.
Формулы для расчета:
ü Сумма доплаты за сложность и напряж. = Оклад * (% доплаты за сложн. и напряжен.);
ü Итого = Оклад + (Доплата за звание) + (Сумма доплаты за сложность и напряжен.);
После ввода формул скопируйте их с помощью мыши (см. Упражнение 2)
Разверните текст в ячейках D2 и Е2: нажмите на кнопку «Ориентация» в группе «Выравнивание» на вкладке «Главная» и установите ориентацию «Повернуть текст вверх».
Отформатируйте таблицу в соответствии с образцом.
Сохраните результат в своей папке с именем Упражнение 4.
Проверьте свои знания:
1. Как установить отображение текста в несколько строк в одной ячейке?
2. Где находится Маркер заполнения (копирования)?
3. Что означает ввод в ячейку знака "="?
4. Как войти в режим редактирования формул?
5. Какое число будет записано в ячейку, если от процентного формата "25%" перейти к денежному?
Абсолютные ссылки
Как уже было сказано выше в Excel при копировании формул происходит автоматическое изменение ссылок на ячейки, используемые в формуле. Но в некоторых формулах это не только не нужно, но и вызовет ошибку при копировании. Например, когда в таблице происходит умножение или деление на одно и то же число. Для этого в формуле используются, так называемые, абсолютные ссылки.
Абсолютная ссылка получается преобразованием относительной, для этого в формуле выделяется относительная ячейка (например, В2) и нажимается клавиша F4 (получится $B$2). Такая ссылка не изменяется при копировании формулы ни вниз, ни вправо.
Упражнение 5.
Откройте созданный ранее файл Упражнение 3 из своей папки.
Добавьте столбец Fв вашу таблицу и отформатируйте ее согласно образцу.
Подсказка.
Для того чтобы текст в ячейке F2 выглядел аналогично остальным ячейкам шапки таблицы, можно воспользоваться кнопкой «Формат по образцу». Она позволяет скопировать внешний вид из одной ячейки в другую. Выделите ячейку Е2, нажмите кнопку «Формат по образцу» в группе «Буфер обмена» на вкладке «Главная» (курсор примет вид кисточки) и щелкните по ячейке F2.
Подсчитайте в ячейкахF3:F17удельный вес раскрытых преступлений. Для этого в ячейку F3 введите формулу =D3/D18.
Скопируйте эту формулу до ячейкиF17.Мы получим сообщение об ошибке.
Посмотрите формулы в ячейкахF4 : F17– в чем ошибка?
Внимание! Для того чтобы при копировании введенной в ячейку F3формулы не возникало ошибок необходимо, чтобы адрес ячейки D18не изменялся при копировании, то есть был абсолютной ссылкой.
Исправим ошибку! Выделите ячейку F3и в строке формулподведите курсор в этой формуле к D18
нажмите клавишу F4 на клавиатуре. Формула примет вид: =D3/$D$18. Нажмите клавишу Enter.
Теперь, выделив ячейку F3, еще раз скопируйте до ячейки F17. Ошибки исчезли!
Установите процентный формат для ячеек F3:F17.
Сохраните результат в своей папке с именемУпражнение 5.
Упражнение 6.
Откройте созданный ранее файл Упражнение 4 из своей папки или диска, указанного преподавателем.
Вставьте в начало таблицы новую строку – для вычисления подоходного налога и введите туда нужные данные.
Добавьте заголовки в ячейки I3, J3 и отформатируйте ее согласно образцу. Рассчитайте с помощью формул данныев столбцах 9-10.
Формулы для расчета:
ü Подоходный налог = Итого*(на % Подоходного налога – ячейка F2). Подсказка! Учтите, процент подоходного налога – величина постоянная для всех работников, нужны абсолютные ссылки;
ü Сумма к выдаче = Итого – (Подоходный налог);
ü Всего = Автосуммирование столбца (Сумма к выдаче).
Сохраните результат в своей папке с именемУпражнение 6.
Упражнение 7.
Подготовьте смету затрат на бензин для проезда на автомобиле из Москвы в другие города. Таблица должна быть универсальной для любого расхода бензина и цены за литр.
Для этого, откройте с сетевого диска файл Бензин.
Рассчитайте столбец«Затраты на бензин».
Подсказка! Чтобы рассчитать стоимость бензина необходимо «Расстояние в км» разделить на 100, умножить на «Расход бензина на 100 км» и умножить на «Цена 1 л. бензина». (Внимание! Не забудьте об абсолютных ссылках).
Отформатируйте и установите обрамление таблицы, применив к числам денежный формат. В результате вы получите следующую таблицу (приведен только фрагмент таблицы):
Сохраните результат в своей папке под именем Упражнение 7.
Упражнение 8.
Подготовьте прайс-лист стоимости товаров в зависимости от курса доллара.
Для этого откройте с сетевого диска файл Прайс-Лист.
Введите формулу расчета рублёвой цены товара. Не забудьте про абсолютную ссылку на значение курса доллара.
Разместите в таблице рисунок. Для этого перейдите на вкладку «Вставка» и нажмите на кнопку «Картинка» в группе «Иллюстрации».
Отформатируйте таблицу в соответствии с образцом (Внимание! Приведен только фрагмент таблицы).
Поменяйте курс доллара на 31,5р. и посмотрите, изменились ли значения в таблице.
Сохраните результат в своей папке с именем Упражнение 8.
Упражнение 9.
Откройте созданный Вами ранее файл Упражнение 2.
Преобразуйте таблицу в ежедневный отчет о продажах.
Для этого измените заголовок таблицы (Подсказка! Для того чтобы текст заголовка отображался в несколько строк установите курсор в месте разрыва строки и воспользуйтесь клавишами принудительного переноса – Alt + Enter).
Вставьте в начало таблицы строку, добавьте необходимые данные и отформатируйте их.
Добавьте недостающие данные в шапку таблицы и формулы расчета для ячеек F4:H4:
ü Сумма (в руб.) = Сумма ($) * Курс доллара;
ü НДС (в руб.) = Сумма (в руб.) * НДС;
ü Итого сумма с НДС (в руб.) = Сумма (в руб.)+ НДС (в руб.);
ü Скопируйте введенные формулы вниз.
Отформатируйте таблицу в соответствии с образцом.
Вставьте рисунок.
В ячейку Е11 введите текст «Общая сумма продаж в $:», а в ячейку Е12 – «В рублевом эквиваленте:». В ячейки F11 и F12 введите формулы: =Е9 и =Н9 соответственно.
В результате должна получится следующая таблица:
Поменяйте курс доллара на 31,20р., НДС на 15% и посмотрите – изменился ли Ваш отчет.
Сохраните таблицу на диске с именем Упражнение 9.
Проверьте свои знания:
1. В чем состоит отличительная черта абсолютных ссылок?
2. Адрес ячейки имеет вид: $А$5. Какая это ссылка?
3. С помощью какой клавиши относительная ссылка преобразуется в абсолютную?
Использование функций
Функции в Excel – заранее определенные формулы, выполняющие вычисления в указанном порядке по заданным величинам, называемыми аргументами.
Все инструменты для работы с функциями находятся на вкладке «Формулы».
Для вставки функции используется «Мастер функций». Для запуска мастера необходимо нажать на кнопку «Вставить функцию».
Также мастера функций можно запустить, нажав на кнопку fx в строке формул.
Мастер функций работает в два шага. Шаг 1 – выбор необходимой функции.
Для удобного выбора нужной функции они разбиты по категориям (Финансовые, Дата и время, Математические, Статистические и др.). Если пользователь не знает, к какой категории принадлежит необходимая функция, можно выбрать «Полный алфавитный перечень».
При выделении названия функции в нижней части отображается краткое описание этой функции.
После выбора нужной функции нажимается кнопка «ОК» и мастер переходит ко второму шагу «Аргументы функции».
Аргументы можно вводить вручную, а можно щелкать по нужным ячейкам или выделять нужные диапазоны. (Примечание: у некоторых функций нет аргументов).
После ввода аргументов нажимается кнопка «ОК».
Упражнение 10.
Откройте с сетевого диска файл Dinamika, отформатируйте его в соответствии с образцом.
Заполните нижние три строки с помощью функций.
Выделите ячейку В12 и нажмите кнопку «Вставить функцию». В категории «Статистические» выберите функцию МАКС и нажмите кнопку «ОК».
При выборе аргументов обратите внимание, что Мастер функций предлагает в качестве аргументов ячейки В3:В11, но это не правильно!
Исправим это – щелкните по кнопке, находящейся в правой части окна ввода аргумента.
Окно Мастера функций свернется, открыв доступ к таблице. Выделите мышкой необходимый диапазон – В4:В11.
Для возврата в мастер еще раз нажмите на кнопку в правой части окна «Аргументы функций» или нажмите Enter. Нажмите на кнопку «ОК» для окончательного ввода функции.
Аналогично вставьте функцию МИН из категории «Статистические» в ячейку В13 и функцию СУММ из категории «Математические» в В14.
Функции, также как и формулы можно копировать. Поэтому выделите ячейки В12:В14 и скопируйте их с помощью маркера заполнения в столбцы С и D.
Введите в ячейку Е4 формулу расчета удельного веса: =D4/D14. Скопируйте формулу до ячейки Е11 (не забудьте об абсолютных ссылках).
Вставьте в начало таблицы строку и введите заголовок «Динамика преступности по Нижегородской области».
Отформатируйте таблицу в соответствии с образцом.
Сохраните таблицу в своей папке с именем Упражнение 10.
Упражнение 11.
Откройте с сетевого диска файл Экология.
Рассчитайте столбец F, для этого из количества «Всего отходящих веществ от всех стационарных источников»надо вычесть данные столбца «Из поступивших на очистные сооружения уловлено и обезврежено».
Нижние четыре строки заполните с помощью функций МАКС, МИН, СРЗНАЧ из категории «Статистические» и СУММ из категории «Математические».
Отформатируйте таблицу по образцу:
Сохраните таблицу в своей папке под именем Упражнение 11.
Упражнение 12.
Откройте с сетевого диска файл Компьютер-прайс.
В ячейке Е2 дата должна изменяться автоматически, для этого вставьте функцию СЕГОДНЯ из категории «Дата и время».
Отформатируйте таблицу по образцу:
Для расчета данных введите в ячейки нужные формулы и скопируйте их:
Цена в $ = (Цена в руб.) разделитьна(Курс доллара).
Минимальные цены рассчитываются с помощью функции МИН.
При вычислении Итогов прайса используйте функцииСУММА, МАКС, МИН с несколькими диапазонами аргументов. Внимание! При перечислении нескольких диапазонов используйте знак ; Например: =МИН(D5:D15;D18:D30;D33:D38)
Сохраните таблицу в своей папке с именем Упражнение 12.
Проверьте свои знания:
1. Что такое Мастер функций?
2. Как запустить мастер функций?
3. Какие бывают категории функций?
4. Что такое аргументы функций? Можно ли использовать функции с несколькими аргументами?
5. Бывают ли функции с автоматическим изменением?
Диаграммы
Диаграмма предназначена для визуального представления данных. С помощью различных элементов (столбцов на гистограмме или линий на графике) диаграмма отображает ряды числовых данных в графическом виде. Excel автоматически подбирает оптимальный способ расположения данных на диаграмме.
В Excel 2010 появились новые инструменты для работы с диаграммами, облегчающие создание профессионально-оформленных диаграмм. Основанные на используемой в книге Excel теме, новые формы диаграмм содержат специальные эффекты, такие как объемность, прозрачность и мягкие тени. Все функции построения диаграмм, которые предлагались раньше в Microsoft Graph, Word и PowerPoint, теперь входят в средства построения диаграмм Excel.
Диаграммы можно легко копировать и вставлять из документа в документ или из одного приложения в другое.
Для вставки диаграммы необходимо выделить данные, которые должны отображаться в ней и выбрать тип диаграммы, щелкнув по соответствующей кнопке в группе «Диаграммы» на вкладке «Вставка».
Для дальнейшей настройки диаграммы (добавления различных элементов, например заголовков или меток данных, а также изменения внешнего вида, структуры и формата диаграмм), после вставки диаграммы, появляются три вкладки: «Конструктор», «Макет» и «Формат», объединенных в группу «Работа с диаграммами».
Упражнение 13.
Откройте с сетевого диска файл Численность рабочих.
Постройте круговую диаграмму, используя данные из таблицы. Для этого выделите всю таблицу (ячейки А1:В11), перейдите на вкладку «Вставка» и, нажав на кнопку «Круговая», выберите простую круговую диаграмму.
Круговые диаграммы показывают вклад каждого значения в общую сумму. Этот тип диаграмм используется, если имеется только один ряд данных, все значения которого являются положительными.
В результате получится следующая диаграмма.
Если место или размер диаграммы вас не устраивает, можно перетащить диаграмму или изменить её пропорции (растянуть или сжать, «схватив» за квадраты по углам и в середине рамки)
Сохраните результат в своей папке с именем Упражнение 13.
Упражнение 14.
Для освоения работы с диаграммами воспользуемся таблицей, созданной в предыдущем упражнении. Откройте, созданный в предыдущем упражнении, файл Упражнение 13.
Снова выделите всю таблицу и вставьте объемную круговую диаграмму.
Для изменения легенды перейдите на вкладку «Макет» и, нажав на кнопку «Легенда», выберите «Добавить легенду снизу».
Изменить легенду также можно выделив ее и воспользовавшись контекстным меню мыши «Формат легенды»
Для добавления или изменения подписей данных нажмите на кнопку «Подписи данных» на вкладке «Макет». В появившемся списке можно выбрать расположение подписей относительно диаграммы. Поэкспериментируйте с различными способами расположения и посмотрите, как будет меняться диаграмма.
Для изменения параметров подписей данных нажмите на кнопку «Подписи данных» и выберите «Дополнительные параметры подписей данных». В появившемся окне «Формат подписей данных» установите «галочки» включать в подписи – доли, положение подписи – у вершины, снаружи.
Изменить подписи данных также можно выделив их и воспользовавшись контекстным меню мыши «Формат подписей данных».
Что делать, если вам не нравится что-либо в диаграмме: цвет, вид, шрифт и т. д.?
Нужно выделить интересующую область и перейти на вкладку «Формат».
На этой вкладке расположены кнопки для изменения внешнего вида, начертания, заливки, начертания, эффекты фигур и другие настройки формата выделенной области диаграммы.
В круговых диаграммах любой сектор можно сделать «вынутым» простым перетаскиванием мышью. Проделайте эту процедуру для двух округов: Северного и Южного, измените их цвет на ярко-зелёный и красный. В результате должна получится следующая диаграмма.
В заключение попробуйте самостоятельно создать объёмную гистограмму.
Гистограммы полезны для представления изменений данных с течением времени и для наглядного сравнения различных величин. В гистограммах категории обычно формируются по горизонтальной оси, а значения – по вертикальной
Гистограмма должна выглядеть примерно так.
Сохраните результат на диске с именем Упражнение 14.
Упражнение 15.
Построение диаграмм по части таблицы.
Откройте подготовленный вами файл Упражнение 11 и постройте обычную гистограмму для данных следующих столбцов: С («Всего отходящих веществ»), Е («Уловлено на очистных сооружениях).
Для этого выполните следующее:
Выделите ячейки с данными А2:А12 (для формирования легенды), С2:С12 и Е2:Е12. Поскольку выделение информации не сплошное, а выборочное – делать это нужно, удерживая нажатой клавишу Ctrl.
После построения гистограммы переместите гистограмму на другой лист. Для этого, нажмите на кнопку «Переместить диаграмму» на вкладке «Конструктор».
В появившемся окне выберите размещение гистограммы «на отдельном листе» – Диаграмма1.
Отредактируйте диаграмму, подобрав оптимальные размеры шрифтов, размещение легенды.
В результате вы должны получить следующую диаграмму:
Сохраните результат в своей папке под именем Упражнение 15.
Упражнение 16.
Откройте подготовленный вами файл Упражнение 15 и видоизменим диаграмму.
1. Добавим в гистограмму ещё один ряд данных: столбец F («Всего попадает в атмосферу»). Для этого выделите диаграмму и нажмите на кнопку «Выбрать данные» на вкладке «Конструктор».
В левой части окна «Элементы легенды (ряды)» нажмите кнопку «Добавить».
В появившемся окне в поле «Имя ряда» необходимо указать ячейку F2 на листе Лист1. Для этого нажмите на кнопку в правом углу поля – это действие вернёт вас в таблицу Excel. Перейдите на Лист1, щелкнете по ячейке F2 и нажмите <Enter>, и в поле имени ряда появится его название.
Аналогично в поле «Значения:» ведите ячейки F3:F12.
Нажмите кнопку «ОК».
2. Удалим первый ряд данных – «Всего отходящих веществ от всех стационарных источников». Для этого выделите этот ряд и нажмите кнопку «Удалить».
Нажмите кнопку «ОК».
3. Изменим тип диаграммы.
Нажмите кнопку «Изменить тип диаграммы» на вкладке «Конструктор» и выберите гистограмму с накоплением.
Нажмите кнопку «ОК».
Смените цвет: для обезвреженных выбросов – зелёный, для попадающих в атмосферу – красный.
У вас должно получиться примерно следующая диаграмма.
Сохраните результат под именем Упражнение 16.
Проверьте свои знания:
1. Какие типы диаграмм существуют в Excel 2010?
2. Что такое легенда в диаграмме? Можно ли настраивать легенду?
3. Что такое гистограмма? Какие они бывают?
4. Как выделить выборочный диапазон значений для построения диаграммы?
Работа с данными
Сортировка данных
Для расположения данных в таблицах в нужном порядке используется сортировка.
В Excel 2007 и 2010 возможности сортировки, по отношению к предыдущим версиям, расширены: появилась возможность сортировать по большему, чем трем (Excel 2003), количеству уровней. В Excel 2010 можно использовать вплоть до 64 уровней сортировки. Также появилась возможность сортировать данные по цветам ячейки или шрифта.
Для сортировки необходимо выделить данные, нажать кнопку «Сортировка и фильтр» на вкладке «Главная» и выбрать соответствующий вид сортировки.
Кнопки сортировки также расположены на вкладке «Данные» в группе «Сортировка и фильтр».
Упражнение 17.
Откройте с сетевого диска файл Сортировка.
Требуется расположить обучаемых в порядке возрастания номеров их групп.
Воспользуемся «простой» сортировкой. Так как таблица представляет собой, так называемые «списки данных», то есть заголовок состоит из одной строки и все остальные строки содержат данные, то для сортировки достаточно щелкнуть по любой ячейке в столбце, по которому необходимо отсортировать, и нажать на кнопку сортировки.
В нашем случае щелкните по любой ячейке в столбце А (например, А1 или А2 и т.д.) и нажмите на кнопку «Сортировать от минимального к максимальному».
В результате в таблице сначала будут расположены учащиеся 301 группы, за ними 302 и далее 303.
Самостоятельно расположите обучаемых в порядке убывания итоговой оценки.
Сохраните результат в своей папке под именем Упражнение 17.
Упражнение 18.
Откройте подготовленный вами файл Упражнение 10 и расположите районы в порядке убывания числа раскрытых преступлений (столбец D).
Самостоятельно попробуйте выполнить «простую» сортировку. Excel выдал сообщение о невозможности операции.
Причина возникновения сообщения в том, что данная таблица не является списком – шапка состоит из трех строк, в конце таблицы три строки статистических данных.
В таких таблицах пользователю требуется самостоятельно выделить данные для сортировки. Выделять нужно данные не только того столбца, по которому необходимо провести сортировку, но и все связанные с ним данные. В нашем случае выделите ячейки А4:Е12 илиА5:Е12 и нажмите кнопку «Сортировка» на вкладке «Данные»
или «Настраиваемая сортировка…» на вкладке «Главная».
В появившемся окне важно установить галочку «Мои данные содержат заголовки» в случае если выделение захватывает строку заголовков (А4:Е12) и убрать эту галочку в противном случае (А5:Е12).
Установите сортировку по 5 столбцу в порядке убывания.
Нажмите кнопку «ОК».
В результате таблица примет следующий вид.
Сохраните результат в своей папке под именем Упражнение 18.
Фильтрация
При работе с большими таблицами требуется иногда скрыть «лишние» данные. Такая операция называется фильтрацией.
Для фильтрации необходимо выделить данные, нажать кнопку «Сортировка и фильтр» на вкладке «Главная» и выбрать команду «Фильтр».
Кнопки фильтрации также расположены на вкладке «Данные» в группе «Сортировка и фильтр».
Упражнение 19.
Откройте с сетевого диска файл Продукты.
Отформатируйте таблицу на свой вкус.
Используя фильтрацию, необходимо отобразить на экране информацию о продаже всех продуктов в 1993 году продавцом Петровой. Для этого выделите всю таблицу и нажмите кнопку «Фильтр». В каждой ячейке заголовка появится кнопка раскрытия списка в виде маленькой стрелочки.
В ячейке В1 нажмите на стрелочку и в нижней части списка установите галочку напротив 1993, а остальные уберите.
Аналогично установите для столбца «Продавец» – Петрова. В результате вы получите следующую таблицу.
Для того чтобы отменить процедуру фильтрации для отдельного столбца нужно установить галочку «(Выделить все)».
Задание: Определить, у кого из продавцов сбыт по колбасе был больше 20 000 тыс. рублей
Для столбца «Продукты» нажмите на стрелочку и выберите «Колбаса», а в столбце «Сбыт» выберите «Числовые фильтры», затем «больше…».
В появившемся окне задайте условие «больше 20000».
Для отмены фильтрации необходимо еще раз нажать на кнопку «Фильтр».
Сохраните результат под именем Упражнение 19.
Упражнение 20.
Задание: Семейная пара мечтает о поездке в Париж вместе со своим сыном. В туристической компании, в которую они обращаются, есть несколько вариантов поездки. Подготовьте таблицу для коммерческого предложения турфирмы.
Откройте с сетевого диска файл Париж.
Рассчитайте и отформатируйте таблицу по образцу.
Подсказка: Для расчета стоимости всей поездки (ячейка Е6) используйте следующую формулу =D6*(B1+B2-B2*B3)
Не забудьте в формулах использовать абсолютную адресацию ячеек.
Задание: Используя фильтрацию (см. предыдущее упражнение) помогите семейной паре выбрать вариант. Они могут себе позволить потратить не более 7 600 р. в день, но при этом хотят проживать в трёхзвёздочном отеле.
Сохраните результат под именем Упражнение 20.