лаб. Лабоработное_1_Excel_основные_принципы_работы. Лабораторная работа 1 Гомель, 2022 прикладные системы обработки данных
 Скачать 1.16 Mb.
|
|
2022 УО «Белорусский торгово-экономический университет потребительской кооперации» Кафедра бухгалтерского учета Лабораторная работа 1 Гомель, 2022 ПРИКЛАДНЫЕ СИСТЕМЫ ОБРАБОТКИ ДАННЫХ А.В. Медведев, доцент кафедры бухгалтерского учета, канд. экон. наук, доцент Прикладные системы обработки данных Лабораторная 1 1 ТЕМА 1 Основы работы с электронными таблицами Microsoft Excel Цели работы: • Изучить основные элементы интерфейса MS Excel • Получить практические навыки по вводу и редактированию данных в MS Excel • Изучить возможности MS Excel по оформлению таблиц и выполнению вычислений Порядок выполнения работы: • Изучить интерфейс табличного процессора MS Excel • Выполнить задания, согласно выбранному уровню сложности • Оформить отчет по лабораторной работе • Ответить на контрольные вопросы Содержание отчета: • номер и название лабораторной работы • цель работы • протокол выполненных действий • ответы на контрольные вопросы Задания для лабораторной работы: Уровень Требования 1 Выполните Задания № 1, 2 2 Выполните Задание № 1, 2, 3 3 Выполните Задания № 1, 2, 3, 4 Прикладные системы обработки данных Лабораторная 1 2 Краткие теоретические сведения Интерфейс программы Интерфейс текстового процессора Microsoft Excel представляет собой стандартный оконный интерфейс. Многооконная организация Microsoft Excel позволяет одновременно работать с несколькими документами, каждый из которых расположен в своем окне. Рабочий файл Microsoft Excel (*.xlsx) называется рабочей книгой. Рабочая книга состоит из листов, имена которых (Лист1, Лист2, …) выведены на ярлыках в нижней части окна рабочей книги. Щелкая по ярлыкам, можно переходить от листа к листу внутри рабочей книги. Для прокручивания ярлыков используются кнопки слева от горизонтальной координатной линейки. Рисунок 1.1 – Интерфейс главного окна Microsoft Excel Рабочий лист представляет собой таблицу, состоящую из 16384 столбцов и 1048576 строк. Столбцы именуются латинскими буквами, а строки – цифрами. Каждая ячейка таблицы имеет адрес, который состоит из имени строки и имени столбца. Например, если ячейка находится на пересечении столбца F и строки 7, то она имеет адрес F7. Одна из ячеек таблицы всегда является активной. Активная ячейка выделяется рамкой. Чтобы сделать ячейку активной, необходимо клавишами управления курсором подвести рамку к этой ячейке или щелкнуть в ней мышью. Для выделения нескольких смежных ячеек необходимо установить указатель мыши в одну из ячеек, нажать левую кнопку мыши и, не отпуская ее, растянуть выделение на всю область. Для выделения нескольких несмежных групп ячеек следует выделить одну группу, нажать клавишу Ctrl и, не отпуская ее, выделить другие ячейки. Чтобы выделить целый столбец или строку таблицы, необходимо щелкнуть мышью на его имени. Для выделения нескольких столбцов или строк следует щелкнуть на имени первого столбца или строки и растянуть Прикладные системы обработки данных Лабораторная 1 3 выделение на всю область. Для выделения нескольких листов необходимо нажать клавишу Ctrl и, не отпуская ее, щелкать на ярлыках листов. Ячейки и их адресация Основным элементов электронной ячейки является ячейка, которая образуется на пересечении строк и столбцов. Для обращения (ссылки) к любой ячейке используется ее адрес, например, А1 или $С$4. Адрес электронной таблицы может быть двух видов: относительный (А1) и абсолютный ($С$4). Ячейка электронной таблицы может содержать: • число • формулу • текст Диапазон ячеек На данные, расположенные в соседних ячейках, можно ссылаться в формулах, как на единое целое. Такую группу ячеек называют диапазоном. Наиболее часто используют прямоугольные диапазоны. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например, А1:С15. Выделяется прямоугольный диапазон ячеек протягиванием указателя мыши от одной угловой ячейки до противоположной по диагонали. Рамка текущей ячейки при этом растягивается, охватывая весь выбранный диапазон. Чтобы выбрать столбец или строку целиком, достаточно щелкнуть на заголовке столбца (строки). Ввод и редактирование данных Для ввода данных в ячейку необходимо сделать ее активной и ввести данные с клавиатуры. Данные появятся в ячейке и в строке формул. Для завершения ввода следует нажать Enter или одну из клавиш управления курсором, процесс ввода данных закончится и активной станет соседняя ячейка. Чтобы отредактировать данные в ячейке, необходимо: сделать ячейку активной и нажать клавишу F2 или дважды щелкнуть в ячейке мышью; в ячейке появится текстовый курсор, который можно передвигать клавишами управления курсором; отредактировать данные; выйти из режима редактирования клавишей Enter. Перед выполнением любой команды Microsoft Excel следует завершить работу с ячейкой, т. е. выйти из режима ввода или редактирования. Форматирование содержимого ячеек Чтобы изменить формат отображения данных в текущей ячейке или выбранном диапазоне, используется команда Формат → Формат ячеек. На рисунке 1.2 представлено диалоговое окно формата ячеек. На вкладке Число выбирается требуемый формат записи данных: количество знаков после запятой, указание денежной единицы, способ записи даты или времени и т.д. Другие вкладки позволяют задавать направление текста и метод его выравнивания (вкладка Выравнивание), Прикладные системы обработки данных Лабораторная 1 4 определять шрифт и начертание символов (вкладка Шрифт), управлять отображением и видом рамок (вкладка Шрифт), задавать фоновый цвет (вкладка Вид), защищать лист (вкладка Защита). Рисунок 1.2 – Диалоговое окно Формат ячеек Автоматизация ввода К средствам автоматизации ввода данных относятся автозавершение и автозаполнение. Автозавершение. В ходе ввода текста в очередную ячейку программа проверяет соответствие введенных символов строкам, имеющимся в этом столбце выше. Если обнаружено однозначное совпадение, введенный текст автоматически дополняется. Нажатие клавиши Enter подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант. Автозаполнение константами. В правом нижнем углу рамки ячейки имеется черный квадратик - это маркер автозаполнения. При наведении на него курсор мыши вместо толстого белого креста принимает вид тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция «размножения» содержимого ячейки в горизонтальном и вертикальном направлении. Если ячейка содержит число, то при перетаскивании маркера происходит либо копирование ячеек, либо их заполнение данными по закону арифметической прогрессии. Для выбора способа автозаполнения применяется специальное перетаскивание по правой кнопке мыши. Например, ячейка А1 содержит число L Протяните маркер заполнения с помощью правой кнопки мыши с ячейки А1 до ячейки С1 и отпустите кнопку. Если в появившемся меню выбрать пункт Копировать, то во всех ячейках появится 1, если пункт Заполнить, то в ячейках окажутся числа 1, 2, 3. Заполнение прогрессией. Чтобы сформулировать условия заполнения ячеек, следует в группе команд Редактирование выполнить Заполнить → Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. Прикладные системы обработки данных Лабораторная 1 5 Работа с формулами Основной объект, размещаемый в отдельной ячейке электронной таблицы, – это формула. Формула в электронных таблицах всегда начинается со знака «=». Она может содержать адреса ячеек, знаки операций (+, –, /, *) и стандартные функции. Для изменения порядка выполнения арифметических операций используется круглые скобки (). Адреса в формулах указывают на те ячейки, значения которых будут использоваться при вычислениях. Формулы, занесенные в ячейки, можно копировать или переносить в другие ячейки. При копировании формул проявляется различие относительных и абсолютных адресов. При копировании формулы с относительными адресами изменяются. Абсолютные адреса в этом случае остаются неизменными, постоянными. Например, формула =А2+В2+$А$1 при копировании на строку ниже измениться и примет вид: =АЗ+ВЗ+$А$1. Очень часто приходится изменять ранее созданные формулы (добавлять новые ссылки и т.п.). Редактировать формулы можно несколькими способами: либо в строке формул, либо непосредственно в ячейке на рабочем листе. Для редактирования в строке формул необходимо щелкнуть на ячейке (сделать её активной), содержащей формулу, а затем в строке формул редактировать так же, как обычный текст. Для редактирования непосредственно в ячейке следует дважды щелкнуть на ней – ячейка переключится в режим редактирования, и в ней появится введенная формула. После этого можно редактировать формулу точно так же, как это делалось в строке формул. Для перехода в режим редактирования непосредственно в ячейке можно так же нажать клавишу F2. Мастер функций Мастер функций запускается либо из меню Формулы (на ленте) → Вставить функцию, либо выбирается значок возле строки для ввода формул. Мастер функций обеспечивает доступ к большому набору встроенных функций Excel. Функции сгруппированы по категориям: математические, финансовые, даты/времени, статистические и т.д. Более подробную информацию о функции, пользователь может получить, выделив имя функции и нажав ссылку Справка по этой функции, в мастере функций. Арифметические операции Microsoft Excel Функции — это встроенные операторы Excel, с помощью которых проводятся сложные математические вычисления. Например, если у вас есть таблица из тысячи чисел и вам необходимо найти среди них максимальное, то сделать это вручную весьма непросто. Для выполнения этой задачи можно применить функцию МАКС(), при этом следует указать диапазон. Для нахождения среднего значения большого набора чисел можно создать формулу, которая бы Прикладные системы обработки данных Лабораторная 1 6 суммировала эти числа, а затем делила бы суммарное значение на количество этих чисел. Но можно применить к этому набору чисел готовую функцию, которая выполнит те же самые действия. Эта функция Excel называется СРЗНАЧ. Всего в Excel более 450 встроенных функций. Кроме возможности использования огромного числа готовых функций, при необходимости можно создавать собственные функции, выполняющие именно ваши повседневные задачи. Формула — это ряд функций, ссылок на числа или самих чисел взаимосвязанных в определенной последовательности операторами. Формула может состоять только из одной функции или ряда функций взаимосвязанных между собой. В Excel как каждая формула, так и каждая функция должна начинаться со знака равенства "=". Например, если в ячейку будет помещено выражение СРЗНАЧ (ВЗ:В20), то ничего не произойдет. Но если перед этим выражением поместить знак равенства, то эта запись будет восприниматься Excel как формула, состоящая из функции, которую необходимо выполнить. Операторы — это математические символы, указывающие операции, выполняемые над аргументами функции или формулы. Все операторы можно отнести к одной из четырех категорий: арифметические, сравнения, текстовые и операторы ссылок. Арифметические операторы служат для выполнения таких арифметических операций, как сложение, вычитание, умножение. Эти операции выполняются над числами. Операторы сравнения используются для сравнения двух значений, результатом которого является логическое значение: либо ИСТИНА, либо ЛОЖЬ. Категория текстовых операторов содержит только один оператор конкатенации, который используется для объединения нескольких текстовых строк в одну строку. Операторы ссылок используются для описания ссылок на диапазоны ячеек. Перечисленные категории операторов приведены в следующей таблице. Таблица 1 – Категории операторов Операторы Название и назначение Пример Арифметические + Сложение 2 + 3 - Вычитание 5-1 - Знак отрицательного числа -7 * Умножение 7*3 / Деление 7/2 % Процент 90% ^ Возведение в степень 7Л2 Сравнения = Равно А1=В1 > Больше чем А1>В1 < Меньше чем АК<В1 >= Больше или равно А1>=В1 <= Меньше или равно А1<=В1 <> Не равно А1<>В1 Прикладные системы обработки данных Лабораторная 1 7 Текстовый & Оператор конкатенации: объединяет несколько последовательностей знаков в одну Формула "Северный "&"ветер" приведет к результату "Северный ветер" Операторы ссылок : Двоеточие - ставится между ссылками на первую и последнюю ячейки диапазона, что является ссылкой на диапазон В5:В15 ; Точка с запятой - объединяет несколько ссылок одну B5:B15; D5:D15 Пробел Служит для ссылки на общие ячейки двух диапазонов B7:D7 D6:D8 Сложные функции и формулы могут включать в себя много различных операторов. В этом случае важен порядок выполнения операторов, который определяется приоритетом операторов. Приоритеты операторов, т.е. последовательность, в которой Excel выполняет операции, показаны в следующей таблице. Операторы Название и назначение Пример 1 Двоеточие, пробел, запятая Операторы ссылок 2 – Знак "минус" отрицательного числа 3 % Процент 4 ^ Возведение в степень 5 * и / Умножение и деление 6 + и – & Сложение и вычитание Объединение двух текстовых строк в одну 7 = < > <= >= <> Сравнение Использование арифметических операторов в Excel Для проведения непосредственных вычислений в формулах используются арифметические операторы. Формула может содержать операторы сложения, вычитания, умножения или деления и операцию возведения числа в степень. Также, вместо ввода процентов в виде дроби (25/100) или десятичном виде (0.25), можно писать в формуле сразу 0.25%. Excel переведет это выражение в десятичный вид, и будет использовать его при вычислениях. Если в формуле при вычислениях используется несколько арифметических операторов Excel при определении приоритета арифметических операторов придерживается стандартных математических правил. Однако порядок выполнения операторов можно изменить с помощью круглых скобок. Excel выполняет операции в скобках в первую очередь, таким образом, вы можете сами устанавливать нужный порядок вычислений. Если в формуле некоторые операции заключены в скобки, то порядок вычислений такой. Сначала выполняются операции в скобках, затем вычисления в порядке приоритета операторов, а среди операторов с одинаковым приоритетом — слева направо. Прикладные системы обработки данных Лабораторная 1 8 ПЕРВЫЙ УРОВЕНЬ Задание 1 - Создание таблицы в MS Excel Создайте новую рабочую книгу и оформите таблицу в соответствии с рисунком 1.3 Рисунок 1.3 – Условие задания 1 Методика выполнения Задания 1: 1. Запустите программу Excel: Пуск→Программы→ Microsoft Office→Microsoft Office Excel 2. Создайте новую рабочую книгу: Файл → Создать. 3. Сохраните рабочую книгу в своей папке: Файл → Сохранить. Присвойте имя файлу Лаб1. 4. Переименуйте текущий рабочий лист. Для этого дважды щелкните на ярлычке рабочего листа с надписью Лист1 и наберите имя Задание № 1. 5. Объедините ячейки A1:F1 (выделите эти ячейки, нажмите на панели инструментов значок ) и введите заголовок таблицы Движение товаров на складе. 6. Оформите шапку таблицы. Для этого выделите ячейки A2:F2 и установите для них в диалоговом окне Формат ячеек на вкладке Выравнивание флажок Переносить по словам. 7. Заполните таблицу в соответствии с рисунком 1.3 8. Используя команду Формат → Формат ячеек измените начертание, размер шрифта, выравнивание текста в таблице, добавьте границы и установите соответствующую заливку ячеек. Прикладные системы обработки данных Лабораторная 1 9 Задание 2 - Вычисления в таблицах Рассчитайте значения в графе «Остаток на конец месяца» и в графе «Итого». Результат выполнения Задания № 2 - на рисунке 1.4. Рисунок 1.4 – Результат выполнения задания 2 Методика выполнения задания № 2 1. Для расчета значения в графе «Остаток на конец месяца» в ячейку F3 занесите формулу =C3+D3-E3. Примечание. Формула всегда начинается со знака «равно». Далее вводятся адреса ячеек. Адреса ячеек можно вводить вручную, но предпочтительнее указывать их мышкой. 2. Скопируйте формулу из ячейки F3 в диапазон ячеек F4:F9. Для этого выделите ячейки F3:F9. Затем выполните команду Редактирование → Заполнить → Вниз. 3. Вычисление суммы остатка на начало месяца. 4. Выделите диапазон ячеек СЗ:С10. 5. На панели инструментов нажмите кнопку Автосумма . 6. В ячейке С10 будет автоматически сформирована формула =СУММ(СЗ:С10) 7. Аналогично заполните ячейки D10:F10 Прикладные системы обработки данных Лабораторная 1 10 ВТОРОЙ УРОВЕНЬ Задание № 3 – Ссылки на ячейки Сформируйте таблицу «Учет товара на складе» на новом листе текущей рабочей книги в соответствии с рисунком 1.5. Произведите необходимые расчеты. Рисунок 1.5 – Условие задания 3 Методика выполнения задания № 3 1. Объедините ячейки А1:Е1 и введите заголовок Остаток товара на складе 2. В ячейку В2 поместите текущую дату. Сначала необходимо установить формат, в котором будет выводиться дата. Для этого щелкните правой кнопкой мыши по ячейке В2, в появившемся контекстном меню выберите Формат ячеек. В открывшемся окне перейдите на вкладку Число, из списка Числовые форматы выберите пункт Дата. В списке Тип установите требуемый формат. 3. Вызовите Мастер функций (Вставка - Функция или значок на панели инструментов). В списке Категория выберите Дата и время и функцию СЕГОДНЯ(). 4. В ячейке Е2 установить финансовый формат: Формат – Формат ячеек. На вкладке Число выбрать числовой формат Финансовый. 5. Заполните столбец Остаток на конец месяца, используя значения соответствующих ячеек листа Задание № 1. Для этого в ячейку D4 введите формулу ='3адание № 1'!F3 Прикладные системы обработки данных Лабораторная 1 11 Примечание. Связывание таблиц. Часто в одной таблице необходимо использовать данные из другой таблицы. В этом случае следует установить ссылку на лист, в котором расположена требуемая таблица. Такие ссылки имеют следующий формат: =Имя_листа!Адрес_ячейки 6. Скопируйте формулу из ячейки D4 в ячейки D5:D10. 7. Рассчитайте общую стоимость каждого наименования товара в рублях. Для этого в ячейку Е4 занесите формулу =C4*D4*$E$2. Примечание. Знак $ в адресе ячейки указывает на абсолютную адресацию. При копировании формулы, адреса ячеек, содержащие абсолютную ссылку изменяться не будут. 8. Скопируйте формулу из ячейки Е4 в ячейки Е5:Е10. 9. С помощью Автосуммы получите итоговые значения в столбцах Остаток на конец месяца и Общая стоимость. ТРЕТИЙ УРОВЕНЬ Задание № 4 – Защита рабочих листов в Excel Для закрытия ячеек от несанкционированного доступа или нежелательного изменения можно установить защиту. Обычно от изменения защищают ячейки с формулами, названия и шапку таблицы. Рассмотрим порядок установки защиты на лист Задание № 3. 1. Активизируйте лист Задание № 3. 2. Для того, чтобы оставить ячейки С4:С10 доступными для редактирования после защиты листа, выделите их и выполните команду Формат → Формат ячеек - вкладка Защита - снимите флажок Защищаемая ячейка и нажмите ОК. 3. Установите защиту на лист, выполнив команду Сервис → 3ащита → 3ащитить лист. 4. Проверьте, как работает защита листа. Чтобы снять защиту выполните команды: Формат → Снять защиту листа. Контрольные вопросы 1. Как запустить табличный процессор MS Excel? 2. Назовите основные возможности команды Формат → Формат Ячеек 3. Как внести в ячейку формулу? 4. Как заполнить формулой диапазон ячеек? 5. Какие виды адресации в электронной таблице вы знаете? 6. В чем отличие относительной адресации от абсолютной? 7. Как установить защиту рабочего листа и отдельных ячеек? |