Пусть требуется выполнить расчет платы за квартиру /1/. Положим она состоит из оплаты за коммунальные услуги (по 2 руб. за квадратный метр площади) и оплаты за газоснабжение (по 3 руб. на каждого проживающего в квартире человека).
Здесь реализован очевидный механизм расчета
<квартплата>=<площадь>* <тариф за коммун. услуги>+<человек>*<тариф за газ>.
Нужно ввести формулу для верхней ячейки D3=B3*2+C3*3, а затем просто скопировать (воспользовавшись маркером заполнения клетки) первую формулуво все нижележащие ячейки. В следующей снизу клетке она превратится в D4=В4*2+С4*3, затем вD5=B5*2+C5*3 и т.д. Здесь проявилось чрезвычайно полезное свойство копируемых ссылок - адаптируемость адресных ссылок под новое положение.
| A | B | C | D | D | ||
| РАСЧЕТ КВАРТПЛАТЫ | ||||||
| Квартира | Площадь | Человек | Кв.плата | Кв.плата | ||
| №1 | =В3*2+С3*3 | |||||
| №2 | =В4*2+С4*3 | |||||
| №3 | =В5*2+С5*3 | |||||
| №4 | =В6*2+С6*3 | |||||
| ВСЕГО | =СУММ(D3:D6) |
Очевидным недостатком нашего решения является его зависимость от изменения тарифов - при их пересмотре придется изменять множество формул. В виду этого, в электронных таблицах все нормативные данные выносят в отдельные области листа (или даже на отдельные листы), обычно, в верхней его части. В нашем случае такими данными являются цены на коммунальное услуги и газ. Разместим их в клетках В2 и D2, а в формулах вместо констант (2 руб. и 3 руб.) укажем ссылки на эти ячейки. Как и ранее, введем формулу только для первой (верхней) квартиры но при этом будем использовать абсолютную адресацию ведением знака $ перед номером строки (D5=B5*B$2+C5*D$2). Копирование такой формулы не повлечет изменения цифры 2 в адресах В$2 и D$
| A | B | C | D | D | ||
| Тарифы оплаты | ||||||
| комм/ус: | 2,0р/метр | газ: | 3,0р/чел | 3,0р/чел | ||
| РАСЧЕТ КВАРТПЛАТЫ | ||||||
| Квартира | Площадь | Человек | Кв.плата | Кв.плата | ||
| №1 | 172,0р | =В5*В$2+С5*D$3 | ||||
| №2 | 129,0р | =В6*В$2+С6*D$3 | ||||
| №3 | 235,0р | =В7*В$2+С7*D$3 | ||||
| №4 | 264,0р | =В8*В$2+С8*D$3 | ||||
| ВСЕГО | 800,0р | =СУММ(D5:D8) |
Для выполнения сортировки например, по колонке «ПЛОЩАДЬ» по убыванию, необходимо выделить таблицу с данными, включая названия колонок, войти в меню ДАННЫЕ и выбрать пункт СОРТИРОВКА. В открывшимся диалоговом окне указать название колонки «Площадь» и направление сортировки «по убыванию», см. рис. 23.
Рис. 23
Выборка данных в режиме «АВТОФИЛЬТР» выполняется следующим образом. Первоначально выделяется таблица с данными, включая названия колонок. Затем активизируется меню ДАННЫЕ и выбирается пункт ФИЛЬТР, режим АВТОФИЛЬТР. Для выбранной колонки, например ЧЕЛОВЕК, активизируется список условий выборки (рис.24) и выбирается «(Условие…)». В открывшимся диалоговом окне задаются параметры выборки. Например, если необходимо выбрать записи с количеством людей более 3 и менее 7, то в диалоговом окне необходимо задать данные отображенные на рис. 25
Рис. 24
Рис. 25
Для фильтрации данных в режиме «РАСШИРЕННЫЙ ФИЛЬТР» первоначально составляется таблица диапазона условий, в которой задаются данные условий выборки, причем если используется критерий условий, как в задании контрольной, то заголовки таблицы диапазона условий должны повторять заголовки исходной таблицы. Например, если необходимо выбрать записи, удовлетворяющие условию «Площадь»<100 ИЛИ «Человек»>7, то таблица диапазона условий будет выглядеть следующим образом (рис.26). После этого активизируется меню ДАННЫЕ и выбирается пункт ФИЛЬТР, режим РАСШИРЕННЫЙ ФИЛЬТР. Далее в диалоговом окне задаются диапазон исходной таблицы с данными, включая заголовки столбцов и диапазон таблицы диапазона условий. Кроме того, может быть указан диапазон результатов выборки (рис. 27). Результаты расчетов приведены на рис.28
| Площадь | Человек |
| <100 | |
| >7 |
Рис. 26 Рис. 27
Рис. 28
