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

Ссылки бывают абсолютные и относительные. По умолчанию MS Excel создает относительные ссылки.

При копировании или перемещении формулы с относительными ссылками MS Excel изменяет ссылки на ячейки в соответствии с новым расположением формулы.

Если ссылка на ячейку не должна меняться при копировании и перемещении, то создаются абсолютные ссылки на ячейки. Для того, чтобы создать такую ссылку, достаточно перед именем строки и столбца поставить знак $. Например, $С$7 – это абсолютная ссылка на ячейку С7.

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

• 
  Абсолютная ссылка на строку. В этом случае знак $ размещается только перед номером строки. Например, В$3 – это абсолютная ссылка на третью строку.
  
• 
  Абсолютная ссылка на столбец. В этом случае знак $ размещается только перед именем столбца. Например,  $В3 - это абсолютная ссылка на столбец В.
  

Замечание. Клавиша F4 позволяет ускорить ввод абсолютной ссылки в формулу. Выделите в формуле ссылку на ячейку или диапазон, а затем нажмите клавишу F4. Знаки $ будут добавлены в формулу перед номером строки и именем столбца автоматически. Повторное нажатие F4 уберет знак $ перед именем столбца и обеспечит абсолютную ссылку только на строку. Если нажать на F4 третий раз, то знак $ останется только перед именем столбца.

Можно ссылаться на ячейки, находящиеся на других листах рабочей книги, в другой рабочей книге, или даже на данные другого приложения. Ссылки на ячейки других рабочих книг называются внешними. Ссылки на данные в других приложениях называются удаленными.

Excel выводит в ячейку сообщение об ошибке, если формула в этой ячейке не может быть правильно вычислена. Ошибки, которые могут быть возвращены формулами рабочего листа, даны в приложении 4.

---

Задание 2 «Построение диаграмм и графиков функций»

1. Создание книги

1. 
   Откройте новую книгу MS Excel.
   
   1. 
      Проверьте, что установлены на вкладке ВИД:режим просмотра книги – ОБЫЧНЫЙ;
      
   2. 
      масштаб – 100%
      

или в правом нижнем углу окна Excel- Сохраните эту книгу под именем ОПЛАТА ПОСТАВОК в папку МОИ ДОКУМЕНТЫ – кнопка OFFICE/СОХРАНИТЬ КАК.

2. Формирование списков

1. 
   Переименуйте Лист1¹ в КЛИЕНТЫ, Лист2 в ДОГОВОР – контекстное меню для соответствующего ярлыка листа/ПЕРЕИМЕНОВАТЬ.
   
2. 
   На листе КЛИЕНТЫ создайте список клиентов, начиная от ячейки А1² (Рис. 1). Для полного отображения информации в ячейках увеличивайте ширину колонок (Рис. 2).
   

Если после ввода значения в ячейку в ней отобразились #####, значит надо увеличить ширину этой колонки.


Рис. 1



Рис. 2

3. 
   На листе ДОГОВОР создайте список договоров, начиная от ячейки А1 (Рис. 5). При формировании шапки таблицы сделайте по горизонтали выравнивание по центру, по вертикали – выравнивание по верхнему краю, переносить по словам:
   
   1. 
      для блока ячеек A1:D1 воспользуйтесь соответствующими кнопками на вкладке ГЛАВНАЯ/группа ВЫРАВНИВАНИЕ или установите в диалоговом окне ФОРМАТ ЯЧЕЕК на вкладке ВЫРАВНИВАНИЕ (Рис. 3);
      

Рис. 3

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

---

Рис. 4

3. 
   теперь можно сформировать проверку данных - на листе ДОГОВОР для блока ячеек С2:С4 (применение списка) активизируйте кнопку ПРОВЕРКА ДАННЫХ на вкладке ДАННЫЕ/группа РАБОТА С ДАННЫМИ. Установите в окне ПРОВЕРКА ВВОДИМЫХ ЗНАЧЕНИЙ тип данных СПИСОК, источник (нажмите клавишу F3 – вставка имени) НАЗВАНИЕ_ФИРМЫ. В этом же окне удаление списка для блока;
   
4. 
   воспользуйтесь списком клиентов при вводе данных в блок ячеек С2:С4 на листе ДОГОВОР.
   
1. 
   создайте примечание для ячейки D3 на листе ДОГОВОР, в котором укажите обоснование большого срока оплаты – вкладка РЕЦЕНЗИРОВАНИЕ/группа ПРИМЕЧАНИЯ (все работы с примечаниями). Текст примечания – НОВЫЙ КЛИЕНТ (Рис. 5);
   

Рис. 5

4. 
   Скопируйте форматирование с блока ячеек A1:D4 листа ДОГОВОР на блок ячеек A1:С4 листа КЛИЕНТЫ:
   
   1. 
      для блока ячеек A1:D1 листа ДОГОВОР активизируйте кнопку ФОРМАТ ПО ОБРАЗЦУ - вкладка ГЛАВНАЯ/группа буфера обмена;
      
   2. 
      выделите блок ячеек A1:С4 листа КЛИЕНТЫ. Результат - Рис. 6.
      

Удаление форматирования – вкладка ГЛАВНАЯ/группа РЕДАКТИРОВАНИЕ/список ОЧИСТИТЬ/ОЧИСТИТЬ ФОРМАТЫ.



Рис. 6

5. 
   Сохраните сделанные в книге изменения – кнопка OFFICE/СОХРАНИТЬ или кнопка СОХРАНИТЬ на панели быстрого доступа.
   

Периодически по умолчанию работает автосохранение - кнопка OFFICE/кнопка ПАРАМЕТРЫ EXCEL/группа СОХРАНЕНИЕ/группа СОХРАНЕНИЕ КНИГ.

3. Формирование учетных документов

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

   Рис. 7	Рис. 8

3. 
   Введите функцию суммирования над данными блока ячеек С2:С10 в ячейку С11 на листе № 1:
   
   1. 
      для ячейки С11 активизируйте кнопку АВТОСУММА – вкладка ФОРМУЛЫ/группа БИБЛИОТЕКА ФУНКЦИЙ;
      
   2. 
      выделите блок ячеек С2:С10, нажмите клавишу ENTER. Для активной ячейки С11 в строке формул отобразится =СУММ(С2:С10);
      
4. 
   Введите функцию ЕСЛИ в ячейку В11 (Рис. 9) (если нет общей суммы оплаты в ячейке С11, то в текущей ячейке должно быть пусто, в противном случае в текущей ячейке должна быть максимальная дата из блока ячеек В2:В10) – вкладка ФОРМУЛЫ/группа БИБЛИОТЕКА ФУНКЦИЙ/список ЛОГИЧЕСКИЕ или кнопка ВСТАВИТЬ ФУНКЦИЮ/категория ЛОГИЧЕСКИЕ.
   

---

Для вставки в поле ЗНАЧЕНИЕ_ЕСЛИ_ЛОЖЬ другой функции МАКС (Рис. 10) (используйте список функций рядом со строкой формул (Рис. 11).

В результате для активной ячейки В11 в строке формул отразится =ЕСЛИ(С11=0;””;МАКС(В2:В10)).

Для редактирования функции в активной ячейке используйте кнопку ВСТАВИТЬ ФУНКЦИЮ ( ) рядом со строкой формул. Для редактирования вложенной функции установите в нее курсор в строке формул и используйте кнопку ВСТАВИТЬ ФУНКЦИЮ.



Рис. 9



Рис. 10



Рис. 11

5. 
   Сделайте одинаковое наполнение листов №1, 2, 3, 4, 5. На листе № 1 выделите блок ячеек А1:С11 и скопируйте его в буфер обмена (вкладка ГЛАВНАЯ/группа буфера обмена), перейдите последовательно на листы № 2, 3, 4, 5 и вставьте скопированное начиная от ячейки А1, т.е. указывая только эту ячейку для вставки.
   
6. 
   Введите данные в таблицы листов №1, 2, 3, 5 (Рис. 12). При этом удобно пользоваться маркером заполнения (+ в правом нижнем углу активной ячейки) и клавишей CTRL, затем увеличивать выделение блока при нажатой левой кнопке мыши.
   

Рис. 12

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

7. 
   Сохраните сделанные в книге изменения.
   

4. Анализ оплаты поставок

1. 
   Вставьте новый последний лист и назовите его КОНТРОЛЬ в книге ОПЛАТА ПОСТАВОК.
   
2. 
   Сформируйте шапку таблицы (Рис. 13) на листе КОНТРОЛЬ, начиная от ячейки А1.
   

---

Рис. 13

3. 
   Для правильности ввода номеров договоров (т.е. таких же, как на листе ДОГОВОР) в блок ячеек А2:А6 на листе КОНТРОЛЬ воспользуйтесь проверкой данных.
   
4. 
   Введите в блок ячеек А2:А6 на листе КОНТРОЛЬ с помощью списка следующие договора: 01, 02, 01, 03, 02.
   
5. 
   В блоке ячеек В2:В6 на листе КОНТРОЛЬ нужно указать соответствующие номерам договоров названия фирм, что целесообразно сделать с помощью функции ВПР (Рис. 14).
   

Рис. 14

Перед применением функции ВПР необходимо:

1. 
   отсортировать таблицу на листе ДОГОВОР по возрастанию номеров договоров;
   
2. 
   присвоить таблице на листе ДОГОВОР (блок ячеек A1:D4) имя ДОГОВОРЫ;
   
3. 
   для ячейки В2 на листе КОНТРОЛЬ вызвать функцию ВПР (вкладка ФОРМУЛЫ/группа БИБЛИОТЕКА ФУНКЦИЙ/список ССЫЛКИ И МАССИВЫ).
   

Для заполнения ТАБЛИЦАнажмите клавишу F3 и выберите из списка таблицу ДОГОВОРЫ. При заполнении ИНТЕРВАЛЬНЫЙ_ПРОСМОТР надо учитывать диапазон просмотра. Если просматривать надо последовательно каждое значение, то следует ввести ноль.

Результат: =ВПР(А2;ДОГОВОРЫ;3) (Рис. 15);

4. 
   скопируйте полученную функцию в ячейки блока В3:В6 на листе КОНТРОЛЬ с помощью маркера заполнения.
   

Рис. 15

6. 
   Аналогично заполните блок ячеек С2:С6 на листе КОНТРОЛЬ с помощью функции ВПР и маркера заполнения.
   
7. 
   Продолжите заполнение таблицы данными (Рис. 16).
   

Рис. 16

8. 
   Даты последних оплат (блок ячеек G2:G6 на листе КОНТРОЛЬ) должны соответствовать максимальным датам по каждой ТТН (ячейки В11 на соответствующих листах по ТТН №1, 2, 3, 4, 5). Для этого следует:
   
   1. 
      в активную ячейку G2 на листе КОНТРОЛЬ ввести = ;
      
   2. 
      перейти на лист №1 (т.к. в ячейке D2 указана ТТН №1) и сделать активной ячейку В11, нажать на клавишу ENTER.
      

Т.о. в ячейку G2 на листе КОНТРОЛЬ будет введена ссылка на ячейку В11 листа №1 (=’1’!В11). Аналогично заполните весь блок ячеек G2:G6 на листе КОНТРОЛЬ.

9. 
   Аналогично заполните следующую графу СУММА ОПЛАТЫ (В РУБ.), ссылаясь на ячейки С11 листов №1, 2, 3, 4, 5.
   
10. 
    Долг по оплате рассчитывается как разность между суммой отгрузки и суммой оплаты:
    

в ячейку I2 на листе КОНТРОЛЬ введите =F2-H2 , ссылаясь ячейки мышью.

---