Поможем в ✍️ написании учебной работы

Имя

Поможем с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой

Выберите тип работыЧасть дипломаДипломная работаКурсовая работаКонтрольная работаРешение задачРефератНаучно - исследовательская работаОтчет по практикеОтветы на билетыТест/экзамен onlineМонографияЭссеДокладКомпьютерный набор текстаКомпьютерный чертежРецензияПереводРепетиторБизнес-планКонспектыПроверка качестваЭкзамен на сайтеАспирантский рефератМагистерская работаНаучная статьяНаучный трудТехническая редакция текстаЧертеж от рукиДиаграммы, таблицыПрезентация к защитеТезисный планРечь к дипломуДоработка заказа клиентаОтзыв на дипломПубликация статьи в ВАКПубликация статьи в ScopusДипломная работа MBAПовышение оригинальностиКопирайтингДругое

Нажимая кнопку "Продолжить", я принимаю политику конфиденциальности

При помощи форматирования таблице придается желаемый вид. При этом возможны следующие варианты работы: использование команды Формат/Автоформат позволяет оформить всю таблицу целиком одним из имеющихся стандартных форматов; использование других команд меню Формат позволяет вручную от­форматировать таблицу, выполнив все необходимые для этого опера­ции. При этом очень полезно вначале создать необходимые для фор­матирования стили (см. ниже).

Изменение ширины столбцов

1 способ:

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

2 способ:

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

Формат/Столбец/Стандартная ширина для установки всем вы­деленным столбцам стандартной ширины;

Формат/Столбец/Автоподбор ширины для автоматической настройки ширины всех выделенных столбцов в соответствии с мак­симальной длиной записи в каждом из них;

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

Показать скрытый столбец

Выделить заголовки столбцов слева и справа от скрытого и вы­полнить Формат/Столбец/Отобразить.

Изменение высоты строк

1 способ:

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

2 способ:

выделить одну или несколько подряд идущих строк; Формат / Строка / Автоподбор высоты  - высоты для авто­матической настройки высоты всех выделенных строк в соответствии с максимальной высотой записи в каждой из них;

Формат/Строка/Высота для установки всем выделенным строкам указанной высоты.

 

Изменение шрифта

Формат/Ячейки/Шрифт или использование панели инструмен­тов Форматирование

 

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

Все ячейки по умолчанию имеют формат Общий, в котором: текст выравнивается по левому краю; числа - по правому краю; ошибочные значения - по центру.

Выравнивать данные удобно с помощью кнопок на панели инст­рументов Форматирование или при помощи команды Фор­мат/Ячейки/Выравнивание. Эта команда позволяет:

установить горизонтальное и вертикальное выравнивание;

установить горизонтальную или вертикальную ориентацию данных;

в случае необходимости установить режим переноса слов.

 

Центрирование заголовков таблиц

Используется кнопка Объединить и поместить в центре.

Необходимо:

выделить ячейку, содержащую заголовок;

расширить выделение на все примыкающие справа пустые ячейки;

щелкнуть по кнопке.

Аналогичные действия выполняет команда

Формат/Ячейки/Выравнивание/По горизонтали/ По центру выделения.

 

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

Формат/Ячейки/Граница, установить толщину и цвет линии,

выбрать вариант обрамления.

Можно воспользоваться кнопкой Границы.

Использование заполнения

Формат/Ячейки/Вид, выбрать вариант заполнения ячеек, рису­нок и цвет фона.

 

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

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

 

 

2. Методические указания по выполнению лабораторных работ

2.1. Лабораторная работа 1

Создание, редактирование, форматирование, сохранение, печать таблиц

 

Цель работы:

× освоить основные элементы экрана Excel, научиться менять вид экрана, используя меню Вид;

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

× научиться заполнять и редактировать таблицы, содержащи_е столбцы с данными различных типов: числа, текст, значения, вычис­ляемые по формулам. Научиться вводить и копировать формулы, копировать повторяющиеся значения, вводить последовательности чи­сел.

× научиться форматировать таблицы, используя команды Формат ячеек и Автоформатирование;

× научиться сохранять рабочие книги на диске и распечаты­вать рабочие листы на принтере.

 

Пример задания

Создадим таблицу, содержащую сведения о товарах, имеющихся на овощном складе:

• номер по порядку;

• наименование товара;

• единица измерения;

• цена единицы товара;

• количество;

• стоимость (должна вычисляться как произведение цены на количество).

Порядок выполнения работы

1. Используя меню Вид, поменять вид экрана: убрать, а затем снова восстановить панели инструментов Стандартная и Формати­рование. Расположить рабочую книгу во весь экран, а затем снова вернуть стандартное расположение.

2. После запуска Excel на экране имеется открытая чистая ра­бочая книга, которая пока имеет название Книга1, состоящая из рабо­чих листов. Убедитесь, что в нижней строке экрана имеются ярлычки рабочих листов, при помощи которых можно активизировать мышью любой рабочий лист. Если ярлычков на экране нет, выполните команду Сервис/Параметры и пометьте параметр Ярлычки листов. Даже если ярлычки видны на экране, выполнить команду Сервис/Параметры очень полезно, так как с ее помощью можно менять вид экрана.

3. Обычно заполнение книги начинают с первого листа, кото­рый называется Лист! Поменяйте название листа, для чего нужно щелкнуть два раза мышкой по ярлычку с названием Лист1 в нижней строке экрана и в появившемся окне ввести новое название, например «Склад». На этом листе будем создавать таблицу, изображенную на рис. 2.1.

Рис.2.1. Рабочий лист «Склад»

 

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

Вводим заголовок таблицы в ячейку А1 рабочего листа. Перед вводом установим размер шрифта 14.

Затем вводятся заголовки столбцов во второй строке. Подбира­ем нужную ширину столбцов путем перетаскивания границ столбцов в самой верхней строке мышью вправо или влево. Указатель мыши дол­жен стоять строго на разделительной линии между столбцами, при этом он имеет вид двунаправленной стрелки. Аналогично можно ме­нять мышью и высоту строк. Для этого маркер от мышки должен стоять в крайнем левом столбце и граница перетаскивается вверх или вниз. Для того, чтобы название столбца «Единицы измерения» расположить в две строки, выполняем Формат / Ячейки / Выравнивание / Перено­сить по словам. Для всех заголовков столбцов выбираем выравнива­ние по вертикали - по верхнему краю, по горизонтали - по центру. Теперь, когда определилась ширина таблицы, можно отформа­тировать заголовок так, чтобы он располагался по центру таблицы. Для этого надо выделить ячейки первой строки, находящиеся над таб­лицей (в нашем примере это A1:F1). После этого выполнить команду Формат / Ячейки / Выравнивание / По горизонтали / По центру выделения. Если в процессе дальнейшей работы все-таки придется еще менять ширину столбцов, центрирование заголовка будет выпол­няться автоматически.

Вводим данные первой строки таблицы (это уже третья строка рабочего листа). Для перехода от одной ячейки к другой нажимаем Enter. При вводе цены можно сразу установить денежный формат, используя соответствующую кнопку на панели Форматирование или команду Формат / Ячейки / Число / Денежный, а можно будет это сделать и после того, как будут введены значения всего столбца. В любом случае не вводите сами букву «р» после значения цены, она должна быть добавлена автоматически как признак денежного форма­та. В ячейке F3, где должна быть вычислена стоимость, вводим фор­мулу (произведение цены и количества). Признак формулы - самый первый символ - знак равенства. Таким образом, в ячейку F3 нужно занести «=D3*E3 ». Для ввода ссылок на ячейки можно поставить ука­затель мыши на нужную ячейку и щелкнуть левой кнопкой. Это позво­лит ускорить ввод формулы и избежать лишних ошибок. Конечно, знак умножения необходимо ввести с клавиатуры. После нажатия Enter вместо введенной формулы в ячейке появляется подсчитанное значе­ние, а саму формулу можно увидеть в строке формул.

Другой способ введения этой же формулы состоит в использова­нии мастера функций. Для этого нужно воспользоваться кнопкой /х. Появится меню имеющихся стандартных функций. Выбираем функцию ПРОИЗВЕД и заносим в поля ввода ссылки на ячейки D3 и ЕЗ, указав их мышью. Должно получиться ПРОИЗВЕД(РЗ;ЕЗ). Этот способ ввода формул считается более предпочтительным.

Вводим следующие строки таблицы. При этом обращаем внима­ние, что в первом столбце будут располагаться последовательные значения чисел. Для заполнения столбца «Номер по порядку» введите второе значение номера - 2, а затем возможны два способа:

1 способ: выделите столько ячеек в этом столбце, сколько нуж­но, и выполните Правка/Заполнить/Прогрессия. Выделить можно или мышью, удерживая нажатой левую клавишу, или клавишами со стрел­ками, удерживая нажатой клавишу Shift.

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

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

1 способ: сделайте активной первую ячейку, в которой написано «тонн» и скопируйте ее содержимое в буфер либо с помощью команды Правка / Копировать, либо с помощью кнопки Копировать на Стан­дартной панели инструментов. Затем выделите все незаполненные ячейки до конца столбца и выполните команду Правка / Вставить, либо воспользуйтесь кнопкой Вставить из буфера на Стандартной панели инструментов.

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

Столбцы «Название», «Цена» и «Количество» заполняем обыч­ным способом.

формулу для последнего столбца «Сумма» распространяем из первой ячейки на все остальные ячейки этого столбца. Обратите вни­мание, что ссылки на ячейки при копировании изменяются автомати­чески.

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

При помощи команды Формат/Ячейки/Граница или кнопки Гра­ницы рамки на панели Форматирование сделайте обрамление таб­лицы, в столбцах «Цена» и «Сумма» установите денежный формат, если он еще не установлен. Для этого проще всего воспользоваться кнопкой Денежный.

В конце таблицы в столбце «Название» наберите «Итого». Сде­лайте активной ячейку в столбце «Сумма» и выберите на Стандартной панели инструментов кнопку со знаком суммы ( Ʃ ). В ячейке появится формула для вычисления общей суммы. Нажмите Enter или еще раз щелкните мышкой по кнопке с изображением суммы и подсчитанная сумма появится на экране. Можно выделить эту строку контрастным цветом.

Вот наконец-то получен рабочий лист, который изображен на рис. 2.1.

Выделите все содержимое первого листа и скопируйте его на Лист2 (сначала скопируйте выделенную область в буфер либо с помо­щью команды Правка/Копировать, либо с помощью кнопки Копировать в буфер на Стандартной панели инструментов, а затем сделайте активным Лист2 и сделайте вставку из буфера). Выберите пункт Автоформат из меню Формат и примените к таблице один из вариантов формата. Выберите тот, который покажется наиболее приемлемым.

Запишите рабочую книгу в файл в указанную преподавателем папку на диске при помощи команды Файл/Сохранить или кнопки Со­хранить на Стандартной панели инструментов. В появившемся окне установите свой рабочий диск и папку, и только потом вводите имя файла и выбирайте Сохранить.

Если есть возможность, выведите один из листов на принтер, используя команду Файл/Печать или кнопку Печать на Стандартной панели инструментов. Обратите внимание, что на бумаге лист выгля­дит так же, как и на экране.

Самостоятельное задание

На новом листе создать и отформатировать таблицу результатов экзаменационной сессии, содержащую следующие столбцы:

• номер студента по журналу;

• ФИО студента;

• оценки по четырем экзаменам, каждая в отдельном столбце, заголовком которого является название предмета;

• сумма баллов по четырем экзаменам.

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

 

2.2.  Лабораторная работа 2

 

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

 

Цель работы:

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

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

• научиться отображать данные таблиц в виде диаграмм.

Пример задания

На основе таблицы «Список товаров на овощном складе», соз­дать таблицу-справочник «Цены на фрукты»:

• номер по порядку;

• наименование товара;

• цена единицы товара.

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

Вид рабочего листа изображен на рис.2.2.

 

Рис.2.2.Рабочий лист «Продажа фруктов»

 

Сделаем активным лист рабочей книги, в котором содержится таблица «Список товаров на овощном складе» и скопируем эту табли­цу на чистый рабочий лист. Удалив лишние столбцы, получим спра­вочник цен. Затем оформим шапку следующей таблицы и скопируем содержимое столбцов «№», «Название» и «Единицы измерения». Все столбцы «К-во» заполняем произвольными данными.

Теперь осталось ввести формулы в столбцы «Сумма». Во всех трех таких столбцах это практически одна и та же формула, поэтому введем ее в ячейку Е16, а затем скопируем во все остальные ячейки. С помощью мастера функций заносим ПРОИЗВЕД(D16;D4). В этой фор­муле используются относительные ссылки на ячейки, которые при ко­пировании в другие ячейки будут автоматически изменяться. Но нам требуется, чтобы буква D ячеек, в которых содержится цена, все время оставалась неизменной, поэтому здесь разумнее использовать абсо­лютную ссылку, которая не меняется при копировании. Добавим знак « $ » перед буквой D. Получим ПРОИЗВЕД(D16;$D4). При копировании этой формулы автоматически будут меняться все ссылки, кроме буквы D.

Распространяем формулу на весь столбец Е, а затем копируем и распространяем на столбцы G и I. Затем вводим ИТОГО и с помощью кнопки Автосуммирование заносим формулу для подсчета суммарной стоимости в ячейку Е24, а затем копируем ее в ячейки G24 и I24. В формуле были использованы относительные ссылки, поэтому при ко­пировании они автоматически изменятся.

Для получения таблицы с нарастающими итогами копируем таб­лицу продаж и исправляем формулы в столбцах F,G,H и I. В ячейку F30 заносим CУMM(D30;F16), а в затем распространяем эту формулу вправо до конца строки 30. Распространяем формулы по столбцам При этом суммарные значения автоматически изменяются, так как формулы для их подсчета скопировались корректно, с изменением ссылок на диапазон.

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

Изменим заголовок активного рабочего листа. Например, дадим ему заголовок «Цены». Еще один чистый рабочий лист озаглавим «Продажи».

Копируем на рабочий лист «Продажи» только две таблицы про­даж. При этом в ячейках первой таблицы, содержащих формулы, по­является сообщение о недопустимой ссылке. Исправим в любой из этих ячеек $D4 на Цены!$04.  Это означает ссылку на ячейку $D4, на­ходящуюся на рабочем листе «Цены». Распространяем исправленную формулу на все остальные ячейки. Больше никаких исправлений не требуется, т.к. все остальные формулы содержат ссылки на ячейки, находящиеся на активном рабочем листе.

Осталось только отобразить содержимое таблицы продаж в виде диаграммы.

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

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

Продажа фруктов в первом квартале

Рис.2.3. Таблица для построения диаграммы

 

Построение диаграммы по такой таблице - дело несложное. Для этого сначала выделим таблицу. Затем выполняем команду встав­ка/Диаграмма или нажимаем кнопку Мастер диаграмм на стандарт­ной панели инструментов. Указатель мыши примет форму тонкого кре­ста с диаграммой под ним. Перетаскиваем указатель мыши по ячей­кам, определяя место и размер будущей диаграммы, а затем отпуска­ем кнопку мыши.

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

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

В результате получили диаграмму, изображенную на рис.2.4.

Рис.2.4. Диаграмма продажи фруктов

 

Самостоятельное задание

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

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

 

 

2.3. Лабораторная работа 3

 

Ввод сложных формул при помощи мастера функций, изу­чение различных функций, имеющихся в Excel

Цель работы:

• закрепить навыки ввода формул при помощи мастера функ­ций путем ввода более сложных формул;

• изучить некоторые часто используемые математические и логические функции.

 

Пример задания

К имеющейся таблице «Ведомость успеваемости студентов» до­бавить еще один столбец «Характеристика», содержащий текст «от­личник», «без троек», «троечник» или «двоечник» в зависимости от оценок данного студента.

Создать еще одну таблицу «Анализ успеваемости студентов», содержащую данные о количестве пятерок, четверок, троек и двоек по каждому предмету.

Порядок выполнения работы

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

Рис.2.5. Рабочий лист «Успеваемость студентов»

 

Скопируем на чистый рабочий лист таблицу «Ведомость успе­ваемости студентов», удалим из нее столбец Сумма баллов и добавим столбец Характеристика. В ячейку G4 необходимо внести формулу для заполнения ее различным текстом в зависимости от значений, нахо­дящихся в ячейках C4:F4. В подобных случаях пользуются логической функцией ЕСЛИ, аргументами которой являются <условие> и два зна­чения, первое из которых заносится в ячейку при выполнении условия, а второе при невыполнении. Поскольку в нашем случае имеется боль­ше двух вариантов заполнения ячейки, воспользуемся вложенной функцией ЕСЛИ. Для задания условия подойдет функция МИН, вычис­ляющая минимальное значение в заданном диапазоне. При помощи Мастера функций заносим формулу:

=ЕСЛИ(МИН(C4:F4)=2; "двоечник";

ЕСЛИ(МИН(C4:F4)=3; "троечник";

ЕСЛИ(МИН( C 4: F 4)=4;"без троек»; отличник»)))

Такая формула заносится за несколько шагов, после каждого из которых нажимаем кнопку Далее. На последнем шаге нажимается кнопка Готово.

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

Сделайте шапку второй таблицы и введите тексты первого столбца. В ячейку С13 при помощи Мастера функций заносим формулу =СЧЁТЕСЛИ(С$4:С$8;5). Это обозначает подсчет количества ячеек в заданном диапазоне, которые содержат значение 5. Распространяем формулу на первый столбец (при этом ссылки в ней не изменяются) и вручную исправляем значение 5 на 4, 3 и 2. Затем распространяем формулы на все столбцы и вторая таблица также готова.

Самостоятельное задание

1. К таблице «Ведомость успеваемости студентов» добавить еще один столбец «Примечание», содержащий текст «повысить сти­пендию», если это отличник или имеется только одна четверка при остальных пятерках, или текст «отчислить», если имеется три двойки. Для всех остальных студентов в этом столбце ничего не писать.

В конце этой таблицы вывести некоторые итоги:

• максимальная и минимальная сумма баллов;

• количество отличников и количество двоечников.

• Решить следующую задачу на чистом рабочем листе. Имеется коллектив сотрудников, выполнивших работу по теме. Известна сумма премии и КТУ (коэффициент трудового участника) каждого сотрудника. Найти сумму премии для каждого сотрудника.

• Таблица с исходными данными имеет следующий вид:

 

А2:А4 - фамилии сотрудников; В2:В4 - индивидуальные КТУ; С7 - общий размер премии.

 

2.4. Лабораторная работа 4

 

Сложные действия с таблицами

 

Цель работы:

• закрепить все навыки повседневной работы с таблицами;

• освоить работу с датами.

 

Пример задания

Создать таблицы «Расписание занятий» на первую и вторую не­делю и на их основе сформировать таблицу занятий за весь семестр и подсчитать количество часов по каждому предмету за семестр.

Порядок выполнения работы

Вид рабочего листа изображен на рис. 2.6.

 

Рис.2.6. Рабочий лист «Расписание на весь семестр»

 

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

Затем готовим шапку таблицы с расписанием занятий за весь семестр. В первом столбце должны быть даты всех дней первого се­местра, который обычно длится 18 недель. Excel распознает даты и время суток, набранных общеизвестными способами, хранит их особым способом и корректно выполняет все действия над ними. Для дат основным форматом является «день.месяц.год», для времени «часы.минуты». Набираем дату 1.09.03 (или другую дату начала семестра) и затем распространяем ее вниз обычным способом, ухватив мышью правый нижний угол ячейки. При этом будут правильно сформированы даты всех дней семестра.

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

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

В ячейку С15 заносим формулу =$В$3. Распространяем ее на все ячейки первой недели и заменяем в каждой ячейке либо букву, либо цифру. Такую же работу проделываем и для второй недели. Ко­нечно, это утомительное занятие, зато потом можно будет копировать первые две недели до конца таблицы.

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

Для   подсчета   воспользуемся формулой:

=СЧЁТЕСЛИ($C$15:$F$140;B144)*2

Рис.2.7. Таблица «Количество часов за семестр»

 

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

 

Самостоятельное задание

1. Скопировать таблицу «Коли­чество часов за семестр» на другой рабочий лист таким образом, чтобы были скопированы только значения, но не формулы (при вставке использовать Правка/Специальная вставка/Значения)

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

Подсчитать количество часов учебных занятий за неделю от­дельно для первой и второй недели. Для этого рекомендуется вос­пользоваться функцией СЧИТАТЬПУСТОТЫ (диапазон), которая под­считывает число пустых ячеек в диапазоне.

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

 

2.5. Лабораторная работа 5

 

Организация баз данных на основе таблиц Excel

 

Цель работы:

• освоить функции поиска в справочнике;

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

 

Пример задания.

Сформировать таблицу-справочник «Цены на ткани»:

• код;

• наименование товара;

• сорт;

• цена за метр.

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

• за определенный период времени;

• только 1 сорта в течение месяца;

• выпуск определенного вида продукции за месяц; Подвести итоги выпуска продукции

• в сумме за каждый день;

• по каждой ткани и каждому сорту за месяц; Отсортировать таблицу продаж по наименованию ткани.

Порядок выполнения работы. Заполнение таблиц.

Вид рабочего листа с заполненным справочником и частью таб­лицы продаж изображен на рисунке.2.8.

 

Рис.2 8. Часть рабочего листа «Продажа тканей»

 

Для его получения выполняем следующие действия:

Формируем исходную таблицу-справочник (пока лучше не запол­нять).

Формируем таблицу продаж копированием исходной таблицы и добавлением столбцов А - Дата, F - Кол-во и G -Сумма.

Заполняем справочник.

До начала заполнения таблицы продаж позаботимся о том, что­бы столбцы Наименование, Сорт, Цена и Сумма заполнялись автома­тически. Для этого необходимо, чтобы после ввода кода отыскивалась соответствующая строка в справочнике цен и данные из нее копирова­лись в таблицу продаж. Для поиска в таблице в Excel имеется несколь­ко различных функций: ВПР(), ГПР(), ПОИСКПОЗ(). Для решения дан­ной задачи воспользуемся функцией ВПР(), которая имеет следующий синтаксис:

ВПР(искомое значение; массив ячеек; номер столбца; вид поис­ка)

Первая функция выполняет поиск заданного значения в первом столбце заданного массива ячеек и возвращает значение ячейки за_: данного столбца найденной строки. Массив задается в виде диапазона ячеек, обычно это диапазон ячеек справочника (в нашем случае $В$4:$Е$9). При этом вид поиска может принимать значения Истина(1) или Ложь(0). Ноль обозначает, что нужно искать значение, точно соот­ветствующее искомому, а единица - что нужно искать наиболее близ­кое к искомому значение (этот вид поиска правильно работает только в случае, когда столбец, в котором ведется поиск, - отсортирован). В нашей задаче, конечно, вид поиска - ноль (или Ложь).

При поиске возможна ситуация, когда искомое значение вообще не будет обнаружено. В этом случае выводится сообщение об ошибке #Н/Д. Это сообщение появляется ив том случае, если столбец Код не заполнен, а формула уже занесена в другие столбцы. Чтобы избежать этой неприятной ситуации, добавим проверку, заполнен столбец Код или нет, воспользовавшись функцией ЕПУСТО().

В ячейку С14 при помощи мастера функций заносим формулу: =ЕСЛИ(ЕПУСТО($В14);"";ВПР($В14;$В$4:$Е$9;2;0))

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

В ячейку G14 вводим формулу =ПРОИЗВЕД(Е14;Р14).

5. Заполнять таблицу продаж можно обычным способом, но для удобства воспользуемся дополнительной возможностью, предостав­ляемой Excel для ввода данных в базу данных. Выделяем таблицу продаж, а затем выполняем команду Данные/Форма. Заполняем таб­лицу на целый месяц.

 

Фильтрация данных.

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

Выделяем заголовки столбцов таблицы продаж и выполняем ко­манду Данные/Фильтр/Автофильтр. При этом заголовки всех столб­цов превратятся в раскрывающиеся списки. При работе с автофильт­ром имеются следующие возможности:

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

• можно выбрать команду Настройка, чтобы задать например диапазон значений;

• команда Показать все позволяет отменить действие фильт­ра.

 

Задание

Используя Автофильтр, сформируйте и скопируйте на новый лист таблицы, отражающие:

1. продажи за определенный период времени (1 декада);

2. продажи только 1 сорта в течение месяца;

3. продажи сатина за месяц;

4. продажи сатина и шелка за месяц.

Подведение итогов

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

Задание

Подведите итоги:

1. за каждый день (см.рис.2.9.);

2. по наименованию и сорту ткани.

Скопируйте полученные таблицы на другой рабочий лист с на­званием «Итоги» и дайте им там соответствующие заголовки.

Рис.2.9. Таблица итогов продажи тканей

 

Самостоятельное задание

Добавить в справочник еще две строки (например, ситец 1 и 2 сорта). Изменить формулы в таблице продаж и добавить в ее конец несколько строк о продажах ситца. Отсортировать таблицу по дате.

Измените формулу для поиска в справочнике, используя вместо функции ВПР() сочетание функций ИНДЕКС() и ПОИСКПОЗ().

 

2.6. Лабораторная работа 6

 

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

 

Цель работы:

• научиться использовать области с именами;

• научиться создавать сводные таблицы;

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

 

Пример задания

Расчет расхода топлива на автотранспортном предприятии, ис­пользующем 20 автомобилей 6-ти марок.

 

Порядок выполнения работы

Сначала необходимо создать справочник по расходу топлива в зависимости от марки автомобиля и справочник цен на топливо (рис.2.10).

Присвоим имена диапазонам ячеек, содержащим справочные данные: выделим диапазон А4:С9, затем выполняем команду Встав­ка/Имя/Присвоить и вводим имя "Расход". Диапазону E4:F7 присваи­ваем имя "Стоимость"

Рис.2.10. Справочники

 

Формируем таблицу пробега  автомобилей за месяц. Графы "Но­мер автомобиля" и "Пробег за месяц" заполняем произвольными дан­ными, а в графу "Марка автомобиля" заносим любые марки автомоби­лей, которые есть в таблице 2.10.

Рис.2.11. Таблица «Пробег автомобилей за месяц»

 

Марка, расход топлива и сумма определяется по справочникам. В ячейку D15 при помощи мастера функций заносим формулу:

=ЕСЛИ(ЕПУСТО($В15);"";ВПР($В15;Расход;2;0)). В ячейку Е15 заносим формулу:

=ЕСЛИ(ЕПУСТО($В15);"";ВПР($В15;Расход;3;0)*$С15/100).

В ячейку F15 заносим:

=ЕСЛИ(ЕПУСТО($В15);"";ВЛР($О15;Стоимость;2;0)*$Е15).

Далее распространяем формулы по столбцам вниз.

Сформируем сводную ведомость расхода топлива в количест­венном и стоимостном выражении по маркам автомобилей. Выделим диапазон A14:F34. Через меню Данные/Сводная таблица запустим мастер сводных таблиц.

В появившемся окне диалога "Мастер сводных таблиц шаг 1 из 3" выберем источник данных "В списке или базе данных Microsoft Excel", вид создаваемого отчета - "Сводная таблица". Нажав "Далее", вводим область данных. Затем в «окне диалога» ... шаг 3 из 3", нажав кнопку "Макет...", размещаем поля базы данных на планшете, в поле строка размещаем кнопку с названием поля "Марка автомобиля"; в поле данные размещаем кнопки "Расход топлива" и "Сумма". Нажав кнопку "ОК", задаем построение сводной таблицы на новом листе и получим таблицу, изображенную на рис. 2.12.

Рис.2.12. Сводная таблица

 

Установив указатель мыши на ячейку ВЗ, нажав левую кнопку, перемещаем мышь вправо до тех пор, пока курсор мыши в виде таб­лички не сменит ее ориентацию. Отпустив кнопку мыши, получим бо­лее удобный вид сводной таблицы:

Рис.2.13. Окончательный вид Сводной таблицы В завершение установим нужный формат чисел в столбцах.

 

Самостоятельное задание

Построить сводную таблицу расхода и стоимости по маркам, топ­лива (рис.2.14):

Рис.2.14. Сводная таблица по маркам топлива Построить диаграммы по сводным таблицам.

Лабораторная работа 7

Действия с матрицами. Макросы

 

Цель работ ы:

• Изучить особенности работы с матрицами;

• Научиться заполнять таблицы случайными числами;

• Научиться создавать, записывать и выполнять макросы.

Пример задания

Решить систему линейных алгебраических уравнений (СЛАУ) 7-го порядка и проверить решение. Создать макрос для решения сис­темы линейных уравнений.

 

Порядок выполнения работы

Представим СЛАУ в виде: А*Х=В,

где

А - матрица коэффициентов; X - вектор решения; В - вектор свободных членов. Тогда, вектор решения будет равен Х=А-1*В,

где А-1 - матрица, обратная матрице А.

Заполняем матрицу А и вектор свободных членов В случайными числами в диапазоне от -10 до 10. Для этого в ячейку A3 заносим формулу =ОКРУГЛ(СЛЧИС()*20-10;1), затем распространяем ее на области (A3:G9) и (I3:I9). Значения в этих ячейках будут меняться при каждом пересчете, что неудобно. Закрепить значения в ячейках можно, заменив формулы в них значениями. Выделив область (A3.G9), скопи­руем ее в буфер (Правка/Копировать). Затем вставим в эти же ячейки только их значения (Правка/Специальная вставка/Значения). Анало­гично поступим с областью (I3:I9).

Рис.2.15. Рабочий лист «Система ЛАУ»

 

Чтобы получить обратную матрицу коэффициентов, с помощью мастера функций занесем в ячейку А14 формулу =МОБР(А3:G9) и осо­бым образом распространим ее на область (A14:G20). Для этого нужно выделить область (A14:G20), переместить указатель мыши в строку формул (под панелью инструментов) и щелкнуть левой клавишей мы­ши. Затем нужно последовательно, не отпуская, нажать три клавиши Ctrl, Shift и Enter. В выделенной области появится обратная матрица.

Для формирования вектора решения в ячейку I14 помещаем формулу =MУMHOЖ(A14:G20;I3;I9) и распространяем ее описанным выше способом на ячейки  (I14:I20).

Рис.2.16. Решение системы ЛАУ

 

Макросы

Если при работе с MS Excel возникает необходимость несколько раз выполнить одну и ту же последовательность действий, то можно записать эту последовательность под определенным именем. Запи­санная последовательность называется макросом. Его можно вызы­вать для выполнения из основного меню, а также с помощью кнопки на панели инструментов или в рабочей области. Макрос записывается на языке Visual Basic for Application и может быть отредактирован.

Создадим макрос для решения системы линейных уравнений 5- го порядка.

На новом листе заполним вручную матрицу коэффициентов и вектор свободных членов.

Выполним команду Сервис / Запись макроса / Начать запись.

В появившемся окне диалога введем имя макроса "Решение" и укажем клавишу быстрого вызова Ctrl+R. В поле ввода Описание можно ука­зать назначение макроса. После нажатия кнопки ОК все действия бу­дут записываться в макрос. На экране появится кнопка Остановить макрос.

Сначала очистим область, где будут располагаться обратная матрица и вектор решения. Очистка нужна для повторных запусков макроса на выполнение. Затем сформируем обратную матрицу и век­тор решения. В заключение нужно остановить запись макроса. Кроме нажатия на кнопку Остановить макрос можно использовать меню ко­манду Сервис / Запись макроса / Остановить запись. После записи макроса он появляется в списке макросов под именем "Решение". Для вызова макроса нужно выполнить команду Сервис/Макрос. Затем найти требуемый макрос и нажать кнопку Вы­полнить. Можно также воспользоваться назначенной клавишей быст­рого вызова Ctrl+R

Вызвать макрос можно и нажатием кнопки мыши на графическом объекте, связав его с макросом. Нарисуем закрашенный овал с надпи­сью "Решение". Нажмем правую кнопку мыши на графическом объекте. Появится меню со списком команд. Выберем Назначить макрос. В окне диалога выберем макрос "Решение", нажмем кнопку ОК. Щелкнем кнопкой мыши вне объекта. Теперь для вызова макроса достаточно щелкнуть кнопкой мыши на этом графическом объекте.

Аналогично можно назначить макрос рисунку, созданному само­стоятельно в графическом редакторе либо взятому из каталога.

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

Нарисуем кнопку и назначим ей макрос. Включите панель инст­рументов Формы (Вид/Панели инструментов/Формы). Нажмите на этой панели кнопку Кнопка, установите курсор в то место листа, где будет располагаться кнопка и, удерживая нажатой кнопку мыши, выде­лите прямоугольник. Отпустите кнопку мыши и в появившемся окне диалога "Назначить макрос объекту" выберите макрос "Решение". На­жмите кнопку мыши на созданной кнопке и введите вместо номера кнопки требуемый текст. Щелкните кнопкой мыши вне объекта. Нажми­те правую кнопку мыши на созданной кнопке. В появившемся меню выберите команду Формат и установите нужные параметры шрифта и выравнивания.

Для размещения пользовательской кнопки на панели инструмен­тов выполняем Вид/Панели инструментов/Настройка. На вкладке Команды выбираем категорию Макросы. Настраиваемую кнопку пере­мещаем с помощью мыши на панель инструментов. Щелкнув по ней правой кнопкой в контекстном меню назначаем нужный макрос, выби­раем значок для кнопки.

 

Самостоятельное задание

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

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

Сформируйте макрос для проверки правильности решения сис­темы линейных уравнений. Поместите на лист рисунок из каталога Windows и назначьте ему макрос проверки решения.

 

2.8. Лабораторная работа 8