double arrow

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

2

РАБОТА СО СТРУКТУРАМИ ДАННЫХ В MS EXCEL

Цель: Научить студентов работать со структурами данных в MS EXCEL.

ЗАДАНИЕ 1. МАССИВЫ ДАННЫХ В MS EXCEL

4.1 Понятие массива в Excel

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

На рисунке 4.1 приведены примеры различных массивов: в диапазоне B2:F2 расположен одномерный массив чисел целого типа, в диапазоне B4:B8 расположен одномерный массив текстовых данных, а в диапазоне D5:F7 Расположен двумерный массив данных типа ДАТА.

Рисунок 4.1. Примеры массивов в таблице Excel

4.2 Правила работы с массивами

Элементы массива можно обработать, если в этом возникает необходимость, с помощью одного выражения, применяемого ко всему массиву, как к одному целому объекту. Для обработки массива нужно:

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

- ввести нужное выражение, применяемое ко всем элементам массива;

- одновременно нажать клавиши CTRL+SHIFT+ENTER.

4.3 Пример обработки массива

К массивам данных, приведенным на рисунке 4.1, применим следующие действия:

Элементы числового массива возвести в квадрат и увеличить на 5. Из массива фамилий выделить только инициалы, а для массива дат определить количество дней, прошедших с каждого из указанных дат до сегодняшнего дня. Формулы, используемые для обработки элементов данных массивов приведены на рисунке 4.2.

Рисунок 4.2 Обработка элементов массива

При работе с массивами нужно помнить, что нельзя удалить или изменить часть массива, полученного вследствие нажатия клавиш CTRL+SHIFT+ENTER.

4.4 Пример выполнения самостоятельной работы

Дана система линейных уравнений

Решить систему уравнений, т.е. найти такие значения неизвестных х1234 при подстановке которых в уравнения, каждое из уравнений обращается в тождество.

4.4.1 Метод Гаусса

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

Рисунок 4.3 Математическая модель реализации метода Гаусса

Рисунок 4.4 Результат реализации метода Гаусса

4.4.2 Метод Крамера

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

Рисунок 4.5 Математическая модель реализации метода Крамера

Рисунок 4.6 Результат реализации метода Крамера

4.4.3 Метод обратной матрицы

Для реализации данного метода нужно вычислить обратную матрицу для матрицы коэффициентов системы. Затем умножить обратную матрицу и вектор свободных членов.

Рисунок 4.5 Математическая модель реализации метода обратной матрицы

Рисунок 4.6 Результат реализации метода обратной матрицы

4.5 Варианты индивидуальных заданий

В таблицу Excel ввести коэффициенты системы линейных уравнений и свободные члены системы. Решить данную систему тремя методами: Гаусса, Крамера, с помощью обратной матрицы. Для вычисления обратной матрицы использовать функцию Excel =МОБР(), для вычисления определителя матрицы - =МОПРЕД(), для умножения двух матриц - =МУМНОЖ(). При решении системы использовать приемы работы с массивами.

Вариант1 2,13 x 1+1,3 x 2 -0,11 x 3+0,07 x 4=2,15 0,12 x 1+2,03 x 2+0,09 x 3 -0,08 x 4=0,44 -0,36 x 1-0,84 x 2+0,28 x 3+0,06 x 4=-0,83 0,02 x 1+5,1 x 2+1,04 x 3-0,12 x 4=1,16 Вариант2 3,51 x 1+0,17 x 2 +3,75 x 3-0,28 x 4=0,75 4,52 x 1+2,11 x 2-0,11 x 3 -0,12 x 4=1,11 -2,11 x 1-3,17 x 2+0,12 x 3-0,15 x 4=0,21 3,17 x 1+1,81 x 2-3,17 x 3+0,22 x 4=0,05 Вариант3 0,17 x 1+0,75 x 2 -0,18 x 3+0,21 x 4=0,11 0,75 x 1+0,13 x 2 + 0,11 x 3 –1,00 x 4=2,00 -0,33 x 1+0,11 x 2+3,01 x 3-2,01 x 4=0,11 0,11 x 1+1,12 x 2+1,11 x 3-1,31 x 4=0,13 Вариант4 - x 1+0,13 x 2 –2,00 x 3-0,14 x 4=0,15 0,75 x 1+0,18 x 2 -0,21 x 3 –0,77 x 4=0,11 0,28 x 1-0,17 x 2+0,39 x 3+0,48 x 4=0,12 x 1+3,14 x 2-0,21 x 3- x 4=-0,11 Вариант5 3,01 x 1-0,14 x 2 + x 3-0,15 x 4=1,00 -0,75 x 1+1,11 x 2 +0,13 x 3 –0,75 x 4=0,13 0,17 x 1-2,11 x 2+0,71 x 3+1,71 x 4=1,00 0,21 x 1+0,21 x 2+0,35 x 3+0,33 x 4=0,17 Вариант6 1,15 x 1+0,62 x 2 –0,83 x 3-0,92 x 4=2,15 0,82 x 1-0,54 x 2 +0,43 x 3 –0,25 x 4=0,62 0,24 x 1+1,15 x 2-0,33 x 3+1,42 x 4=-0,62 0,73 x 1-0,81 x 2+1,27 x 3-0,67 x 4=0,88 Вариант7 2,2 x 1-3,17 x 2 +1,24 x 3-0,87 x 4=0,46 1,5 x 1+2,11 x 2 –0,45 x 3 +1,44 x 4=1,50 0,86 x 1-1,44 x 2+0,62 x 3+0,28 x 4=-0,12 0,48 x 1+1,25 x 2-0,63 x 3-0,97 x 4=0,35 Вариант8 0,64 x 1+0,72 x 2 –0,83 x 3+4,2 x 4=2,23 0,58 x 1-0,83 x 2 +1,43 x 3 –0,62 x 4=1,71 0,86 x 1+0,77 x 2-1,83 x 3+0,88 x 4=-0,54 1,32 x 1-0,52 x 2-0,65 x 3+1,22 x 4=0,65 Вариант9 1,42 x 1+0,32 x 2 –0,42 x 3+0,85 x 4=1,32 0,63 x 1-0,43 x 2 +1,27 x 3 –0,58 x 4= -0,44 0,84 x 1-2,23 x 2-0,52 x 3+0,47 x 4=-0,64 0,27 x 1+1,37 x 2-0,64 x 3-1,27 x 4=0,85 Вариант10 0,73 x 1+1,24 x 2 –0,38 x 3-1,43 x 4=0,58 1,07 x 1-0,77 x 2 +1,25 x 3 +0,66 x 4= -0,66 1,56 x 1+0,66 x 2+1,44 x 3-0,87 x 4=1,24 0,75 x 1+1,22 x 2-0,83 x 3-0,37 x 4=0,92 Вариант11 3,51 x 1+0,17 x 2 +3,75 x 3-0,28 x 4=0,75 4,52 x 1+2,11 x 2-0,11 x 3 -0,12 x 4=1,11 -2,11 x 1-3,17 x 2+0,12 x 3-0,15 x 4=0,21 3,17 x 1+1,81 x 2-3,17 x 3+0,22 x 4=0,05 Вариант12 1,15 x 1+0,62 x 2 –0,83 x 3-0,92 x 4=2,15 0,82 x 1-0,54 x 2 +0,43 x 3 –0,25 x 4=0,62 0,24 x 1+1,15 x 2-0,33 x 3+1,42 x 4=-0,62 0,73 x 1-0,81 x 2+1,27 x 3-0,67 x 4=0,88 Вариант13 - x 1+0,13 x 2 –2,00 x 3-0,14 x 4=0,15 0,75 x 1+0,18 x 2 -0,21 x 3 –0,77 x 4=0,11 0,28 x 1-0,17 x 2+0,39 x 3+0,48 x 4=0,12 x 1+3,14 x 2-0,21 x 3- x 4=-0,11

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

1) Что называется массивом в Excel?

2) Какой массив называется одномерным?

3) Какой массив называется двумерным?

4) Как производится обработка элементов массива?

5) Какой особенностью обладают массивы?

ЗАДАНИЕ 5. ДАННЫЕ ЛОГИЧЕСКОГО ТИПА. ФУНКЦИЯ ВЫБОРА ЕСЛИ

2.1 Логическое выражение

Выражение называется логическим, если в нем присутствуют знаки операций отношения и логические функции:

> -“больше”, < -“меньше”, >= -“больше или равно”,

<= -“меньше или равно”, = -“равно”, <> -“не равно”

=И(арг1;арг2), =ИЛИ(арг1;арг2), =НЕ(арг)

Значения логических функций определяются в зависимости от значений аргументов.

Таблица 2.1 Таблица истинности для функций НЕ, И, ИЛИ

Арг1 арг2 =НЕ(арг1) =И(арг1;арг2) =ИЛИ(арг1;арг2)
И И Л И И
И Л Л Л И
Л И И Л И
Л Л И Л Л

2.2 Функция выбора ЕСЛИ

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

В общем виде формат функции имеет следующий вид:

=если(усл1;действ1;если(усл2;действ2;…;если(усл n-1; действ n-1; действ n)…)

При выполнении данной функции проверяются условия, и будет выполнено то действие, которое соответствует истинному условию, если ни одно условие не оказалось истинным, то выполняется действие n.

2.3 Контрольное задание

Значение логического выражения.

Определим порядок действий в следующем логическом выражении

Рисунок 2.1 Логическое выражение в Excel

9 4 1 3 2 8 5 7 6

=И(ИЛИ(B2>-2;НЕ(B2+D2<>4));ИЛИ(B2>D2-4;НЕ(D2<0)))

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

1. B2>-2 – ИСТИНА; 2. B2+D2<>4 – ИСТИНА;

3. НЕ(B2+D2<>4) – ЛОЖЬ 4. ИЛИ(B2>-2;НЕ(B2+D2<>4) - ИСТИНА;

5. B2>D2- 4 - ИСТИНА; 6. D2<0 – ЛОЖЬ;

7. НЕ(D2<0) – ИСТИНА; 8. ИЛИ(B2>D2-4;НЕ(D2<0)) – ИСТИНА

9. И(ИЛИ(B2>-2;НЕ(B2+D2<>4));ИЛИ(B2>D2-4;НЕ(D2<0))) – ИСТИНА

Значение функции ЕСЛИ.

Рисунок 2.2 Пример функции ЕСЛИ

Если значением ячейки A1 будет являться число 7, а ячейки B1 - 14, то значением данной функции будет число 2.

Если значением ячейки A1 будет являться число 10, а ячейки B1 - 6, то значением данной функции будет число 16.

Если значением ячеек А1 и В1 будут являться число 5, то значением данной функции будет число 25.

2.4 Варианты индивидуальных заданий

Вариант1:

а) Определить значение логического выражения

=И(НЕ(A2<3);ИЛИ(И(A2+4>5;B5<=6);НЕ(A2+B5<0)))

при a) A2: -5; B5: 6 б) A2: 3; B5: -1

б) Протабулировать функцию, построить график функции.

в) Создать документ (для принятия решения использовать функцию если)

Наименование товара Дата выпуска Срок хранения Решение
  Макароны 07.05.03   =если(…
……

Варианты решения:

-если срок годности истек – снять с продажи;

-если до окончания срока годности осталось меньше 1 года – уценить на 30%;

-если до окончания срока годности остался 1 год и более – реализовать при нормальных условиях;

Использовать функцию =СЕГОДНЯ()

Вариант2:

а) Определить значение логического выражения:

=И(НЕ(3*(A2+C4)/2<-4);ИЛИ(A2>C4*2;НЕ(C4<7)))

при a) A2: -5; C4: 6 б) A2: 3; C4: -1

б) Протабулировать функцию, построить график функции:


в) Создать документ (для принятия решения использовать функцию если

Фамилия И.О. Дата получения книги Решение
  Иванов М.Н. 05.10.03 =если(…
……

Варианты решения:

-если читатель задержал книгу больше, чем на 10 дней, то –щтраф;

-если до сдачи книги остался 1 день – предупредить читателя о возврате книги;

-если до сдачи книги осталось больше 1 дня, то подсчитать количество оставшихся дней до сдачи книги;

Использовать функцию СЕГОДНЯ.

 
 


Вариант3:

а) Определить значение логического выражения:

=ИЛИ(И(C5>7;НЕ(B4<>0));НЕ(С5+B4<15))

при a) B4: -2; C5: 3 б) B4: 7; C5: -4

б) Протабулировать функцию,

построить график функции:

в) Создать документ (для принятия решения использовать функцию если)

Фамилия И.О. Срок лечения (дней) Дата поступления Решение
  Рахимбаев А.Б.   15.03.04 =если(…
……  

Варианты решения:

-если срок лечения истек, то ВЫПИСАТЬ;

-если до окончания срока лечения осталось 2 дня, то ПОДГОТОВИТЬ К ВЫПИСКЕ;

-если до окончания срока лечения более 2-х дней, то ПРОДОЛЖАТЬ ЛЕЧЕНИЕ.

Использовать функцию СЕГОДНЯ.

Вариант 4:

а) Определить значение логического выражения:

=И(ИЛИ(С3>8;НЕ(D2+4<>6));НЕ(C3+D2/2<12))

при a) C3: -5; D2: 4 б) C3: 6; D2: -2

б) Протабулировать функцию, построить график функции:

в) Создать документ (для принятия решения использовать функцию если)

Фамилия И.О. студента Экзаменационные оценки Решение
Математика Физика История
  Ахметов А.С.       =если(…
……    

Варианты решения:

-если средний балл равен 5, то ПОВЫШЕННАЯ СТИПЕНДИЯ;

-если средний балл меньше или равен 4, то СТИПЕНДИЯ БЕЗ НАДБАВКИ;

-если средний балл меньше 4, то НЕ ВЫПЛАЧИВАТЬ СТИПЕНДИЮ;

Использовать функцию СЕГОДНЯ.

Вариант 5:

а) Определить значение логического выражения:

=И(ИЛИ(НЕ(A2+C1<>10));НЕ((A2/2)<14))

при a) А2: 10; С1: 1 б) А2: 2; С1: -2

б) Протабулировать функцию,

построить график функции:


в) Создать документ (для принятия решения использовать функцию если)

Название книги, автор Дата издания книги Решение
  Овод, Э.Л.Войнич 16.07.00 =если(…
……

Варианты решения:

-если дата издания книги 1960 год или ранее, то СПИСАТЬ книгу;

-если дата издания книги позже 1960 г. и ранее 2000 г. - ИЗМЕНИТЬ ШИФР;

-если дата издания книги 2000 г или позднее- ДОБАВИТЬ В КАРТОТЕКУ; Использовать функцию СЕГОДНЯ.

Вариант 6:

а) Определить значение логического выражения:

=ИЛИ(И(НЕ(C3>8);НЕ(D2+4<>6));НЕ(D2/2<4))

при a) C3: 5; D2: 2 б) C3: 6; D2: 8

б) Протабулировать функцию, построить график функции:


в) Создать документ (для принятия решения использовать функцию если)

ФИО заказчика Наименование изделия Срок пошива (дней) Дата принятия заказа Решение
  Ракитова Р.Т. платье   16.04.03 =если(…
    ……

Варианты решения:

-если со дня принятия заказа прошло 2 дня, то ПЕРВАЯ ПРИМЕРКА;

-если со дня принятия заказа прошло 4 дня, то ВТОРАЯ ПРИМЕРКА;

-если со дня принятия заказа прошел срок пошива изделия – ВЫДАТЬ ИЗДЕЛИЕ; Использовать функцию СЕГОДНЯ.

Вариант 7:

а) Определить значение логического выражения:

=НЕ(И(ИЛИ(B1>7;НЕ(B1=0));НЕ(A3+B1<15)))

при a) B1: 10; А3: 3 б) B1: 10; А3: 10

б) Протабулировать функцию, построить график функции:


в) Создать документ (для принятия решения использовать функцию если)

ФИО водителя Номер автомобиля Год выпуска Решение
  Степин И.И. B 457 MHR   =если(…
    ……

Варианты решения:

-если год выпуска 1970 г. и ранее, то ДОРОЖНЫЙ НАЛОГ НЕ ПЛАТИТЬ;

-если год выпуска позднее 1970 и ранее 1990 г., то ОПЛАТИТЬ 50% ДОРОЖНОГО НАЛОГА;

-если год выпуска 1990 и позднее – ОПЛАТИТЬ ДОРОЖНЫЙ НАЛОГ ПОЛНОСТЬЮ;

Использовать функцию СЕГОДНЯ.

Вариант 8:

а) Определить значение логического выражения:

=НЕ(И(ИЛИ(B1>7;НЕ(B1=0));ИЛИ(A3>3;НЕ(B1<15))))

при a) В1: 3; А3: 3 б) В1: 3; А3: 10

б) Протабулировать функцию, построить график функции:

 
 


в) Создать документ (для принятия решения использовать функцию если)

ФИО абонента Номер телефона Долг за переговоры Решение
  Коломина А.Т.     =если(…
    ……

Варианты решения:

-если долг за переговоры 1000 тг. и более, то ОТКЛЮЧИТЬ ТЕЛЕФОН;

-если долг за переговоры МЕНЕЕ 1000 ТГ., то ПОСЛАТЬ ПРЕДУПРЕЖДЕНИЕ;

-если долга нет – включить в список ПОСТОЯННЫХ ПЛАТЕЛЬЩИКОВ;

Использовать функцию СЕГОДНЯ.

Вариант 9:

а) Определить значение логического выражения:

=НЕ(И(ИЛИ(B1>7;НЕ(B1=0));И(C2>3;НЕ(B1<10))))

при a) В1: 5; С2: 8 б) В1: 2; С2: 7

б) Протабулировать функцию,

построить график функции:

в) Создать документ (для принятия решения использовать функцию если)

Фамилия И.О. студента Экзаменационные оценки Решение
Информатика Высш. математика Культурология
  Рахметов Р.Т.       =если(…
……    

Варианты решения:

-если средний балл равен 5, то при оплате за обучение СКИДКА 10%;

-если средний балл менее 5 и равен 4, то при оплате за обучение СКИДКА 5%;-----если средний балл менее 4, то ПОЛНАЯ ОПЛАТА;

Использовать функцию СЕГОДНЯ.

Вариант 10:

а) Определить значение логического выражения:

=НЕ(И(НЕ(A2<3);ИЛИ(И(НЕ(A2+4>5);B5<=6));НЕ(B5<0)))

при a) A2: -3; B5: 8 б) A2: 5; B5: -4

б) Протабулировать функцию, построить график функции:

 
 


в) Создать документ (для принятия решения использовать функцию если)

Наименование препарата Дата изготовления Срок годности Решение
  парацетамол 13.04.03   =если(…
……  

Варианты решения:

-если срок годности истек, то СНЯТЬ С ПРОДАЖИ;

-если до конца срока годности осталось менее 1 года, то УЦЕНИТЬ НА 30%;

-если до конца срока годности осталось 1 год и более, то РЕАЛИЗОВАТЬ В НОРМАЛЬНЫХ УСЛОВИЯХ;

Использовать функцию СЕГОДНЯ.

Вариант11:

а) Определить значение логического выражения:

=И(НЕ(3*(A2+C4)/2<-4);ИЛИ(A2>C4*2;НЕ(C4<7)))

при a) A2: -5; C4: 6 б) A2: 3; C4: -1

б) Протабулировать функцию, построить график функции:


в) Создать документ (для принятия решения использовать функцию если

Фамилия И.О. Дата получения книги Решение
  Иванов М.Н. 05.10.03 =если(…
……

Варианты решения:

-если читатель задержал книгу больше, чем на 10 дней, то –щтраф;

-если до сдачи книги остался 1 день – предупредить читателя о возврате книги;

-если до сдачи книги осталось больше 1 дня, то подсчитать количество оставшихся дней до сдачи книги;

Использовать функцию СЕГОДНЯ.

 
 


Вариант12:

а) Определить значение логического выражения:

=ИЛИ(И(C5>7;НЕ(B4<>0));НЕ(С5+B4<15))

при a) B4: -2; C5: 3 б) B4: 7; C5: -4

б) Протабулировать функцию,

построить график функции:

в) Создать документ (для принятия решения использовать функцию если)

Фамилия И.О. Срок лечения (дней) Дата поступления Решение
  Рахимбаев А.Б.   15.03.04 =если(…
……  

Варианты решения:

-если срок лечения истек, то ВЫПИСАТЬ;

-если до окончания срока лечения осталось 2 дня, то ПОДГОТОВИТЬ К ВЫПИСКЕ;

-если до окончания срока лечения более 2-х дней, то ПРОДОЛЖАТЬ ЛЕЧЕНИЕ.

Использовать функцию СЕГОДНЯ.

Вариант 13:

а) Определить значение логического выражения:

=И(ИЛИ(С3>8;НЕ(D2+4<>6));НЕ(C3+D2/2<12))

при a) C3: -5; D2: 4 б) C3: 6; D2: -2

б) Протабулировать функцию, построить график функции:

в) Создать документ (для принятия решения использовать функцию если)

Фамилия И.О. студента Экзаменационные оценки Решение
Математика Физика История
  Ахметов А.С.       =если(…
……    

Варианты решения:

-если средний балл равен 5, то ПОВЫШЕННАЯ СТИПЕНДИЯ;

-если средний балл меньше или равен 4, то СТИПЕНДИЯ БЕЗ НАДБАВКИ;

-если средний балл меньше 4, то НЕ ВЫПЛАЧИВАТЬ СТИПЕНДИЮ;

Использовать функцию СЕГОДНЯ.

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

1) Какое выражение называется логическим?

2) Какие значения может принимать логическое выражение?

3) Формат логических функций НЕ, И, ИЛИ и значение данных функций в зависимости от значений аргументов?

4) Назначение, формат, и механизм работы функции ЕСЛИ?


Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:  


2

Сейчас читают про: