Лабораторная работа5. Лабораторная работа 5 Тема Основные приемы работы с ms excel Цель работы освоить основные приемы работы в ms excel

• Название: Лабораторная работа 5 Тема Основные приемы работы с ms excel Цель работы освоить основные приемы работы в ms excel
• Дата: 14.01.2023
• Размер: 0.78 Mb.
• Имя файла: Лабораторная работа5.docx
• Тип: Лабораторная работа; #886083

Лабораторная работа №5
Тема: Основные приемы работы с MS Excel

Цель работы: освоить основные приемы работы в MS Excel

Оборудование рабочего места: Персональный компьютер, ОС Windows, Пакет ПО Microsoft Office.
Краткие теоретические сведения

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

Т.о., термин табличный процессор (электронная таблица) используется для обозначения простой в использовании компьютерной программы, предназначенной для обработки данных. Обработка включает в себя:

• 
  проведение различных вычислений с использованием мощного аппарата функций и формул (позволяет решать обыкновенные дифференциальные уравнения, уравнения в частных производных и пр.);
  
• 
  исследование влияния различных факторов на данные;
  
• 
  решение задач оптимизации, линейного программирования;
  
• 
  получение выборки данных, удовлетворяющих определенным критериям;
  
• 
  построение графиков и диаграмм;
  
• 
  статистический анализ данных;
  
• 
  работа с базами данных
  

Основное достоинство электронных таблиц заключается именно в простоте использования средств обработки данных и не требует от пользователя специальной подготовки в области программирования.

Один из наиболее популярных табличных процессоров - Microsoft Excel для Windows, используемый в работе специалистами различного профиля (экономистами, финансистами, бухгалтерами, статистиками, инженерами, аналитиками и др.)

Информационные технологии Microsoft Excel доступны пользователям различной квалификации – от конечных пользователей до профессиональных программистов. Существующие ограничения электронных таблиц Microsoft Excel (Приложение1) весьма не существенны и определяются, в большей степени, техническими параметрами компьютеров.

Книги, созданные в среде Excel представляют собой набор листов, следующих типов:

?     рабочие листы;

?     листы диаграмм;

?     листы макросов;

?     листы модулей, написанных языком Visual Basic;

?     листы диалога.

Рабочие листы или просто листы– это электронные таблицы, которые состоят из столбцов (колонок) и строк. Каждый лист имеет ярлычок с именем. Столбцы обозначаются латинскими буквами (A, B, C,…), строки – цифрами (1, 2, 3,…).

Область пересечения строки и столбца называется ячейкой. Обозначение (адрес) ячеек состоит из обозначения колонки и строки. Например, А4 или BZ15.

Одна из ячеек таблицы выделена темной прямоугольной рамкой. Это так называемый табличный курсор. Столбец, строка и ячейка, в которой в данный момент расположен курсор, называются активными. В правом нижнем углу табличного курсора расположен маленький черный квадратик – маркер заполнения, предназначенный для копирования данных или формул, содержащихся в активной ячейке. Табличный курсор можно перемещать по таблице с помощью клавиш перемещения курсора или при помощи мыши.

Любые данные, вводимые в рабочий лист, заносятся в ячейки независимо от того, являются ли данные цифрами или текстом. В ячейке могут одновременно сохраняться формулы и вычисленные по ним результаты. Формулы могут использовать значения из других клеток таблицы и Excel. Как только изменяется какое-либо значение в таблице, автоматически обновляет ответы, вычисленные по этим формулам.

Чтобы ввести информацию в таблицу, надо просто выбрать ячейку, в которой нужно вводить данные, и начать печатать. Как только информация начнет вводиться, все введенные символы станут появляться в двух местах: в активной ячейке и на панели формул. Завершив набор содержимого клетки, нужно нажать Enter. При этом курсор переходит на соседнюю клетку. Каждый раз, когда курсор будет установлен на конкретную клетку, ее содержимое появляется на панели формул. Если курсор ошибочно был установлен не на ту клетку и начался ошибочный ввод информации до завершения ввода нажать Esc.

Excel проверяет каждую введенную формулу, сможет ли она ее вычислить и пытается отнести данные к одному из типов: текст, числовое значение или формула. Формула сразу вычисляется, результат заносится в соответствующую клетку (там, где находился курсор), а саму формулу можно видеть на панели формул. Если введенная строка текст, то он выравнивается по левому краю ячейки. Если текстовая строка не помещается в ячейке, она растекается на соседние клетки, расположенные справа, при условии, что соседняя клетка чистая. Если позднее ввести информацию в соседнюю клетку, Excel отрежет длинную текстовую строку по границе клетки, хотя отрезанные символы из строки не удаляются.

Если после ввода информации в клетку была обнаружена ошибка, то для ее исправления можно воспользоваться одним из следующих способов:

-       чтобы отредактировать формулу прямо в ячейке, нужно щелкнуть по клетке дважды или поставить на нее табличный курсор и нажать F2 - курсор будет помещен в конец отображаемой строки;

чтобы отредактировать формулу в строке формул, нужно подвести указатель мыши в позицию записи на панели формул и щелкнуть кнопкой мыши. Курсор будет помещен в указанную позицию. По окончании исправлений необходимо нажать Enter.

Основным достоинством электронной таблицы Excel является наличие мощного аппарата формул и функций. Любая обработка данных в Excel осуществляется при помощи этого аппарата.

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства “=“. В эту последовательность символов могут входить

• 
  постоянные значения,
  
• 
  ссылки на ячейки,
  
• 
  имена,
  
• 
  функции или операторы.
  

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

Что делать, если, например, таблица из 1000 строк и в каждой строке нужно найти произведение чисел. Ответ: в формулах использовать адреса ячеек, в которых хранятся данные, т.е. в ормулах использовать ссылки.

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

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

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

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

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

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

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

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

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

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 , ссылаясь ячейки мышью.

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

5. Ввод новых данных

При вводе новых данных формулы, ссылающиеся на них, по умолчанию автоматически пересчитываются – кнопка 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.
      

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

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

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

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

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

Оклад	Возраст
>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).
      

Рис. 24

8. Итоги

Итоги позволяют производить вычисления над сгруппированными данными.

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

Результат – структурированная таблица (Рис. 26). Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке второго уровня структуры, вследствие чего данные третьего уровня (исходные значения) будут скрыты.

Удаление структуры и итогов – кнопка УБРАТЬ ВСЕ в диалоговом окне ПРОМЕЖУТОЧНЫЕ ИТОГИ.



Рис. 25



Рис. 26

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

Рис. 27



Рис. 28

9. Сводные таблицы

Сводная таблица является специальным типом таблицы, которая подытоживает данные из граф. При создании сводной таблицы можно задать нужные графы, организацию таблицы (ее макет) и тип выполняемых вычислений. Сводная таблица является многомерной и всегда связана с источником данных. Она предназначена только для чтения, а изменения можно вносить в исходные таблицы.

1. 
   На основе данных листа КОНТРОЛЬ на отдельном листе постройте сводную таблицу (Рис. 29).
   

Рис. 29

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

Сводная таблица будет построена в левой части окна Excel.

При необходимости удаления поля из макета используйте этот же метод перетаскивания в окне СПИСОК ПОЛЕЙ СВОДНОЙ ТАБЛИЦЫ или сделайте текущим поле в сводной таблице, вызовите для него контекстное меню и выберите команду УДАЛИТЬ (НАЗВАНИЕ ПОЛЯ).

4. 
   для создания % ЗАДОЛЖЕННОСТИ перетащите поле ДОЛГ второй раз в область ЗНАЧЕНИЯ, сделайте это поле активным в сводной таблице и выберите на вкладке ПАРАМЕТРЫ/список АКТИВНОЕ ПОЛЕ/кнопка ПАРАМЕТРЫ ПОЛЯ.
   

В диалоговом окне ПАРАМЕТРЫ ПОЛЯ ЗНАЧЕНИЙ на вкладке ДОПОЛНИТЕЛЬНЫЕ ВЫЧИСЛЕНИЯ выберите в списке вариант ДОЛЯ ОТ СУММЫ ПО СТОЛБЦУ и укажите числовой формат – ПРОЦЕНТНЫЙ, 0 десятичных знаков.

В этом же диалоговом окне введите пользовательское имя - % ЗАДОЛЖЕННОСТИ;

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

При активной ячейке в сводной таблице можно включить итоги, выбрав вкладку КОНСТРУКТОР/группа МАКЕТ/список ОБЩИЕ ИТОГИ/команда ВКЛЮЧИТЬ ТОЛЬКО ДЛЯ СТОЛБЦОВ.

5. 
   Произведите оформление сводной таблицы стилем - при активной ячейке в сводной таблице выберите вкладку КОНСТРУКТОР/группа СТИЛИ СВОДНОЙ ТАБЛИЦЫ.
   

Рис. 30

6. 
   В новом поле сводной таблицы рассчитайте суммарный долг в у.е. Курс будет фиксированным – 40,5:
   
   1. 
      при активной ячейке в сводной таблице активизируйте вкладку ПАРАМЕТРЫ/группа СЕРВИС/список ФОРМУЛЫ/команда ВЫЧИСЛЯЕМОЕ ПОЛЕ;
      
   2. 
      в диалоговом окне ВСТАВКА ВЫЧИСЛЯЕМОГО ПОЛЯ установите курсор в поле ФОРМУЛА и удалите «0»;
      
   3. 
      в списке ПОЛЯ выберите поле ДОЛГ и нажмите кнопку ДОБАВИТЬ ПОЛЕ. Это поле отобразится в поле ФОРМУЛА;
      
   4. 
      далее введите в поле ФОРМУЛА - /40,5
      

Т.о. в поле формула будет указано: = Долг/40,5

5. 
   в поле ИМЯ введите имя нового поля – ДОЛГ В У.Е.;
   
6. 
   нажмите кнопки ДОБАВИТЬ, ОК.
   

Для удаления вычисляемого поля из сводной таблицы можно использовать это же диалоговое окно ВСТАВКА ВЫЧИСЛЯЕМОГО ПОЛЯ, где в списке ИМЯ выбрать поле для удаления, нажать кнопку УДАЛИТЬ.

Аналогично редактируется вычисляемое поле.

7. 
   настройте для этого поля числовой формат, 2 десятичных знака (Рис. 31).
   

Рис. 31

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

      Рис. 32	Рис. 33

8. 
   На основе такой сводной таблицы (на экране только необходимое для отображения на диаграмме и нет итогов) можно быстро построить сводную диаграмму (Рис. 33) на текущем или отдельном листе:
   
   1. 
      при активной ячейке в сводной таблице активизируйте вкладку ПАРАМЕТРЫ/группа СЕРВИС/кнопка СВОДНАЯ ДИАГРАММА;
      
   2. 
      в окне ВСТАВКА ДИАГРАММЫ укажите вид желаемой диаграммы – ОБЪЕМНАЯ КРУГОВАЯ;
      
   3. 
      измените название диаграммы – ОТГРУЗКА В РУБ. ПО ДОГОВОРАМ.
      

Настройки диаграммы – команды в контекстном меню для диаграммы или на вкладках КОНСТРУКТОР, МАКЕТ, ФОРМАТ для активной диаграммы.

Отображение на диаграмме зависит от видимого в сводной таблице и наоборот (окно ОБЛАСТЬ ФИЛЬТРА СВОДНОЙ ДИАГРАММЫ).
Содержание отчета

1. 
   В отчете указать тему, цель работы.
   
2. 
   Описать основные этапы выполнения работы
   
3. 
   Записать выводы по проделанной работе.
   

Контрольные вопросы

1. 
   Для чего нужен табличный процессор?
   
2. 
   Что такое табличный процессор? Для чего он предназначен?
   
3. 
   Как запустить Microsoft Excel ?
   
4. 
   Где находится поле имени ? Для чего оно предназначено?
   
5. 
   Для чего используется строка формул? Где она находится?
   
6. 
   Что такое табличный курсор?
   
7. 
   Где находится маркер заполнения? Как им пользоваться?
   
8. 
   Какие данные можно вносить в ячейки?
   
9. 
   Как исправить информацию в ячейке?
   
10. 
    Каким образом производится расчет формул?
    
11. 
    Как поменять формат ячеек?
    
12. 
    Что такое сортировка и фильтр?
    
13. 
    Как изменить количество выводимых знаков после запятой?
    
14. 
    Что называется формулой в Excel ?
    
15. 
    Как ввести формулу в ячейку7
    
16. 
    Где отображается результат вычислений, а где содержимое ячейки?
    
17. 
    Что такое относительные ссылки? Привести пример использования.
    
18. 
    Что такое абсолютная ссылка на строку? Пример.
    
19. 
    Что такое абсолютная ссылка на столбец? Пример.
    

1^ Количество листов по умолчанию – кнопка OFFICE/кнопка ПАРАМЕТРЫ EXCEL/группа ОСНОВНЫЕ/группа ПРИ СОЗДАНИИ НОВЫХ КНИГ/список ЧИСЛО ЛИСТОВ.

2^ При включенном режиме СТИЛЬ ССЫЛОК R1C1 на линейках отображаются только числа. Отключение этого режима – снять флажок СТИЛЬ ССЫЛОК R1C1 (кнопка OFFICE/кнопка ПАРАМЕТРЫ EXCEL/группа ФОРМУЛЫ/группа РАБОТА С ФОРМУЛАМИ).

3^ Список – большая таблица, сформированная определенным образом:

• 
  Каждый столбец должен содержать информацию одного типа.
  
• 
  Одна или две верхние строки списка должны содержать уникальные заголовки, каждый из которых описывает содержимое расположенного ниже столбца.
  
• 
  Список не должен включать пустые строки и столбцы.
  
• 
  Лучше всего, если для списка отводится отдельный лист. Если же это невозможно, то список должен быть отделен от других данных рабочего листа, по крайней мере, одной пустой строкой и одним пустым столбцом.
  
• 
  Не следует размещать данные слева и справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка.
  

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

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

4^ Окно СПИСОК ПОЛЕЙ СВОДНОЙ ТАБЛИЦЫ вызывается активизацией кнопки СПИСОК ПОЛЕЙ на вкладке ПАРАМЕТРЫ/группа ПОКАЗАТЬ ИЛИ СКРЫТЬ.

5^ Настройка отображения в окне – кнопка со списком в правом верхнем углу этого окна.