3.7 Сортировка, автофильтр данных
Команда Данные / Фильтр / Автофильтр
позволяет установить в интересующих поль-
зователя столбцах таблицы кнопки-стрелки Рис. 22
Рис. 23
3.8 Тестирование созданной таблицы
После создания новой таблицы, даже если она покажется пользователю чрезвычайно простой, необходимо обязательно выполнить ее тестирование. Тестирование – проверка правильности работы таблицы с различными данными из возможного диапазона значений, определяемого заданием на разработку таблицы. В случае, если в ходе тестирования Excel выдаст сообщение об ошибках, следует принять меры к их устранению (корректировка формул, проверка правильности адресов ячеек, проверка ссылок на ячейки и т.д.).
4.Примеры решения типовых задач
Как уже отмечалось ранее, при помощи Excel можно эффективно решать широкий круг разнообразных задач. Рассмотрим несколько типичных примеров.
4.1 Расчет табличных значений
На рис. 24 показан фрагмент таблицы для вычисления показательных, гиперболических и тригонометрических функций для аргумента Х, заданного в радианах. Все расчеты производятся при помощи встроенных математических функций. В диалоговом окне мастера функций введен адрес аргумента Х из ячейки А5 для вычисления значения гиперболического косинуса в ячейке Е5.
Аналогичным образом можно создать справочную базу таблиц элементарных и специальных математических функций, таблиц данных, необходимых для выполнения разнообразных инженерно-технических расчетов.
Рис. 24
4.2 Решение расчетной задачи
При помощи Excel удобно решать разнообразные технические задачи, требующие многошаговых расчетов, что позволяет существенно снизить трудоемкость и повысить производительность расчетов. В качестве примера рассмотрим решение задачи о сносе лодки, известную из школьного курса физики.
Лодка движется поперек реки. Известны скорость лодки Vл , ширина реки Hр , скорость течения реки Vр . Требуется рассчитать величину сноса лодки и траекторию ее движения.
Часто принимают допущение, что скорость реки постоянна по всей ширине. В результате решение задачи сводится к однократному вычислению гипотенузы и катета прямоугольного треугольника, а траектория движения лодки получается прямолинейной.
Вычисления с помощью таблицы позволяют учесть неодинаковость скорости течения реки на разных расстояниях от берега, для чего применим идеализированную модель для скорости течения. Считаем, что скорость течения равномерно нарастает от нуля на границе «суша-вода» до максимального значения на середине реки. Поделим ширину реки на нечетное число N участков одинаковой длины, например, 11. Добавим фиктивный участок № 12, который будем использовать для удобства записи формул.
На каждом участке для расчетов будем использовать среднюю скорость течения на этом участке. Так как считается, что скорость течения реки максимальна посередине, т.е. в рассматриваемом нами примере на шестом участке, определим приращение скорости на каждом отрезе как Vр/6. С некоторым приближением примем среднюю скорость на каждом участке поперечного сечения реки равной произведению номера участка на величину изменения скорости на этом участке. Такой расчет выполним до середины реки, т.е. до участка № 6.
С участка № 7 по участок № 11 скорость течения убывает, поэтому для второй половины реки умножаем приращение скорости не на номер участка, а на разность между числом 12 (фиктивный участок) и номером участка. Этот расчет выполняется с использованием функции ЕСЛИ.
Время преодоления лодкой одного участка To = Hр / N*Vл. Подсчитав это время, определим снос на каждом участке. Для определения общего сноса величину сноса, получаемую на данном участке, прибавляем к ранее полученному значению (формируется накапливаемая сумма). Точность принятой нами модели увеличивается с увеличением количества участков.
Формулы, по которым выполняются вычисления, приведены на рис. 25.
При составлении формул были использованы как относительные, так и абсолютные адреса ячеек. Результат решения – на рис.26. После освоения приемов построения графиков в Excel, данное решение целесообразно добавить графиком сноса лодки, построенным в координатах «величина сноса – номер участка».
Изменяя исходные данные (скорость течения реки, ширина реки, скорость лодки), пользуясь подготовленной таблицей расчетов можно получить и проанализировать различные варианты решений рассмотренной задачи.
|
А |
B |
C |
D |
E |
F |
1 |
Скорость движения лодки, км/ч |
|
|
3,0 |
||
2 |
Максимальная скорость течения реки, км/ч |
|
4,0 |
|||
3 |
Ширина реки, км |
|
|
|
5,0 |
|
4 |
Изменение скорости течения при переходе от участка к участку, км/ч |
4,0/6 |
||||
5 |
Время прохождения участка реки, ч |
|
|
|
5,0/(12*3,0) |
|
6 |
|
|
|
|
|
|
7 |
№ участка |
Скорость течения, км/ч |
Текущий снос, км |
Накоплен-ный снос, |
|
|
8 |
1 |
=ЕСЛИ(A8<=6;F$4*A8; (A$19-A8)*F$4) |
=B8*F$5 |
=C8 |
|
|
9 |
2 |
=ЕСЛИ(A9<=6;F$4*A9; (A$19-A9)*F$4) |
=B9*F$5 |
=D8+C9 |
|
|
10 |
3 |
=ЕСЛИ(A10<=6;F$4*A10; (A$19-A10)*F$4) |
=B10*F$5 |
=D9+C10 |
|
|
11 |
4 |
=ЕСЛИ(A11<=6;F$4*A11; (A$19-A11)*F$4) |
=B11*F$5 |
=D10+C11 |
|
|
12 |
5 |
=ЕСЛИ(A12<=6;F$4*A12; (A$19-A12)*F$4) |
=B12*F$5 |
=D11+C12 |
|
|
13 |
6 |
=ЕСЛИ(A13<=6;F$4*A13; (A$19-A13)*F$4) |
=B13*F$5 |
=D12+C13 |
|
|
15 |
7 |
=ЕСЛИ(A14<=6;F$4*A14; (A$19-A14)*F$4) |
=B14*F$5 |
=D13+C14 |
|
|
15 |
8 |
=ЕСЛИ(A15<=6;F$4*A15; (A$19-A15)*F$4) |
=B15*F$5 |
=D14+C15 |
|
|
16 |
9 |
=ЕСЛИ(A16<=6;F$4*A16; (A$19-A16)*F$4) |
=B16*F$5 |
=D15+C16 |
|
|
17 |
10 |
=ЕСЛИ(A17<=6;F$4*A17; (A$19-A17)*F$4) |
=B17*F$5 |
=D16+C17 |
|
|
18 |
11 |
=ЕСЛИ(A18<=6;F$4*A18; (A$19-A18)*F$4) |
=B18*F$5 |
=D17+C18 |
|
|
19 |
12 |
|
|
|
|
|
Рис. 25
Рис. 26