Табличные процессоры Microsoft Excel и libreoffice Calc : первоначальное знакомство с возможностями электронных таблиц

 

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

Формируемые компетенции

 

Для студентов, обучающихся по н аправлени ю подготовки 09.03.03 – «Прикладная информатика», профиль подготовки «Прикладная информатика в юриспруденции».

Способность использовать современные информационные технологии для решения учебных, исследовательских и профессиональных задач в области юриспруденции (ПВК-3):

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

- уметь пользоваться современными программными средствами работы с информацией в табличном представлении;

- владеть навыками компьютерной обработки служебной документации.

Для студентов, обучающихся по специальности 40.05.01 «Правовое обеспечение национальной безопасности» и направлению подготовки «Юриспруденция».

Способность работать с различными источниками информации, информационными ресурсами и технологиями, применять основные методы, способы и средства получения, хранения, поиска, систематизации, обработки и передачи информации (ОК-16):

- владеть навыками компьютерной обра ботки служебной документации;

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

ЗАДАНИЕ

 

1. Загрузить процессор электронных таблиц Мiсrоsоft Excel.

2. На первом листе рабочей книги решить следующую задачу.

Для адвокатского бюро необходимо купить два цифровых фотоаппарата (по 23490 р.), два лазерных принтера (по 4845 р.), пять мышек (по 400 р.), пять мониторов (по 6500 р.). Посчитать необходимые расходы, т.е. стоимость каждого товара (стоимость товара = цене х на количество товара) и итоговую сумму, используя три различных способа ввода формул:

а) вводить в формулы конкретные числовые значения;

б) вводить в формулы адреса ячеек с необходимыми данными;

в) ввести только одну формулу, а остальные получить копированием первой (см. § 2.3 блока «Помощь»).

3. Лист переименовать в «л-р-14».

4. Сохранить файл в свою папку с именем, состоящим из фамилии исполнителя.

Внимание! Все лабораторные работы в Excel выполнять в одной книге (файле), но на разных листах. Каждому листу давать имя по номеру работы, например, л-р-15.

 

ПОМОЩЬ

Алгоритм действий

 

1. Записать исходные данные в соответствии с рис. 24.

2. В ячейку Е2 ввести заголовок СПОСОБ 1, ячейки F2 и G2 заполнить с помощью прогрессии, используя метод копирования содержимого ячеек с помощью маркера заполнения (см. § 2.2 блока «Помощь»).

3. В ячейки Е3, Е4, Е5 и Е6, т.е. в блок Е3:Е6, пользуясь первым способом (ввод конкретных числовых значений), занести формулы для подсчета стоимости соответствующего ячейке товара, а именно: в ячейку Е3 ввести = 23490*2, в ячейку Е4 ввести = 4845*2,... и в ячейку Е6 = 6500*5. Ввод формулы подтверждается клавишей [ Enter ].

4. В ячейки блока F3:F6, вторым способом (ввод адресов ячеек, т.е. используя в формуле ссылки на адреса конкретных ячеек, в которых находятся цены и количество товара) занести следующие формулы для подсчета стоимости товара, т.е.: в ячейку F3 ввести формулу = C3*D3,..., в F6 = C6*D6.

5. В ячейку G3 занести формулу = C3*D3, вводя после знака «=» адреса ячеек с помощью щелчка по ним мышкой, затем содержимое ячейки G3 скопировать в ячейки блока G4:G6 с помощью маркера заполнения (см. § 2.3 блока «Помощь»). Обратите внимание, как изменяются ссылки при копировании.

 

 

Рис. 24. Исходные данные для задания лабораторной работы 14

 

6. Рассчитать общие расходы (итог) на покупку всего товара как сумму стоимости товара в столбце:

1) при подсчете суммы для блока ячеек Е3:Е6 использовать вариант ввода формулы с клавиатуры, т.е. в ячейку Е8 занести формулу =Е3+Е4+Е5+ + Е6 и нажать клавишу [ Enter ];

2) для блока ячеек F3:F6 использовать встроенную функцию суммирования, т.е. установить курсор таблицы в ячейку F8 и вызвать мастер функций, щелкнув мышкой по кнопке-команде  ВСТАВИТЬ ФУНКЦИЮ (), расположенную на перемычке строки формул. Также мастер функций можно вызвать с помощью кнопки-команды  → ДРУГИЕ ФУНКЦИИ. В окне мастера функций в поле КАТЕГОРИЯ выбрать ПОЛНЫЙ АЛФАВИТНЫЙ ПЕРЕЧЕНЬ, а в поле ВЫБЕРИТЕ ФУНКЦИЮ: – СУММ и нажать кнопку [ ОК ], в следующем окне для элемента ЧИСЛО 1 указать блок ячеек, для которых следует подсчитать сумму, т.е. F3:F6.

Выполнение: установить в окне элемента ЧИСЛО 1 курсор ввода и выделить необходимый блок ячеек с помощью мыши. Нажать кнопку [ОК];

3) для ячеек блока G3:G6 подсчитать сумму с помощью кнопки-команды СУММА ().

Выполнение: выделить блок ячеек G3:G8, выполнить щелчок по кнопке-команде.

7. Подобрать исходные данные в ячейке С3 так, чтобы итоговая сумма в ячейке F8 не превышала 90000 р. Операцию подбора выполнить с помощью функции ПОДБОР ПАРАМЕТРА с вкладки ДАННЫЕ (см. рис. 25).

Рис. 25. Вывоз функции ПОДБОР ПАРАМЕТРА

Выполнение: в окне команды ПОДБОР ПАРАМЕТРА установить следующие значения: УСТАНОВИТЬ В ЯЧЕЙКЕ - $F$8 (выполнение: установить курсор ввода в окне УСТАНОВИТЬ В ЯЧЕЙКЕ: и выполнить щелчок мышкой по ячейке F8); ЗНАЧЕНИЕ – 90000; ИЗМЕНЯЯ ЗНАЧЕНИЕ ЯЧЕЙКИ – $С$3 (выполнение: установить курсор ввода в окне ИЗМЕНЯЯ ЗНАЧЕНИЕ ЯЧЕЙКИ и выполнить щелчок мышкой по ячейке С3).

 

Основные понятия

Табличные процессоры (редакторы) (например, Мiсrоsоft Excel или LibreOffice Calc) – общее название программных средств, предназначенных для обработки электронных таблиц.

Под электронной таблицей понимают способ представления информации в табличной форме, хранимой в памяти компьютера. Активное рабочее окно представлено в процессорах Excel и Calc в виде таблицы, неотъемлемым и основным элементом которой является курсор таблицы, имеющий вид черной рамки, выделяющей текущую (активную) ячейку.

Файл (документ, созданный в табличных процессорах, имеющий в Excel расширение «. xlsx», а в Calc «. ods») представляет собой рабочую книгу, состоящую из листов – электронных таблиц.

Ячейка – элемент таблицы, находящийся на пересечении столбца и строки.

Адрес ячейки состоит из адреса столбца (большая латинская буква) и номера строки, например: А1, С12 и т.д.

Текущая (активная) ячейка – ячейка, выделенная курсором таблицы, её адрес отображается в левой части строки формул, а содержимое – в правой.

Строка формул – строка, расположенная под вкладочным меню и отображающая адрес текущей ячейки и её содержимое.

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

Блок ячеек – непрерывная прямоугольная область ячеек (одна ячейка, строка, столбец, прямоугольная область). Блок задается адресами левой верхней и правой нижней ячеек через двоеточие, например, А1 (ячейка), С6:К6 (строка), В3:В8 (столбец), A4:D9 (блок или прямоугольная область).

 

Методические указания

Имя ячейки складывается из заголовка столбца и номера строки, на пересечении которых она находится, например, А1, Z324, АА12, IV16384. Полное имя ячейки также включает в себя имена файла рабочей книги и листа, на которых она расположена. Так, ячейка А1 на листе «Лист 1» в рабочей книге «Книга1» имеет полное имя [Книга1]Лист1!А1. В редакторе Calc  в полном имени ячейки вместо восклицательного знака стоит точка.

Перемещение по листу выполняется при помощи полос прокрутки или кнопок клавиатуры: [↑],[↓], [→], [←], [ Page Up ], [ Page Dn ].

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

Информация, вводимая в ячейки электронной таблицы, бывает трех видов: текст, число и формула. Числовая информация по умолчанию разделяется запятой.

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

Удаление данных из ячейки производится клавишей [ Delete ].

Чтобы редактировать данные в ячейке, 1) активизируйте соответствующую ячейку и нажмите клавишу F2, или 2) щелкните один раз мышкой в правой части строки формул, или 3) выполните двойной щелчок мышкой в нужной ячейке.

Для форматирования ячеек в Excel служит команда ФОРМАТ ЯЧЕЕК из динамического меню или из окна кнопки ФОРМАТ с вкладки ГЛАВНАЯ. В Calc – команда ЯЧЕЙКИ меню ФОРМАТ.

Операции копирования и перемещения. Для перемещения и копирования данных в электронных таблицах используется стандартный механизм, использующий буфер обмена. Последовательность действий: 1) выделить исходную ячейку или блок ячеек; 2) вызвать команду КОПИРОВАТЬ или ВЫРЕЗАТЬ; 3) активизировать (выделить) итоговую ячейку; 4) выполнить команду ВСТАВИТЬ.

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

Для выполнения в Excel операции копирования необходимо во время перемещения содержимого ячейки с помощью мышки удерживать в нажатом состоянии клавишу [ Ctrl ], при этом рядом с указателем мыши появится «плюс» – признак операции копирования.

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

Блоки ячеек (диапазоны). Блок (диапазон) представляет собой объединение нескольких ячеек. Выделение блока производится при помощи мыши. Выделенные ячейки, кроме той, с которой начиналось выделение, отмечаются черным цветом. Несмежные группы ячеек выделяются мышью с нажатой клавишей [ Ctrl ]. Чтобы выделить целый столбец, необходимо щелкнуть левой кнопкой мыши по заголовку данного столбца; аналогично, выделяется строка. Выделение всех ячеек на рабочем листе осуществляется с помощью щелчка мышью по серому прямоугольнику, расположенному в левом верхнем углу листа на пересечении строки заголовков столбцов и строк таблицы.

Выделенный диапазон имеет имя, например, B2:D4, где В2 и D4 – имена левой верхней и правой нижней ячеек блока соответственно. Диапазон, состоящий из несмежных блоков ячеек записывается через «точку с запятой», например, А1:А4;С2:Е5. Операции очистки (удаления), копирования, перемещения и заполнения для блоков выполняются так же, как и для отдельных ячеек.

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

Правила ввода формул. 1. Формула всегда начинается со знаков « = », «+» или «-». 2. Адреса ячеек вводятся в режиме латинских букв. 3. Аргументы функций заключаются в круглые скобки.

Мастер функций в Excel. Встроенные функции можно вводить вручную с клавиатуры или при помощи мастера функций, который вызывается нажатием одноименной кнопки в строке формул. Мастер функций также может вызываться кнопкой СУММА → ДРУГИЕ ФУНКЦИИ с вкладки ГЛАВНАЯ.

В табличных процессорах или редакторах имеется возможность копирования формул. Данная операция осуществляется аналогично операции копирования ячеек, самым простым и удобным способом при этом является использование маркера заполнения. Ссылки на исходные ячейки, которые при копировании изменяют свою адресацию (номер строки, или обозначение столбца) относительно номера или буквы соответствующей итоговой ячейки, называются относительными. Ссылки на исходные ячейки, которые при копировании не изменяют своей адресации, называются абсолютными. Абсолютная ссылка «заключается» в знаках $, например, $F$5 – абсолютная ссылка на ячейку F5.

 

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

1. Введите в ячейку С8 формулу: С3+С4+С5+С6 и объясните, почему в результате не получилось число.

2. Объясните, почему суммы в ячейках Е8 и G8 не совпадают, а в ячейках F8 и G8 совпадают.

3. Как ввести исходные данные в ячейку?

4. Как изменить содержимое ячейки?

5. Перечислите правила ввода формул.

6. Какие бывают ссылки и как они изменяются при копировании формул?

7. Объясните, как выделить строку, столбец, всю таблицу?

8. Чем отличается перемещение от копирования?

9. Какая клавиша в Excel позволяет перенести содержимое ячейки (выделенного блока) с сохранением информации в исходной ячейке?

10. Какое расширение присваивает программа документам процессора Excel?

Литература: [3].

ЗАДАЧА ДЛЯ САМОСТОЯТЕЛЬНОГО РЕШЕНИЯ

 

В редакторе Calc подсчитайте необходимые расходы на покупку для каждого из трех детективных агентств следующей техники: два диктофона (по 19 060 р.), три фотоаппарата (по 27 845 р.), двенадцать жучков прослушек (по 7 800 р.) Количество детективных агентств в формулах использовать как абсолютную ссылку.

 

Литература: [3, 5].

 

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