70101

Электронная таблица Excel. Использование встроенных функций

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

Информатика, кибернетика и программирование

Использование встроенных функций. Значения которые используются для вычисления функций называются аргументами. В качестве аргументов функций можно использовать константы ссылки на ячейки имена диапазонов ячеек а также другие функции. EXCEL содержит более 400 встроенных функций.

Русский

2014-10-15

250.5 KB

5 чел.

Лабораторная работа № 11

Электронная таблица Excel. Использование встроенных функций.

1. ОСНОВНЫЕ ПОЛОЖЕНИЯ

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

Функцию можно использовать как часть формулы, так и автономно размещая  в ячейке рабочего листа. EXCEL содержит более 400 встроенных функций. Вводить с клавиатуры в формулу названия функций и значения входных параметров не всегда удобно и при использовании сложных формул сопровождается возрастанием вероятности возникновения ошибок при вводе. При ручном вводе формулы или функции необходимо вначале ввести символ  <=>. Использование средств автоматизации позволяет оптимизировать процесс создания формул использующих  функции.

Варианты ввода функция следующие:

1. На панели инструментов, как правило, имеется кнопка Автосумма. В ней разработчиками Microsoft заложены пять наиболее часто используемых функций, а также возможность вызова мастера функций (Другие функции…). Отметим, что мастер функций позволяет реализовать  все возможности использования функций.

2. Мастер функций можно вызвать также следующими способами:

- В строке формул нажать кнопку

                       

- Выполнить команду ВставкаФункция

В результате откроется окно следующего вида:

     

Рис. 1. Мастер функций.

      

Выберите категорию функций, с которыми Вы собираетесь работать, а затем необходимую функцию и нажмите кнопку OK. На экране появится   окно ввода аргументов функции (Рис.2).

        

Рис. 2. Окно ввода аргументов функции.

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

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

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

2. ПРАКТИЧЕСКИЕ ЗАДАНИЯ

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

- Создать файл и сохранить в рабочей папке.

- Каждое задание выполнять на отдельном рабочем листе, названия которых выбираете самостоятельно.  

-

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

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

 

Логические функции ЕСЛИ, И, ИЛИ

ЕСЛИ()

Синтаксис:

ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).

И()

Синтаксис:

Логич_знач И( логич_знач1; логич_знач2; ... ; логич_знач30 )

Предназначение: Функция И используется тогда, когда нужно проверить, выполняются ли несколько условий ОДНОВРЕМЕННО.

Аргументы: Функция И принимает от 1 до 30 аргументов (в Office 2007 -- до 256), каждый из которых является логическим значением ЛОЖЬ или ИСТИНА, либо любым выражением или функцией, которое в результате дает ЛОЖЬ или ИСТИНА.

Возвращаемое значение: Функция И возвращает логическое значение. Если ВСЕ аргументы функции И равны ИСТИНА, возвращает ИСТИНА. Если хотя бы один аргумент имеет значение ЛОЖЬ, возвращает ЛОЖЬ.

Примечание: Функция И почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.

Пример

=ЕСЛИ(И((B1>1);(C1<5));12;24) – В ячейке содержащей  формулу устанавливается значение 12, если (B1>1) и (C1<5), при не выполнении хотя бы одного из двух условий  значение в ячейке -24

ИЛИ()

Синтаксис:

Логич_знач ИЛИ( логич_знач1; логич_знач2; ... ; логич_знач30 )

Предназначение: Функция ИЛИ используется тогда, когда нужно проверить, выполняется ли ХОТЯ-БЫ ОДНО из многих условий.

Аргументы: Функция ИЛИ принимает от 1 до 30 аргументов (в Office 2007 -- до 256), каждый из которых является логическим значением ЛОЖЬ или ИСТИНА, либо любым выражением или функцией, которое в результате дает ЛОЖЬ или ИСТИНА.

Возвращаемое значение: Функция ИЛИ возвращает логическое значение. Если ХОТЯ БЫ ОДИН аргумент имеет значение ИСТИНА, возвращает ИСТИНА. Если ВСЕ аргументы имеют значение ЛОЖЬ, возвращает ЛОЖЬ.

Примечание: Функция ИЛИ почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.

2.1. Вычислить  выражения, при различных значениях аргументов. Для этого  на листе Excel  создайте таблицу, в структуре которой предусмотреть диапазоны для ввода значений аргументов и значений вычисленных выражений. Для организации ветвления используйте логические функции ЕСЛИ(), И(), ИЛИ().

1)         x3+cos(x)+ln x                                x>=1  и a+b=5

    y=

          cos(x)3+sin(x)2-5a+1/b1/3                       x< 1 и a+b 5

2)         8*sin(x)-5*x4+tg(x)                             x>=0  и  a=b

   y=      y=a2+b1/2+ln(x)-1/(a+b)                       x< 0 и a b

3)         5*x+x3-lnx                                          x>=0 и a>b

   y=

           (cosx+sinx)2/(a3-b)                               x< 0 и a<=b

      

4)        lnx+8*x+cos(x)                                    x>=0 и a+b 4

    y=  

           1/a2+arctgx2+1/(a+b)1/2                            x< 0 и a+b = 4

      

5)        x5+x2+5*sin(x)                                    x>=0 и a>b

    y=  

           ln(b2)+1/2a2+(cos(x)+b)1/2                    x< 0 и  a<=b

6)        lnx+5*x2+ x                                x>=2 и a+b 6

    y=  

           a2+b/(a-b)+(lnx+6)1/2                             x< 2  и a+b = 6

7)        cos(x)+tg(x)+x2                         x>=0  и  a=b

    y=   1/sin(a)+(lnx)2+1/(a+b)5                                  x<  0  и  ab

8)         5*x2+x3+lnx                   x>=0 и  a>b

    y=  

           1/(a2+b)+lnx-1/(a+b)                                          x< 0 и  a<=b

9)         cos(x)+x6+ x                    x>=0  и  a b

    y=  

           ln(b2)+cos(2a2)-(x+b)1/2                                       x< 0  и  a = b

10)         6*x+1/2*x2+lnx                    x>=2  и  a+b 6

    y=  

             b3+lnx-(a-b)1/2+6                                              x< 2  и   a+b = 6

11)         cos(x)+sin(x)+tg(x)                       x>=0  или a>b

    y=  

             1/a1/2+ln(x) 2-1/(a+b)5                            

12)         x +x2 + ln(x)               x>0    и  a  < b

    y=  

            sin(a)+5*ln(x)2-1/(a+b)5                                         

13)        cos(x)+5*x2+ x            x>=2   или     a + b <0

    y=  

            ln(b2)+ cos(x)2+(a+b)1/2                             

14)         7*x3+x3+2*ln(x)      x>=0   или  a < 0   или a2+b2  = 0   

    y=        

             lg(a2)+arctgx+1/(a2+b2)                                   

      

2.2. Одномерные массивы.

Создать в диапазоне ячеек А2-А10 массив №1, В2-В10 массив №2 и найти для этих массивов значения статистических функций (медианы, максимум, среднее арифметическое (СРЗНАЧ), среднее геометрическое(СРГЕОМ) и корреляцию(КОРРЕЛ)).

2.3. Двухмерные массивы.

Создать в диапазонах ячеек  две матрицы 4 × 4 и найти для этих массивов значения математических функций:

  •  Обратную матрицу
  •  Произведение матриц
  •  Определитель матрицы.

 Транспонировать матрицы с помощью функции ТРАНСП() (из категории ССЫЛКИ И МАССИВЫ)

2.4. Решить задачи, используя подходящие формулы и функции EXCEL:

  •  1. Заданы уравнения двух пересекающихся прямых на плоскости: y=k1*x+b1; y=k2*x+b2. Найти (в градусах) угол между ними, используя формулу tga=(k2-k1)/(1+k1k2).
  •  2.  Может ли шар радиуса r пройти через ромбообразное отверстие с диагоналями p и q?


Описание  задачи
.

Рис. 3

Из рис.3 видно, что радиус R вписанной в ромб окружности, является высотой прямоугольного треугольника. По теореме Пифагора находим длину стороны ромба:

Радиус вписанной окружности

R=(p*q)/(4*St)

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

Замечание: при решении задачи использовать логическую функцию ЕСЛИ().

На листах Excel привести условия задач, исходные данные, результаты расчета.

2.5. Ввести в ячейку дату рождения. С использованием функций Даты и времени определить:

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

Используются функции ГОД(),  МЕСЯЦ(),  ДАТА().

Пример определения количества прожитых месяцев

=(ГОД(A1)-ГОД(A2))*12+МЕСЯЦ(A1)-МЕСЯЦ(A2)

A1 – текущая дата

A2 – дата рождения  

3.Содержание отчета

Отчет по работе должен содержать:

3.1. Цель работы.

3.2. Краткие теоретические сведения.

3.3.  Ссылки на файлы с результатами выполнения заданий.

 

4. Контрольные вопросы

4.1.  Особенности ввода формул в ячейки рабочего листа. Компоненты формул.

4.2.  Создание формул массива.

  1.  .  Что такое функция и как её можно задавать?

4.4.  Как и когда использовать функцию автозаполнения?

4.5.  Мастер функций. Способы вызова.