Строки. Дата и время. Работа с блоками

Лабораторная работа №1.

«Строки. Дата и время. Работа с блоками»

Задание 1: Пусть в ячейке содержится возраст человека в годах (целое число – количество полных лет). В соседней строке вывести текстовую строку: возраст с указанием «год», «года», «лет».

Задание 3: В ячейках блока A2:A10 содержатся строки цифр, разделенные запятыми (например, в А2 строка «1,2,4», в А3 строка «1,3,6» и т.д.). В В1 пользователь вводит цифру, допустим 2. Поместить в блок формулы, которые возвращают строки «есть» или «нет» в зависимости от того имеется ли в строке из соответствующей ячейки столбца А цифра из В1 или нет (в В2 будет выведено «есть», а в В3 – «нет»). В В11 сосчитать количество ячеек блока А2:А10, содержащих искомую цифру.

Выполнение:

Задание1:

В ячейку В4 вводим целое число – количество полных лет, а в ячейку С4 вводим формулу:

=ЕСЛИ(ОСТАТ($B4;10)=1;"год";ЕСЛИ(ОСТАТ($B4;10)=2;"года";"лет")).

Получаем результат (рис1.).

Рис. 1. Примеры выполнения задания1.

Задание3:

Заполняем диапазон H6:H14 в каждой ячейке тремя цифрами через запятую. В ячейку I5 вводим искомую цифру. В диапазон I6:I14 вводим формулу

=ЕСЛИ(ЕЧИСЛО(ПОИСК($B$1;A7:A15;1));"есть";"нет")

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

Для подсчета количества ячеек, содержащих искомую цифру используем формулу:

=СЧЁТЕСЛИ(B2:B10;"есть")

Получаем результат (рис. 2).

Рис.2. Пример выполнения задания3.

 

Лабораторная работа №2.

«Работа со списками и базами данных в Excel.»

Задание: Создать список, содержащий 60-80 записей:

№	Предметная область	Пояснения
3	Отдел кадров(Фамилия, Имя, Отчество, Отдел, Оклад, Пол, Дата рождения, Возраст, Дата приема на работу)	Поле Возраст необходимо рассчитывать по формуле

 

Над созданным списком необходимо выполнить:

• Сортировку:

№	Сортировка по четырем и более полям	Сортировка в особом порядке
3	Дата рождения, Фамилия, Имя, Отчество	Отдел

 

• Расширенный фильтр:

Определить принимались ли на работу в отделы Отдел1 и Отдел2 несовершеннолетние

 

 

Выполение:

Сортировка:

Заполняем таблицу записями. Поле Возраст рассчитаем по формуле: =РАЗНДАТ(G21; СЕГОДНЯ(); "y"). Выполняем сортировку по четырём полям. Для этого вызываем команду Данные/Сортировка и фильтр/Сортировка и в появившемся диалоговом окне указываем столбцы для сортировки: Дата рождения, Фамилия, Имя, Отчество. При указании каждого из полей для сортировки необходимо выбирать также порядок сортировки: по возрастанию или по убыванию. нажимаем ОК и получаем результат (рис.3).

Рис. 3. Пример выполнения Сортировки по 4 полям

Для сортировки в особом порядке также вызываем команду Данные/Сортировка и фильтр/Сортировка, только в появившемся диалоговом окне в графе Порядок выбираем Настраиваемый список, затем задаем элементы нового списка для сортировки. Так как я выполняю сортировку по полю Отдел, в качестве списка сортировки и задала следующий список (рис.4.):

Рис 4. Создание списка для сортировки

Нажимаем кнопку ОК и получаем отсортированную таблицу (рис.5).

Рис 5. Результат выполнения Сортировки в особом порядке

 

Расширенный фильтр.

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

 

Рис. 6. Расширенный фильтр

Для выполнения выбираем команду меню Данные/Сортировка и фильтр/Дополнительно и в появившемся диалоговом окне указываем диапазоны ячеек (рис 7.).

Рис. 7. Диалоговое окно Расширенный фильтр

Нажимаем ОК и получаем результат (рис. 8):

Рис. 8. Результат выполнения расширенного фильтра

 

 

Лабораторная работа №3.

«Анализ данных в Excel.»

Задание 1: Найти корни уравнения (использовать инструмент Подбор параметра):

Задание 2: Решить систему нелинейных уравнений (использовать инструмент Поиск решения):

Выполнение:

Задание1:

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

1. Локализация корней. Находим отрезки, на которых находится один корень. Для этого протабулируем функцию на отрезке [-2;2] с шагом 0,4. В нашем случае как бы не менялся диапазон и шаг функция меняет знак только на одном отрезке [-0,4;0]. За начальное приближение примем середину отрезка -0,2 (рис.9):

Рис. 9. Нахождение корней кубического уравнения.

2. Уточнение корней. Вычислим значение функции в точке, которую приняли за приближение корня. В ячейку C12 запишем формулу, вычисляющую значение функции. Затем применяем Подбор параметра для уточнения корня на этом отрезке. Выделяем ячейку С12, вызываем команду Данные/Работа с данными/Анализ «что-если»/Подбор параметра. В поле Установить в ячейке указываем ссылку на ячейку С12, в поле Значение вводим значение 0, в поле Изменяя значение ячейки указываем ссылку на ячейку В12. Excel найдет решение и поместите его в ячейку В12 (рис 10.).

Рис. 10. Результат выполнения Задания1.

Получается, что заданное уравнение имеет один действительный корень.

Задание 2:

Система содержит уравнение окружности и прямой. Пара (x,y) является решением системы уравнений в том случае, если она является решением уравнения с двумя неизвестными:

Чтобы решить это уравнение нужно протабулировать функцию двух переменных. За начальные приближения к корням уравнения выбрать пары (x,y), в которых функция ближе всего к нулю.

В столбец А поместим значения переменной x с шагом 0,2, в строку 2 поместим значения переменной y с шагом 0,2. После табулирования заметим, что наиболее близкие к нулю значения функция принимает в точках x=-0,8, y=-0,4, f(x,y)=0,27 и x=0,8, y=0, f(x,y)=1. Их и возьмем за начальные приближения. В диапазон E17:G17 поместим первую пару, в диапазон E18:G18 – вторую пару. В диапазон G17:G18 введем формулу, реализующую левую часть уравнения с двумя неизвестными.

Для нахождения первого корня выделяем ячейку G17, вызываем команду Данные/Анализ/Поиск решения, вводим необходимые данные и нажимаем выполнить (рис 11.).

Рис. 11. Параметры Поиска решений

Аналогично находим решение для второй пары чисел. Получаем решение на рис. 12.:

Рис. 12. Результат выполнения Задания2.