Файл: По результатам.doc

11. 
    Дни задержки (блок ячеек J2:J6 на листе КОНТРОЛЬ) нужно рассчитать по следующему алгоритму: если дата последней оплаты отсутствует, то в графе ДНЕЙ ЗАДЕРЖКИ будет надпись НЕТ ОПЛАТЫ, в противном случае – разность между датой последней оплаты, датой отгрузки и сроком оплаты по договору. Т.е. например, для ячейки J2 с помощью функции ЕСЛИ будет указано: =ЕСЛИ(G2=””;”нет оплаты”;G2-E2-C2).
    
12. 
    Для автоматического выделения долга цветом проделайте в блоке ячеек I2:I6 на листе КОНТРОЛЬ условное форматирование – вкладка ГЛАВНАЯ/группа СТИЛИ/список УСЛОВНОЕ ФОРМАТИРОВАНИЕ/ПРАВИЛА ВЫДЕЛЕНИЯ ТОЧЕК/БОЛЬШЕ…, указать больше 0 и заливку.
    

Удаление условного форматирования для блока или листа – аналогично в списке условного форматирования команда УДАЛИТЬ ПРАВИЛА.

13. 
    В ячейку L1 на листе КОНТРОЛЬ введите значение 40,50 €. В блоке ячеек К2:К6 рассчитаем сумму оплаты (в у.е.) как деление построчно суммы оплаты (в руб.) на значение ячейки L1, например =H2/L1 для ячейки К2. При использовании маркера заполнения для блока ячеек К2:К6 в большинстве ячеек отобразится #ДЕЛ/0!, т.к. изменилась адресация в формуле – ссылка на ячейку L1. Для корректности ссылок при копировании/переносе формул вместо относительной адресации требуется использовать абсолютную адресацию ($), например =H2/L$1 или =H2/$L$1 для ячейки К2. Знак $ устанавливается с помощью последовательного нажатия на клавишу F4 при курсоре в соответствующем адресе ячейки в строке формул.
    
14. 
    Рассчитайте снова сумму оплаты в у.е., применяя имя ячейки L1. Аналогичное п. 13 действие можно сделать через именование ячеек, т.е. ячейке L1 присвоить имя (например, КУРС_ВАЛЮТЫ) и в формуле использовать ссылку на это имя (например, =H2/КУРС_ВАЛЮТЫ). Это удобно пользователю для понимания действия формул.
    
15. 
    Подсчитайте общие суммы отгрузки, оплаты в руб. и у.е., долга в соответствующих ячейках строки 7 на листе КОНТРОЛЬ.
    
16. 
    Отформатируйте блок ячеек А7:К7 на листе КОНТРОЛЬ с помощью готового стиля АКЦЕНТ – вкладка ГЛАВНАЯ/группа СТИЛИ/список СТИЛИ ЯЧЕЕК. При необходимости проведите форматирование и в других ячейках, чтобы получить конечный результат как на Рис. 17.
    

Рис. 17

17. 
    Закрепите верхнюю строку с шапкой таблицы, т.е. она будет постоянно видна на экране во время вертикальной прокрутки, что удобно использовать для больших таблиц – вкладка ВИД/группа ОКНО/список ЗАКРЕПИТЬ ОБЛАСТИ/ЗАКРЕПИТЬ ВЕРХНЮЮ СТРОКУ.
    

---

Удаление закрепления – аналогично в списке ЗАКРЕПИТЬ ОБЛАСТИ команда СНЯТЬ ЗАКРЕПЛЕНИЕ ОБЛАСТЕЙ.

18. 
    Скопируйте лист КОНТРОЛЬ (с помощью контекстного меню для ярлыка листа КОНТРОЛЬ, команда ПЕРЕМЕСТИТЬ/СКОПИРОВАТЬ – Рис. 18), вставьте три его копии, переименуйте в АВТОФИЛЬТР, РАСШ ФИЛЬТР, ИТОГИ.
    

Рис. 18

---

Задание 3 «Электронная таблица как база данных. Сортировка и фильтрация данных»

При вводе новых данных формулы, ссылающиеся на них, по умолчанию автоматически пересчитываются – кнопка OFFICE/кнопка ПАРАМЕТРЫ EXCEL/группа ФОРМУЛЫ/ПАРАМЕТРЫ ВЫЧИСЛЕНИЙ/ВЫЧИСЛЕНИЯ В КНИГЕ.

1. 
   Предположим, что ТТН №4 оплатили двумя ПП:
   

1. 
   ПП №5 от 26.01.11 на сумму 300 руб. и ПП №6 от 27.01.11 на сумму 700 руб., т.е. полная оплата ТТН №4. Соответствующие данные введите на лист №4;
   
2. 
   произойдет пересчет формул на текущем листе №4 и на листе КОНТРОЛЬ относительно ТТН №4 и итоговых значений.
   
1. 
   Верните данные на листе №4 в первоначальное состояние – отсутствие ПП.
   

6. Автофильтр

Автофильтр применяется для отображения данных, отвечающих какому-то условию или условиям. Остальные данные будут скрыты от просмотра.

1. 
   Очистите от содержимого и форматов итоговую 7-ю строку на листе АВТОФИЛЬТР – вкладка ГЛАВНАЯ/группа РЕДАКТИРОВАНИЕ/список ОЧИСТИТЬ/команда ОЧИСТИТЬ ВСЕ.
   
2. 
   Выведите все неоплаты с помощью автофильтра:
   
   1. 
      сделайте активной любую ячейку таблицы на листе АВТОФИЛЬТР и активизируйте вкладку ДАННЫЕ/группу СОРТИРОВКА И ФИЛЬТР/кнопку ФИЛЬТР. В названии каждого столбца таблицы появится кнопка со стрелкой, с помощью которой можно управлять фильтрацией;
      
   2. 
      активизируйте кнопку со стрелкой по графе ДНЕЙ ЗАДЕРЖКИ;
      
   3. 
      в появившемся окне снимите флажок ВЫДЕЛИТЬ ВСЕ и поставьте флажок НЕТ ОПЛАТЫ.
      
3. 
   Отмените условие фильтрации – кнопка ОЧИСТИТЬ на вкладке ДАННЫЕ/группа СОРТИРОВКА И ФИЛЬТР. Для отказа от возможностей фильтрации нужно отжать кнопку ФИЛЬТР.
   
4. 
   Выведите на листе АВТОФИЛЬТР данные по суммам отгрузки от 10000 руб. до 50000 руб.:
   
   1. 
      активизируйте кнопку со стрелкой по графе СУММЫ ОТГРУЗКИ (В РУБ.);
      
   2. 
      активизируйте список ЧИСЛОВЫЕ ФИЛЬТРЫ/команда МЕЖДУ…;
      
   3. 
      настройте условие в открывшемся окне ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР, используя кнопки со списками и редактируя полученные из списков значения (Рис. 19).
      

Рис. 19

---

5. 
   Добавьте к существующему новое условие – наличие долга. Т.о. будет реализовано 2 условия – вывод сумм отгрузки от 10000 руб. до 50000 руб И наличия долга:
   
   1. 
      активизируйте кнопку со стрелкой по графе ДОЛГ;
      
   2. 
      активизируйте список ЧИСЛОВЫЕ ФИЛЬТРЫ/команда БОЛЬШЕ…;
      
   3. 
      настройте условие в открывшемся окне ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР (БОЛЬШЕ) и введите 0.
      

Задание 4 «Сводные таблицы»

Расширенный фильтр, в отличие от автофильтра, требует задания условий отбора строк в отдельном диапазоне рабочего листа или на другом листе.

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

Если к одной и той же таблице надо применить несколько условий, то диапазонам условий (как спискам) удобно присвоить имена, которые затем можно использовать вместо ссылок.

Примеры диапазонов условий:

10000'>Оклад__Возраст__>10000__>50'>Оклад	Возраст
>10000	>50

Условия в одной строке считаются соединёнными логическим оператором И, т.е. из таблицы будут отобраны строки при выполнения всех условий (оклад больше 10000 И возраст больше 50).

Расположение условий в разных строках диапазона условий соответствует логическому оператору ИЛИ. Пустая ячейка в диапазоне условий означает любые значения.

Оклад	Возраст
>10000
	>50

Указаны два условия, соединённые логическим оператором ИЛИ:

1. 
   оклад больше 10000 И любой возраст
   

ИЛИ

2. 
   возраст больше 50 И любой оклад
   

Из таблицы будут отобраны строки, удовлетворяющие хотя бы одному из всех условий ИЛИ.

---

1. 
   Выведите на листе РАСШ ФИЛЬТР данные по долгу больше или равно 2000 руб. И просроченным срокам оплаты (дни задержки больше 0):
   
   1. 
      для исключения итоговой строки из фильтрации присвойте блоку ячеек А1:К6 имя ТАБЛ_ДЛЯ_ФИЛЬРА;
      
   2. 
      создайте диапазон условий, начиная от ячейки А12 (Рис. 20). Заголовки для диапазона условий лучше копировать из таблицы;
      
   3. 
      активизируйте вкладку ДАННЫЕ/группу СОРТИРОВКА И ФИЛЬТР/кнопку ДОПОЛНИТЕЛЬНО;
      
   4. 
      укажите в окне РАСШИРЕННЫЙ ФИЛЬТР, что ФИЛЬТРОВАТЬ СПИСОК³ НА МЕСТЕ исходной таблицы, исходный диапазон - ТАБЛ_ДЛЯ_ФИЛЬТРА (с помощью клавиши F3), выделите диапазон условий – блок ячеек А12:В13 (Рис. 20 Рис. 21).
      

Рис. 20 Рис. 21

На месте исходной таблицы останутся видимыми строки, отвечающие условиям (Рис. 22). Итоговая строка будет отображаться, т.к. она не должна подвергаться фильтрации и не была указана в блоке ТАБЛ_ДЛЯ_ФИЛЬРА.



Рис. 22

2. 
   Отмените фильтрацию - кнопка ОЧИСТИТЬ на вкладке ДАННЫЕ/группа СОРТИРОВКА И ФИЛЬТР.
   
3. 
   При имеющемся условии выведите результат фильтрации на другом месте, начиная от ячейки D12. Для этого в окне РАСШИРЕННЫЙ ФИЛЬТР установите переключатель в позицию СКОПИРОВАТЬ РЕЗУЛЬТАТ В ДРУГОЕ МЕСТО, укажите в ПОМЕСТИТЬ РЕЗУЛЬТАТ В ДИАПАЗОН - ячейку D12 (с помощью мыши). Исходный диапазон и диапазон условий остаются прежними.
   
4. 
   Если необходимо вывести не все графы из исходной таблицы, то нужно дополнительно сформировать шапку таблицы-результата фильтрации. Например, при имеющемся условии выведите только № договора и название фирмы:
   
   1. 
      скопируйте блок ячеек А1:В1 и вставьте, начиная от ячейки D15;
      
   2. 
      в окне РАСШИРЕННЫЙ ФИЛЬТР укажите в ПОМЕСТИТЬ РЕЗУЛЬТАТ В ДИАПАЗОН – блок ячеек D15:Е15 (шапка таблицы-результата фильтрации). Остальные настройки остаются прежними (Рис. 23).
      

Рис. 23

5. 
   Нужно вывести на листе РАСШ ФИЛЬТР данные по долгу больше или равно 2000 руб. И просроченным срокам оплаты (дни задержки больше 0). ИЛИ данные по отсутствию суммы оплаты в руб. Результат фильтрации поместите, начиная от ячейки Е12:
   
   1. 
      очистите блок ячеек D12:N16 (результаты предыдущей фильтрации);
      
   2. 
      добавьте в диапазон условий новое условие - ИЛИ данные по отсутствию суммы оплаты в руб. (Error: Reference source not found);
      
   3. 
      заполните окно РАСШИРЕННЫЙ ФИЛЬТР (Error: Reference source not found).
      

---