ЛАБОРАТОРНАЯ РАБОТА № 2

Цель работы:

• познакомиться с использованием функции в Excel;

• научиться осуществлять заимку информации в таблице.

1. Теоретическое введение

1.1. Работа с функциями пакета Excel

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

Также в контекстном меню имеется пункт «Прогрессия» (Рис. 1) с помощью которого можно задать формат отображения числа, тип прогрессии, шаг предельное значение и т.д.

Рис. 1

Если искомая таблица содержит слишком большой интервал чисел, из-за чего не помещается на странице можно попытаться решить эту проблему путем смены ориентации бумаги с книжной на альбомную. Для этого необходимо войти в меню «Файл», выбрать в нем пункт «Параметры страницы» и включить соответствующую опцию (Рис. 2). Если же этого все еще недостаточно, то можно попробовать уменьшить поля — тот же пункт, но закладка № 2.

Теперь разберем работу следующей группы встроенных функций —статистические функции (Рис. 3). Покажем работу статистических функций на примере функции «МАКС» (Рис. 4). В качестве аргумента в ней обычно указывают некоторый диапазон адресов, какую-либо область таблицы, в ячейках которой необходимо найти наибольшее число. Если области поиска не образуют целый прямоугольник, а расположены в разных местах таблицы, то число аргументов функции «МАКС» может оказаться достаточно большим. Кроме того, аргументом может являться и просто определенное число.

Способ задания области аргументов функции «МАКС» стандартные: либо задание диапазона с помощью мыши (при нажатой левой кнопке), либо вводить в каждое поле ввода в мастере функций (Рис. 4) по одному адресу (например, G4 или F4 и т.д.)

Рис.2

Рис. 3

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

Рис. 4

Рис. 5

Очень часто при вводе той или иной функции возникает необходимость указать в качестве ее аргумента не просто адреса каких-нибудь ячеек, а другую встроенную функцию. Например, в случае, когда в определенную ячейку необходимо ввести функцию «ЕСЛИ», которая бы в зависимости от обстоятельств выводила либо среднее значение на некотором интервале чисел, либо просто пробел. В этом случае может получиться довольно сложная формула, имеющая примерно следующую структуру: = ЕСЛИ («условие»; CP3HAЧ(E6:J6); «ПРОБЕЛ»). Кроме того, при формировании условия скорее всего придется вставить еще одну функцию, которая может потребовать ввода следующей и т.д.

В результате формула придет к следующему виду: = ЕСЛИ (ЕОШИБКА (СРЗНАЧ (E6:J6); « »; СРЗНАЧ (E6:J6)). Если перевести данную запись с формализованного языка на обычный, то условие можно сформулировать следующим образом: «Если произойдет ошибка при вычислении среднего значения, то вывести пробел, в противном случае — само среднее значение».

В построении вышеописанной формулы была использована новая встроенная функция «ЕОШИБКА» из категории функций «Проверка свойств и значений», которая выдает значение «Истина» при возникновении любой ошибки.

Вводить такую громоздкую формулу можно вручную. Но есть и более удобный способ. Например, ячейка К4 в строке формул представлена введенным ранее выражением для подсчета среднего значения. Теперь выделяем эту функцию, за исключением знака равенства, и вырезаем ее в буфер обмена,. Затем, не выходя из ячейки, вызываем мастер функций и выбираем вставку функции «ЕСЛИ». В результате появится уже знакомое окно (Лабораторная работа № 1, Рисунок 8) с тремя полями ввода (Рис. 6). В нижнее поле вставляем выражение из буфера обмена, затем во второе поле вводим пробел (в кавычках или без них) и, наконец, в первое поле помещаем еще одну функцию. Для ее вызова мы должны воспользоваться дополнительным списком, расположенный слева вверху окна. При этом, если нужной функции в этом списке нет выбираем пункт «Другие функции» и ищем ее среди них.

Рис. 6

В итоге место окна функции «ЕСЛИ» займет другое окно — функции «ЕОШИБКА» с одним-единственным полем ввода, в которое мы во второй копируем функцию из буфера обмена (Рис. 7).

Рис.7

Кроме числовых данных в таблице могут использоваться также и текстовые пометки. Возможно, при статистической обработке, появится необходимость их считать. Для этого имеется функция «СЧЕТЕСЛИ» категория «Статистические», для которой требуется указать два аргумента — что именно искать и где искать то, что нужно считать: СЧЕТЕСЛИ(«диапазон»; «условие»). Например, диапазон ячеек А4:А14, а в качестве условия зададим наличие в тексте словосочетание: «пр».

В ответе мы получим в нужной ячейке число равное количеству ячеек, содержащих заданное словосочетание. Если же при этом мы хотим поместить в данной ячейке какой-либо комментарий, мы должны представить полученное число в текстовом формате и добавить к нему поясняющие буквы посредством функции «СЦЕПИТЬ» из категории «Текстовые» (Рис. 8).

Рис. 8

В первое поле появившегося окна вводим искомое словосочетание, а во второе вставляем функцию «ТЕКСТ», выбрав ее из списка расположенного слева вверху (Рис. 9). Появится новое поле с двумя полями. Заполнять его будем также как и в предыдущем случае с функцией «ЕОШИБКА» - снизу вверх.

Рис. 9

Сначала в поле «Строка_формат» вводим ноль. В этом случае мы заказываем такую текстовую форму числа, при которой количество знакомест, зарезервированных для текстового представления, определяется целой частью числа (если число однозначное — то одно знакоместо, если двузначное - то два).

В верхнем поле «Значение» вставляем еще одну функцию «СЧЕТЕСЛИ», предварительно выбрав ее из списка (пример заполнения функции «СЧЕТЕСЛИ» показан выше).

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

Рис. 10

В итоге функция будет иметь следующий вид: = ПОИСКПОЗ (D4; E4:J4; 0). Выдавать она будет номер той ячейки в исходном массиве данных, в которой встретится искомое значение, указанное в ячейке D4.

В тех случаях, когда различные показатели сведенные в таблицу имеют разную степень важности (т.е. разные веса), и, при подсчете некой суммарной оценки, это обстоятельство нужно учитывать, можно использовать функцию «СУММПРОИЗВ» из категории «Математические функции» (Рис. 11). В каждом из полей ввода открывшегося окна мы задаем адреса ячеек для перемножения, в нашем примере это адрес ячейки одного показателя и адрес ячейки содержащий значение весовой функции данного показателя.

Рис. 11

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

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

При необходимости оценки полученных показателей по дополнительных критериям, учитывающим одновременно совокупность нескольких показателей можно применять логические функции «ЕСЛИ», «И», «ИЛИ», «ИСТИНА», «ЛОЖЬ», позволяющие составлять достаточно сложные логические конструкции.

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

При создании документов, содержащих не один, несколько рабочих листов данные с одного рабочего листа могут быть легко скопированы на другой рабочий лист через буфер обмена. Для этого необходимо выделить с помощью мыши необходимый диапазон и нажать кнопку «Копировать». По мере надобности рабочие листы можно добавлять, используя для этого пункт «Лист» из меню «Вставка». Чередование листов можно менять произвольным образом, захватив за ярлык листа мышью и перетаскивая его вправо или влево (по необходимости) и отпуская в нужном месте. Для большей наглядности листы можно переименовать. Чтобы сделать это, достаточно щелкнуть по соответствующему ярлыку правой кнопкой мыши, после чего в появившемся контекстном меню выбрать пункт «Переименовать», и после того, как текущее имя будет выделено, ввести новое имя.