Е. М. Карчевский, И. Е. Филиппов

Excel 2007 в примерах

Учебно-методическое пособие

Казанский федеральный университет

2010

1

СОДЕРЖАНИЕ

ПЕРВЫЙ УРОК

Первое знакомство

Работа с диаграммами

Работа с формулами и функциями

Сортировка строк по столбцам

ВТОРОЙ УРОК

Работа со списками

Работа с диаграммами

Работа с функциями

ТРЕТИЙ УРОК

Работа с функциями

Сортировка

Шаблоны

ЧЕТВЕРТЫЙ УРОК

Фильтрация

Работа с диаграммами

Вычисления и функции

ПЯТЫЙ УРОК

Трехмерные ссылки

Консолидация данных.

Работа с диаграммами

ШЕСТОЙ УРОК

Создание сводной таблицы

Функция «ЕСЛИ»

Работа с диаграммами

СЕДЬМОЙ УРОК

Диаграммы

Абсолютные и относительные ссылки

Использование фильтров

ВОСЬМОЙ УРОК

Защита нескольких ячеек рабочеголиста

Защита рабочего листа за исключением нескольких ячеек

Проведение анализа документа.

ДЕВЯТЫЙ УРОК

Таблицы подстановок

Анализ «что-если».

Работа со сценариями.

Поиск решений

ДЕСЯТЫЙ УРОК

Базы данных в Excel

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

При запуске Excel на экране появляется окно с пустой таблицей. При первом запуске этот документ имеет стандартное имя Книга 1, которое указывается в строке заголовка.

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

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

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

Вкладки листов. Имена листов отображаются на вкладках в нижней части окна книги.

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

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

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

Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид.

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

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

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

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

Создание, закрытие и сохранение файлов    Создание книги 1. Нажимаем кнопку Office и выбираем Создать.

2. Чтобы создать новую пустую книгу, выберите вкладку Пустые и последние и дважды щелкните Новая книга.

Открытие книги 1. Нажимаем кнопку Office и нажимаем Открыть или просто нажимаем кнопку Открыть на панели быстрого доступа.

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

3. Нажмите кнопку Открыть.

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

Во время каждого последующего сохранения книга будет обновляться в соответствии с внесенными в нее изменениями. Для этого можно просто нажать кнопку Сохранить Сохранение новой книги 1. Выберите команду кнопка Office /Сохранить как и выберите необходимый вид сохранения документа (например, Книга Excel).

2. В окне Сохранение документа укажите диск и папку, в которую будет помещена 3. В поле Имя файла введите имя книги.

4. Нажмите кнопку Сохранить.

Закрытие книги Выберите команду Кнопка Office/ Закрыть.

Ввод и форматирование данных    Ввод данных 1. Укажите ячейку, в которую необходимо ввести данные.

2. Наберите данные и нажмите клавишу ENTER.

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

2. Измените содержимое ячейки.

3. Для сохранения изменений нажмите клавишу ENTER. Для отмены изменений нажмите клавишу ESC.

Очистка ячейки 1. Выделите ячейки, строки или столбцы, которые следует очистить.

2. В меню Главная в подменю Редактирование выберите команду Очистить, а затем — Все, Содержимое, Форматы или Примечания Работа с диаграммами    Создание диаграммы  Диаграмму можно создать на отдельном листе или поместить в качестве внедренного объекта на лист с данными. Создадим таблицу:

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

1. Поместите курсор мыши в рабочую область программы. Наберите в ячейке C3 имя поля: квартал1. Выделите ячейку С3, далее нажмите на черный квадратик, находящийся в правом нижнем углу ячейки, затем, не отпуская, кнопку мыши перетащите курсор до ячейки F3. У вас должно получиться следующее:

2. В ячейки B4 и B5 введите имена полей: плановые и фактические соответственно Должно получиться так:

3. В диапазон ячеек С4:F5 ввести числовые данные.

4. Выделите ячейки B3:F5 и установите шрифт Times New Roman 12 пунктов, а затем выровняйте по центру.

В итоге мы получим исходную таблицу.

Далее выполним команду:

1. Выделить введенные данные, далее Вставка/(Диаграммы) Гистограмма/Гистограмма с группировкой.

Такая должна получится диаграмма:

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

Значения оси. Microsoft Excel 2007 создает значения оси на основе указанных данных.

В приведенном примере значения оси изменяются от 0 до 100000, что соответствует значениям ячеек диапазона на листе.

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

Маркеры данных. Маркеры данных одного цвета представляют один ряд данных.

Каждый маркер соответствует одному значению данных листа.

Область диаграммы. Вся диаграмма и ее элементы.

Область построения. На плоских диаграммах — ограниченная координатными осями область, содержащая все ряды данных. На объемных диаграммах — ограниченная координатными осями область, содержащая все ряды данных, имена категорий, подписи делений и названия осей.

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

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

Изменение типа диаграммы.  1. Для того чтобы изменить тип диаграммы, щелкните на диаграмму.

2. Выполните команду Конструктор/(Тип) Изменить тип диаграммы/Объемная коническая.

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

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

1. Щелкните левой кнопкой мыши на область диаграммы.

2. При необходимости заливки фигуры выберите Формат/(Стили фигур)Заливка фигуры и укажите нужный вид во вкладках Градиентная или Текстура.(В нашем примере Формат/(Стили фигур)Заливка фигуры/Желтый цвет 3. При необходимости изменения вида диаграммы пользуйтесь вкладками Конструктор, Макет или Формат.

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

Для того, чтобы у вас получилось также, выполните следующие команды:

1. Щелкните левой кнопкой мыши на область диаграммы.

2. Выполните команду Формат/(Стили фигур)Заливка фигуры/Рисунок.

3. В появившемся окне Вставка рисунка выберите диск или папку, где находится нужный рисунок, а затем дважды щелкните его.

4. Далее вы полните команду Макет/Текущий фрагмент. Выберите Область построения. Далее Формат выделенного фрагмента/Нет заливки.

Работа с формулами и функциями    Вставка формул.  В Microsoft Excel 2007 можно использовать формулы, которые интегрированы в систему. Для этого достаточно выполнить следующую команду Формулы/Библиотека функций и выбрать нужную вам функцию. Также можно вставлять функцию вручную. Далее этот способ будет описан в примере.

Ввод формулы, содержащей функцию.

1. Создайте таблицу для подсчета одной единицы товара и общей стоимости товаров:

2. Щелкните дважды левой кнопкой мыши на ячейку E4.Нажмите знак равенства(=),затем нажмите левой кнопки мыши на ячейку C4, потом поставьте знак «звездочку»(*),а после левой кнопкой нажмите на ячейку D4.

3. Нажмите ENTER.

4. Выделите ячейку E4, далее нажмите на черный квадратик, находящийся в правом нижнем углу ячейки, затем, не отпуская, кнопку мыши перетащите курсор до ячейки 5. Выделите ячейку E8. Далее выполняем: Формулы/Библиотека функций/Автосумма.

6. Нажмите ENTER.

Если получилась такая таблица, то вы все выполнили верно.

Сортировка строк по столбцам    Лучше всего, если сортируемый список будет иметь заголовки столбцов.

Создайте таблицу данного типа:

1. Выделите ячейки, которые требуют сортировки. В нашем случае ячейки A2-A6.

2. Выполните команду Данные/Сортировка и фильтр/Сортировка.

3. Укажите в выплывшем окне Сортировка в полях: Столбец Сортировать по Ф.И.О., Сортировка Значение и Порядок От А до Я.

4. Нажмите кнопку OK.

В итоге получаем такую таблицу:

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

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

Создадим документ следующего вида:

5. Создайте новую книгу. Для этого выполните команду Кнопка Office/Создать/Новая книга и нажмите Создать.

6. Поместите курсор мыши в рабочую область программы. Наберите в ячейках А1- Е имена полей: фамилия, имя, отчество, адрес, телефон.

7. Выделите ячейки А1-Е1 и установите шрифт Times New Roman 14 пунктов, а затем выровняйте по центру.

8. Заполните все остальные ячейки шрифтом Arial 10 пунктов.

9. Установите внутренние и внешние границы для таблицы. Для этого выделите таблицу и выберите команду Главная/(ячейки)Формат/Формат ячеек. Затем выберите вкладку Граница и щелкните на кнопки: Внешние и Внутренние.

10. Отрегулируйте ширину столбцов по содержимому. Для этого выделите таблицу и выполните команду Главная/(ячейки)Формат/Автоподбор ширины столбца.

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

Сортировка.  При вводе записей можно не обращать внимание на их порядок, т.к. в дальнейшем есть возможность сортировать списки.

1. Для того чтобы программа Microsoft Excel 2007 обнаружила список нужно щелкнуть на любой ячейке, относящейся к списку.

2. Выполните команду Данные/(сортировка и фильтр)Сортировка. После чего программа сама выделит таблицу.

3. В окне Сортировка выберите сортировать по Фамилия, затем Сортировка/Значения, в последнюю очередь Порядок/От А до Я. Нажмите кнопку Получим документ следующего вида:

В Microsoft Excel 2007 можно производить многоуровневую сортировку, когда совпадают одно или несколько полей.

Сортировка данных.  Создадим документ следующего вида:

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

1. Выделите всю таблицу.

2. Выполните команду Данные/(сортировка и фильтр)Сортировка. В выпадающем списке Сортировать по выбрать "Музеи", установить Сортировка/Значение и Порядок/По убыванию. Далее Добавить уровень в выпадающих списках Затем по выбрать соответственно "Театры" и "Магазины". Установить переключатели в позиции По убыванию. Нажмите кнопку OK.

В результате у вас получиться таблица следующего вида:

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

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

Включите в выделение заголовки.

2. Выполните команду Вставка/(диаграммы) Круговая диаграмма.

3. Выберите тип диаграммы - круговую объемную.

4. Поместите диаграмму в нужное место (желательно, чтобы она не закрывала таблицу).

5. Выполните Макет/(подписи)Легенда/Нет.

6. Далее выполните Макет/(подписи)Подписи данных/Дополнительные параметры подписей данных.

7. Уберите флажок со строки Значения и установите его в строке Имена категорий.

Нажмите Закрыть.

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

9. Сохраните полученный документ на диске С: в папке Учебные документы под названием Построение диаграммы.

Создадим документ следующего вида:

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

1. Выделите ячейки A-F. Выполните команду Главная/(ячейки)Формат/Ширина столбца. Задайте ширину столбца 12 см и нажмите кнопку ОК.

2. В первой строке выделите ячейки A-F.

3. Выполните Главная/(выравнивание) Объединить и поместить в центре и напечатайте заголовок (размер шрифта 14 пунктов).

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

5. Введите значения ячеек таблицы.

6. На основе таблицы создайте диаграмму. Для этого выделите таблицу, выберите Вставка/(диаграмма) Гистограмма/Цилиндрическая и выберите нужный вид диаграммы.

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

8. С помощью мышки растяните границы диаграммы до нужных размеров и поместите ее в нужное место.

9. Сохраните документ на диске С: в папке Учебные документы в файле с именем Диаграмма.

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

1. В ячейку B1 введите "понедельник". В выделенной ячейке B1 в правом нижнем углу можно заметить маленький черный квадрат - Маркер заполнения. Если поместить курсор на маркер заполнения, курсор принимает форму черного крестика.

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

2. Выделите ячейку B1.

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

4. Удерживая нажатой левую кнопку мыши, переместите указатель на 6 ячеек вправо.

5. Выполните команду Главная/(Ячейки)Формат/Автоподбор ширины столбца. В этом случае ширина столбца будет соответствовать содержимому активной (выделенной) ячейки, или в случае, когда столбец предварительно был выделен, ширина столбца будет подобрана в соответствии с содержимым ячейки, содержащей самую длинную цепочку символов.

6. В ячейки А2, А3, А4 введите "Автобус", "Троллейбус", "Трамвай".

7. В ячейку С5 введите "Стоимость одной поездки", а в ячейку G5 введите "Всего за неделю".

8. Выделите ячейки C5 и G5. Для этого выделите сначала ячейку С5, затем, удерживая нажатой клавишу Ctrl, выделите ячейку G5. На панели инструментов Главная в группе меню (Выравнивание) нажмите кнопку По правому краю, или во вкладке Выравнивание диалогового окна Формат ячеек в выпадающем списке по горизонтали выбрать по правому краю.

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

10. Введите значение стоимости одной поездки. Затем введите число поездок на каждом виде транспорта в определенный день недели.

11. Выделите ячейку, в которую собираетесь поместить итоговый результат и нажмите кнопку Сумма на панели инструментов Главная/(Редактирование). Перетащите курсор по всем ячейкам, подлежащим суммированию. Примерный вид формулы:

"=СУММ(В2:Н4)" (двоеточие между адресами ячеек определяет интервал: все ячейки от В2 до Н4). Установите курсор в строку формул и наберите оставшуюся часть формулы, например, "=СУММ(В2:Н4)*D5". Нажмите Enter.

12. Для обрамления выделите сначала таблицу без последней строки и выполните команду Главная/(Шрифт)Изменение границ выделенных ячеек и установите все рамки. Затем выделите отдельные ячейки в последней строке: выделите первую ячейку, затем, удерживая нажатой кнопку Ctrl левой кнопкой мыши выделите вторую ячейку. Установите все рамки.

13. Сохраните документ на диске С: в папке Учебные документы с именем Работа с функциями.

Третий урок Работа с функциями  Создадим документ следующего вида:

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

2. Выделите ячейки A-F. Выполните команду Главная / (Ячейки) Формат / Ширина столбца. Задайте ширину столбца 13 см и нажмите кнопку ОК.

3. В первой строке выделите ячейки A-F.

4. На панели инструментов Главная/(Выравнивание) выполните команду Объединить и поместить в центре и напечатайте заголовок (размер шрифта 14 пунктов).

5. Зададим строкам 3 и 7 нужный вид. Для этого в строке 3 выделим ячейки A-E. На панели инструментов Главная/(Шрифт) выполните команду Границы и выберете нужные границы. Тоже самое проделайте для строки 7.

6. Напечатайте названия колонок. Для этого на панели инструментов Главная/Шрифт задайте тип шрифта Полужирный, формат абзаца По правому краю и напечатайте названия колонок.

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

8. Введите значения ячеек таблицы.

9. Вставьте функции в ячейки E4-E6. Для этого выделите ячейку E4, выполните команду Формулы/(Библиотека функций)Вставить функцию, в диалоговом окне выберите функцию СУММ нажмите кнопку ОК, в окне Число1 задайте ссылки на ячейки B4:D4 и нажмите кнопку ОК. Аналогично вставьте функции в ячейки E5-E6, задав нужные ссылки на ячейки.

10. Вставьте функции в ячейки B7-E7. Для этого выделите ячейку B7, выполните команду Формулы/(Библиотека функций)Вставить функцию, в диалоговом окне выберите функцию СУММ нажмите кнопку ОК, в окне Число1 задайте ссылки на ячейки B4:В и нажмите кнопку ОК. Аналогично вставьте функции в ячейки C7-E7, задав нужные ссылки на ячейки.

11. Сохраните документ на диске С: в папке Учебные документы в файле с именем Функция.

Сортировка    Создадим документ следующего вида:

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

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

2. Выделите ячейки A-F. Выполните команду Главная/(Ячейки)Формат/ Ширина столбца. Задайте ширину столбца 13 см и нажмите кнопку ОК.

3. В первой строке выделите ячейки A-F.

4. На панели инструментов Главная/(Выравнивание) выполните команду Объединить и поместить в центре и напечатайте заголовок (размер шрифта 14 пунктов).

5. Зададим строкам 3 и 7 нужный вид. Для этого в строке 3 выделим ячейки A-E. На панели инструментов Главная/(Шрифт) выполните команду Границы и выберете нужные границы. Тоже самое проделайте для строки 7.

6. Напечатайте названия колонок. Для этого на панели инструментов Главная/(Шрифт) задайте тип шрифта Полужирный, формат абзаца По правому краю и напечатайте названия колонок.

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

8. Введите значения ячеек таблицы.

9. Вставьте функции в ячейки E4-E6. Для этого выделите ячейку E4, выполните команду Формулы/(Библиотека функций)Вставить функцию, в диалоговом окне выберите функцию СУММ нажмите кнопку ОК, в окне Число1 задайте ссылки на ячейки B4:D4 и нажмите кнопку ОК. Аналогично просуммируйте строки 5-8.

10. Задайте значениям колонки Всего тип шрифта Полужирный.

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

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

Получится документ следующего вида:

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

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

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

Для того, чтобы создать документ на основе уже существующего шаблона достаточно выполнить команду Кнопка Office/Создать и выбрать один из шаблонов.

Создание шаблона товарного счета Создадим документ следующего вида:

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

1. Создайте новую книгу. Для этого выполните команду Кнопка Office/Создать.

2. Используя команду Разметка страницы/Параметры страницы установите одинаковые со всех сторон поля размером 2 см.

3. Введите в ячейку А1: Грузоотправитель и адрес подчеркнутым шрифтом.

4. Введите в ячейку А3: Грузополучатель и адрес подчеркнутым шрифтом.

5. Введите в ячейку А5: К реестру №… 6. Введите в ячейку А7: Счет № от… полужирным шрифтом.

7. Введите в ячейку А9-А11: Поставщик, Адрес, Расчетный счет №.

8. Введите в ячейку А13: Дополнения.

9. Установите внутренние и внешние границы для таблицы с координатами: А15;Е20.

10. Заполните имена столбцов, а также введите ИТОГО в ячейку D20 полужирным 11. Установите ширину столбцов A-E: 23,20; 12,70; 9,50; 13,70; 20,00 соответственно.

12. Введите в ячейки А22 и А24 Руководитель предприятия и Главный бухгалтер соответственно полужирным шрифтом.

13. Для ячеек А1, А3, А11, А13 проделайте следующие операции:

а) Объедините строку в которой содержится данная ячейка со следующей строкой до столбца F. Для этого выделите нужные ячейки, затем выполните команду Главная/(Выравнивание)Объединить ячейки.

b) Затем в этой же вкладке Главная/(Выравнивание)Формат ячейки установите галочку Переносить по словам, затем установите Выравнивание по горизонтали: по левому краю, по вертикали: по верхнему краю.

14. Объедините ячейки А7-Е7 и установите верхние и нижние границы.

15. Объедините ячейки А5-Е5, А9-Е9, А10-Е10 и установите выравнивание по левому 16. Объедините ячейки А22-Е22, А2-Е24 и установите горизонтальное выравнивание в ячейках по левому краю (отступ) с отступом 5.

17. Введите в ячейку Е16: =С16*D16.

18. Скопируйте содержимое ячейки Е16 в ячейки Е17-Е19.

19. Введите в ячейку Е20: =СУММ(Е16:Е19).

20. Сохраните книгу как шаблон. Для этого выполните команду Файл/Сохранить как.

Затем установите Тип Файла: Шаблон, а Имя файла: Товарный счет. Нажмите ОК.

Шаблон товарного счета готов. Чтобы убедиться, что шаблон был создан правильно выполните команду Кнопка Office/Создать и выберите шаблон Товарного счета. Теперь на основе этого шаблона можно создавать товарные счета, заполняя лишь некоторые поля.

Четвертый урок Фильтрация  Excel оснащен весьма полезной при работе с большими таблицами: фильтрацией, т.е.

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

Создадим таблицу следующего вида:

1. В каждую ячейку введем данные.

2. В ячейку С10 введем формулу =сумм (С2:С9), таким же образом введем формулы 3. Левой кнопкой мыши выделим все ячейки, с помощью правой кнопки мыши выберем Формат ячеек.

4. Во вкладке Граница выбираем внешние и внутренние.

5. Во вкладке Заливка выберем нужный цвет.

6. Во вкладке Шрифт выберем начертание – полужирный, размер 10.

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

8. Выделяем мышкой первые два столбца таблицы, выбираем в меню Данные/(Сортировка и фильтр)Фильтр.

9. Тут же в каждой ячейке первой строки появится по кнопочке со стрелкой, обозначающей наличие какого-то списка.

10. Щелкнем по стрелочке в столбце Город. Появится список всех введенных нами городов. Выберем строку “Москва”, будут показаны только результаты работы московских агентов.

11. Добавим примечания в 10 строку, в ячейках C10:H10.

12. Левой кнопкой мыши выделим ячейку С10, с помощью правой кнопки мыши выберем Вставить примечание и введем примечания как указано ниже:

Работа с диаграммами  Создадим диаграмму для этой таблицы.

1. Выделим ячейки A1:H10.

2. Выберем команду Вставка/(Диаграмма)Гистограмма 3. Во всплывшем окошке выбираем Гистограмма с группировкой.

4. Во вкладке Макет/Подписи выберем Название диаграммы. Во всплывающем окошке выберем над диаграммой.

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

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

7. Например, выберем только Московских клиентов, получим диаграмму следующего Сохранение документа:

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

2. В окне сохранение документа введем имя файла Книга1.

3. Тип файла Книга Exel(*.xlsx) 4. И выберем папку, куда хотим поместить этот документ.

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

1. Выделим ячейки от A1:E12.

2.Правой кнопкой мыши выбираем Формат ячеек.

3.Во вкладке Граница выбираем внешние и внутренние, тип линии и цвет.

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

5.Во вкладке Заливка - выбираем нужный цвет, таким же образом выделяем те ячейки которые должны быть одного цвета.

6. В ячейки B1 и A2:A11 можно вводить любые числа, а в ячейках B2:B11 вводим ответы.

Это программа для самопроверки.

7.В ячейку C1 введем слово ”проверка”.

8.В ячейки C2 введем формулу =если(B2=B1*A2;”молодец”;”подумай”), таким же образом в ячейки C3:C11. Это формула проверяет правильность введенного ответа.

9. В ячейку D1 введем слово “ошибка”.

10. В ячейку D2 введем формулу =если(C2="подумай";1;0). Также для ячеек D3:D нужно написать подобную формулу. Если в проверке было слово “подумай”, то в ошибке будет стоять единица. Это удобно чтобы подсчитать общее количество ошибок.

11. В ячейку D12 введем формулу, которое вычисляет общее количество ошибок сумм(D2:D11).

12. В ячейку E11 введем слово ”оценка”.

13. В ячейку E12 введем формулу которая будет оценивать ваше знание =еcли(D12>3;”плохо”;если(D12=0;”отлично”;”хорошо”)). Это формула проверяет :

если количество ошибок больше трех, то оценка “плохо”, если ошибок нет то “отлично”, в других случаях “хорошо”.

Рассмотрим пример, в котором есть ошибки:

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

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

Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке.

Создадим таблицу следующего вида, и сохраним его под именем Книга23:

1. Выделим ячейки А1:G16.

2. Правой кнопкой мыши выбираем Формат ячеек.

3. Во вкладке граница выбираем Граница внешние и внутренние.

4. Выделяем 11-ую строчку правой кнопкой мыши, выбираем объединить ячейки.

5. В ячейки B10:G10 вводим формулы =сумм(B2:B9), для каждого столбца своя 6. В ячейку B12 вводим формулу, которая считает общее количество проданных деталей за 6 дней только в Москве =СУММ(B2:G2)+СУММ(B4:G4)+СУММ(B6:G6)+СУММ(B8:G8).

7. В ячейку B13 введем формулу =(СУММ(B3:G3)).

8. В ячейку B14 =(СУММ(B5:G5)).

9. В ячейку B15 =(СУММ(B7:G7)).

10. В ячейку B16 =(СУММ(B9:G9)) 11. В ячейках C12:G16 нужно убрать внутренние границы. Выделяем эти ячейки, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница убираем 12. Все оставшиеся ячейки заполняем так, как указано в примере.

13. Создадим таблицу следующего вида, и сохраним его под именем Книга5:

1. Выделяем ячейки A1:B6, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница выбираем внешние и внутренние.

2. В ячейку B2 введем формулу, которая ссылаясь на другой документ, присваивает то значение, которое мы хотим увидеть в этом документе.

3. В ячейку B2 введем формулу =[книга23.xlsx]лист1!b12.

4. В ячейку B введем формулу =[книга23.xlsx]лист1!b13.

5. В ячейку B4 введем формулу =[книга23.xlsx]лист1!b14.

6. В ячейку B5 введем формулу =[книга23.xlsx]лист1!b15.

7. В ячейку B6 введем формулу =[книга23.xlsx]лист1!b16.

Консолидация данных.  Консолидация данных это сбор и объединение данных из разных исходных областей.

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

Создайте такую таблицу:

Чтобы создать такую таблицу, необходимо:

1. Создать новую Книгу.

2. Ввести данные в ячейки 3. Сохранить Книгу под именем таблица.xlsx.

Теперь сделайте другую таблицу:

Для этого:

1. Введите имена строк и столбцов таблицы.

2. Выделите ячейки A1:E3 и выполните команду Главная/(ячейки)Формат/Формат ячеек.

3. Во вкладке Границы сделайте внутренние и внешние рамки и нажмите ОК.

4. Выделите шапку таблицы Выделите ячейки A1:E3 и выполните команду Главная/(ячейки)Формат/Формат ячеек.

5. Во вкладке Заливка измените цвет ячеек таблицы на черный.

6. Во вкладке Шрифт измените цвет текста на белый.

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

Чтобы вставить ссылки в последнюю таблицу сделайте следующее:

1. Выделите ячейку B2 второй таблицы.

2. В этой ячейке поставьте знак "=".

3. Перейдите в файл таблица.xlsx, где находятся исходные данные.

4. Найдите в первой таблице строчку, в которой значения ячейки в столбцах Спорт, Год соответствует значению Гольф, 93 соответственно, и выделите её.

5. Аналогично проделайте действия с ячейками C2 – E2, B3 – E3.

6. Нажмите Enter. В ячейку вставится ссылка на ячейку исходной таблицы.

После этих действий должна получиться такая таблица:

Работа с диаграммами  Теперь сделайте диаграмму по последней таблице. Она должна выглядеть так:

Для этого:

1. Выделите таблицу 2. Выполните команду Вставка/(Диаграммы)Гистограмма, далее Все типы диаграмм. Выберите тип график. Нажмите OK.

3. Выделите Область построения. Выполните команду Макет/ (Текущий фрагмент) Формат выделенного фрагмента. Выберите цвет заливки и укажите пункт Сплошная заливка и нажмите Закрыть.

4. Выделите горизонтальную ось категорий. Аналогично предыдущему выберите Формат выделенного объекта. В параметрах оси выберите положение оси по делениям и нажмите Закрыть.

5. Выделите Основные линии сетки горизонтальной оси, далее Формат выделенного объекта. Выберите сплошная линия, цвет линии – белый. Во вкладке Тип линии: Тип штриха выберите штрих. Нажмите Закрыть.

Аналогично проделайте операции с Основными линиями сетки вертикальной 6. Выделите ряд гольф. Зайдите в Формат объекта, вкладка цвет линии. Выберите сплошная линия, цвет – белый. Задайте черный цвет для ряда теннис.

7. Сделайте цвет для легенды заливку более темный оттенок 25%.

8. Отредактируйте размер диаграммы.

Шестой урок Создание сводной таблицы.  Сделайте такую таблицу (если у Вас сохранилась заготовка с прошлого урока, можете использоватьее):

Для этого:

1. Введите данные в ячейки.

2. Выделите получившуюся таблицу и выберите Главная/(Ячейки)/Формат/Формат ячеек. В этом окне диалога во вкладке Граница выберите внешние и внутренние границы. Нажмите OK.

3. Выделите шапку таблицы и в том же диалоговом окне во вкладке Заливка выберите черный цвет, а во вкладке Шрифт цвет шрифта – белый и нажмите OK.

Создайте для нее сводную таблицу и сводную диаграмму.

Сводная таблица — это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, а также отобразить детальные данные области.

Таблица должна выглядеть вот так:

Диаграмма должна выглядеть вот так:

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

1. Выделите таблицу.

2. Выполните команду Вставка/(Таблицы)/Сводная таблица/Сводная диаграмма и 3. Справа выберите поля спорт и продажи для добавления в отчет, затем щелкните правой кнопкой мыши на поле год и выберите пункт Добавить к полям легенды 4. Отформатируйте диаграмму.

 Функция «ЕСЛИ»  Очень часто в таблицах удобно использовать функции. Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий.

Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение если условие истинно, и другое — если оно ложно.

Нужно сделать таблицу следующего вида:

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

Чтобы создать такую таблицу создайте сначала следующую:

Для этого:

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

3. Во вкладке Выравнивание поставьте выравнивание По верхнему краю и включите флажок Переносить по словам.

В столбец Состояние бюджета нужно вставить функцию ЕСЛИ, которая показывает, были ли превышены расходы.

Для этого:

1. Поставьте курсор во вторую ячейку столбца.

2. Выполните команду Формулы/(Библиотека функций) Вставить функцию.

3. В поле Лог_выражение введите С2=120 (товара «Марс» необходимо купить не менее 120), В2=2*В4 ( товара «Мечта» необходимо купить в два раза больше, чем товара «Люкс»).

Выделите ячейку D5. Выполните команду Данные/(Анализ)Поиск В поле Установить целевую ячейку укажите D5. Для указания ячеек можно поставить курсор в поле, а затем на листе выделить нужные ячейки.

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

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

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

В данном примере установите флажок в положении значению и введите число В поле Изменяя ячейки следует указать, какие ячейки влияют на результат вычислений в целевой ячейке. В нашем примере это ячейки В2:В4.

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

Добавление ограничений  1. Нужно нажать кнопку Добавить. Откроется диалоговое окно Добавление ограничения.

2. В поле Ссылка на ячейку указать ячейку В3.

3. Затем в раскрывающемся списке выбрать знак ограничения >=.

4. В поле Ограничение введите 120.

5. Для перехода к установке другого ограничения в окне Добавление ограничения следует нажать кнопку Добавить.

Аналогично добавьте следующее ограничение В2=2*В4.

Кроме того, значения в ячейках В2:В4 должны быть целыми положительными числами: В2:В4=целое и В2:В4>=0.

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

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

6. В окне Поиск решения нажмите кнопку Выполнить.

7. В окне Результаты поиска решения будет указано на наличие решения.

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

8. Для сохранения найденного решения на листе следует установить переключатель Сохранить найденное решение и нажать кнопку ОК.

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

1. Выполните команду Данные/(Анализ)Поиск решения. Откроется диалоговое окно Поиск решения.

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

3. Измените ограничения и нажмите кнопку ОК. Снова откроется диалоговое окно Поиск решения.

4. Нажмите кнопку Выполнить.

Удаление ограничений.  Чтобы удалить ограничение:

1. Выполните команду Данные/(Анализ)Поиск решения. Откроется диалоговое окно Поиск решения.

2. В списке имеющихся ограничений выделите нужное ограничение и нажмите кнопку Удалить.

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

Оформление 1го листа  На первом листе создадим следующий документ:

Для этого:

Введём все значения в таблицу.

Выделим визуальную таблицу. Наведём на неё курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Граница выберем внешние и внутренние. Закроем окно.

Выделим ячейки А1, В1, С1. Наведём на одну из них курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Заливка выберем цвет. Цвет по вашему желанию. Он не имеет значения. Проделаем то же и со столбцами.

Выделим таблицу и выполним команду Главная/(ячейки)Формат/Автоподбор ширины столбца.

В нижнем левом углу вы видите Лист1, Лист2, Лист3. Переименуем Лист1 в Справочник. Для этого наведём курсор на Лист1 и дважды нажмём левую кнопку Оформление 2го листа  Итак, мы создали Справочник. Теперь наведём курсор на Лист2 и нажмём левую кнопку мыши. Мы перешли на второй лист. Оформим его так:

Для этого:

1. Введём все значения в таблицу.

2. Выделим визуальную таблицу. Наведём на неё курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Граница выберем внешние и внутренние. Закроем окно.

3. Выделим строки А1, В1. Наведём на одну из них курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Заливка выберем цвет. Цвет по вашему желанию. Он не имеет значения. Проделаем то же и со столбцами.

4. Выделим таблицу и выполним команду Главная/(ячейки)Формат/Автоподбор ширины столбца.

5. Переименуем Лист2 в Тарификация.

Оформление 3го листа  А теперь приступим к выполнению главной части данного проекта. Сначала создадим документ такого вида:

Для этого:

1. Введём все значения в таблицу.

2. Выделим визуальную таблицу. Наведём на неё курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Граница выберем внешние и внутренние. Закроем окно.

3. Выделим строки А1:D1. Наведём на одну из них курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Заливка выберем цвет. Цвет по вашему желанию. Он не имеет значения. Проделаем то же и со столбцами.

4. Выделим таблицу и выполним команду Главная/(ячейки)Формат/Автоподбор ширины столбца.

5. Переименуем Лист3 в Разговоры за январь.

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

7. Выделим столбцы А, В, С, но не выделяя в них первую строку. Наведём курсор на выделение и щёлкнем правой кнопкой мыши. Выберем копировать.

8. Теперь выделим ячейку А33 и щелкнем правой кнопкой мыши. Выберем вставить.

Переименуем ячейку А33:

Проделаем так несколько раз, пока не выпишем всех, кто есть в справочнике:

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

Для этого:

1. Выделим всю таблицу и выполним команду Вставка/(таблицы)Сводная таблица.

укажем диапазон таблицы, если его нет (Он должен быть указан автоматически, если перед созданием сводной таблицы мы выделили её). Укажем поместить таблицу на новый лист, и нажмём ОК.

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

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

4. Выделим всю таблицу и выполним команду Вставка/(таблицы) Сводная таблица. укажем диапазон таблицы, если его нет (Он должен быть указан автоматически, если перед созданием сводной таблицы мы выделили её). Укажем поместить таблицу на существующий лист, и укажем диапазон: Лист4!$A$17 и 5. Выберем абонентов справа в списке полей сводной таблицы нажав на правую кнопку мыши и выберем добавить в название строк. Аналогично добавим Дату в название столбцов, а Количество исходящих звонков на домашний в 6. Готово. Теперь выделим ячейки А31 и В31. Придадим им цвет и границы. В А напишем Количество потраченных $ в месяц. Теперь выделим ячейку В31. В строке формул введём следующую формулу: =AF14*Тарификация!A2 + AF28*Тарификация!B2.

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

Важно! Вводить новых абонентов и информацию про них через форму, путем : ДАННЫЕ – ФОРМА. Таким образом, если каждый день вписывать данные с телефона можно проверить, правильно ли вас обслуживает оператор сотовой связи.