ОМОИ (EXCEL). Табличный процессор ms excel занятие 1

ЗАНЯТИЕ 4

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

• 
  выделить диапазон, для которого будет создан фильтр;
  
• 
  в меню Данные выбрать пункт Фильтр – Автофильтр;
  
• 
  раскрыть список столбца, по которому будет осуществляться фильтрация, щелкнув по кнопке ;
  
• 
  указать требуемые значения или выбрать строку Условие… и задать критерий выборки в диалоговом окне Пользовательский автофильтр.
  

• 
  значение, которое может выбираться из списка или содержать шаблонные символы подстановки * и ?;
  
• 
  оператор сравнения.
  

• 
  щелкнуть по кнопке и в раскрывшемся списке выбрать Все;
  
• 
  выбрать в меню Данные пункт Фильтр – Отобразить все.
  

• 
  сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора (интервал критериев). Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Если условия вводятся в одной строке для разных столбцов, то они считаются связанными логической связкой И. Если критерии отбора записываются в разных строках, то они считаются связанными логической связкой ИЛИ. Между значениями условий отбора и таблицей должна находиться как минимум одна пустая строка;
  
• 
  установить курсор внутри таблицы;
  
• 
  в меню Данные выбрать пункт Фильтр – Расширенный фильтр;
  
• 
  в диалоговом окне Расширенный фильтр указать диапазон ячеек таблицы и диапазон условий отбора.
  

1. 
   Запустите MSExcel.
   
2. 
   В файле Склад создайте и заполните следующую таблицу:
   

   Организация	Дата	Товар	Ед. изм.	Цена	Кол-во	Итого
   АО «Альянс»	1 Янв	Соль	Кг	1,5	550
   АОЗТ «Белокуриха»	1 Янв	Сахар	Кг	16	200
   АОЗТ «Белокуриха»	3 Янв	Хлеб	Бул	7	123
   Бийск.маслосырозавод	3 Июн	Сода	Пач	5,5	300
   АОЗТ «Белокуриха»	4 Янв	Сок	Бан	56	26 000
   к/з «Заря»	4 Янв	Пиломат	Метр	123	340
   АО «Альянс»	13 Янв	Лимоны	Кг	100	50
   АО «Альянс»	3 Фев	Компьют	Шт	25 000	2
   АОЗТ «Белокуриха»	12 Фев	Хлеб	Бул	7	300
   Бийск.маслосырозавод	12 Фев	Бензин	Л	45	1200
   АОЗТ «Белокуриха»	2 Мар	Хлеб	Бул	3	215
   к/з «Восток»	2 Мар	Апельсин	Кг	70	100
   к/з «Заря»	5 Мар	Апельсин	Кг	78	124
   к/з «Луч»	4 Апр	Апельсин	Кг	80	20
   к/з «Заря»	6 Апр	Мука	Кг	20	1000
   к/з «Восток»	6 Май	Сахар	Кг	16	50
   к/з «Восток»	13 Июн	Лимоны	Кг	60	34
   к/з «Восток»	13 Июн	Хлеб	Бул	7	300

3. 
   В последнем столбце произведите вычисления по формуле: Итого=Цена*Количество.
   
4. 
   Используя простую сортировку, отсортируйте данные таблицы по полю Цена.
   
5. 
   Отсортируйте наименования организаций в алфавитном порядке, внутри каждой организации наименование товара в алфавитном порядке и внутри каждого наименования товара – по возрастанию количества проданного товара. Для этого:
   

• 
  установите курсор в область таблицы;
  
• 
  выполните команду Данные – Сортировка;
  
• 
  в первом уровне сортировки выберите поле Организация, во втором — Товар, в третьем — Кол-во и нажмите OK.
  

6. 
   Скопируйте таблицу с Листа 1 на Лист 2 и назовите новый лист Автофильтр.
   
7. 
   Создайте для первого столбца автофильтр.
   
8. 
   Выберите из таблицы только те строки, которые относятся к АОЗТ «Белокуриха».
   
9. 
   Отключите автофильтр.
   
10. 
    Создайте автофильтр для всех столбцов таблицы.
    
11. 
    Используя автофильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «хлеб», т.е. осуществите выборку по двум полям.
    
12. 
    Восстановите все строки исходной таблицы.
    
13. 
    Используя автофильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16.
    
14. 
    Восстановите все строки исходной таблицы.
    
15. 
    Используя автофильтр, выведите на экран записи, содержащие колхоз «Восток» и дату покупки товара в промежутке после 2 марта до 13 июня.
    
16. 
    Скопируйте таблицу с Листа 1 на Лист 3 и дайте имя листу Расширенный фильтр.
    
17. 
    Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «Хлеб», т.е. осуществите выборку по двум полям. Для этого:
    

• 
  ниже таблицы, оставив пустые 2 строки, скопируйте строку заголовка таблицы;
  
• 
  в следующей строке сформируйте критерий отбора записей: в столбец Организация введите АОЗТ «Белокуриха», в столбец Товар — «Хлеб»;
  
• 
  установите курсор в область таблицы, из которой будет производиться выборка данных;
  
• 
  выполните команду Данные – Фильтр – Расширенный фильтр…;
  
• 
  в появившемся диалоговом окне в строке Исходный диапазон появится диапазон, соответствующий расположению анализируемой таблицы;
  
• 
  в диалоговом окне установите курсор в строку Диапазон условий, перейдите на рабочий лист и выделите диапазон условий отбора;
  
• 
  для того, чтобы новые данные печатались в другом месте, установите флажок Скопировать результат в другое место и укажите ячейку, начиная с которой будут выводиться найденные с помощью расширенного фильтра данные.
  

18. 
    Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16. При этом в диапазоне условий отбора в поле Цена укажите условие: <=16.
    
19. 
    Сохраните файл Склад и закройте его.
    
20. 
    Откройте файл Страна.xls.
    
21. 
    Создайте новую рабочую книгу.
    
22. 
    Добавьте в новую рабочую книгу еще три рабочих листа и сохраните книгу под именем Занятие 4.
    
23. 
    Скопируйте таблицу из файла Страна.xls в файл Занятие 4 на Лист1.
    
24. 
    Закройте файл Страна.xls.
    
25. 
    Скопируйте Лист1 на все остальные рабочие листы.
    
26. 
    Расположите на первом рабочем листе страны по алфавиту.
    
27. 
    На втором рабочем листе с помощью автофильтра выберите страны с площадью более 5000 тыс. км².
    
28. 
    На третьем рабочем листе с помощью автофильтра выберите страны с населением меньше 150 млн. чел.
    
29. 
    На четвертом рабочем листе с помощью автофильтра выберите страны с плотностью населения от 100 до 300 чел./км².
    
30. 
    На втором рабочем листе отмените режим фильтрации.
    
31. 
    На четвертом рабочем листе с помощью расширенного фильтра выберите страны, начинающиеся с буквы «К» и имеющие численность населения более 1 млрд. чел. Чтобы выбрать все страны, названия которых начинаются с буквы «К», нужно при создании условий отбора в поле Страна ввести К*.
    
32. 
    Сохраните файл и закройте MSExcel.
    

1. 
   В файле Телевизоры создайте на первом рабочем листе следующую таблицу и
   

1. 
   используя расширенный фильтр, отобразите на втором рабочем листе все телевизоры Sony с размером экрана 37 и Samsung – с размером экрана 51;
   
2. 
   используя автофильтр, отобразите на первом рабочем листе только те строки, которые относятся к телевизорам, начинающимся на букву S или P.
   

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

• 
  Номера уровней. Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами. Данные первого уровня не могут быть скрыты. Максимальное количество уровней – 8;
  
• 
  Линейки уровней показывают, какие группы строк или столбцов входят в каждый уровень структуры. Щелчок по линейке открытого уровня с детальными данными позволяет скрыть детали этого уровня;
  
• 
  Символ «+» маркирует закрытые уровни. Щелчок по нему позволяет показать детали соответствующего уровня;
  
• 
  С
  Номера уровней структуры
  
  Показать группу
  
  Линейка уровня
  
  Скрыть группу
  имвол «–» маркирует открытые уровни. Щелчок по нему позволяет скрыть детали соответствующего уровня.
  

1. 
   Выделить ячейку, определяющую положение итоговой таблицы.
   
2. 
   Выбрать пункт Консолидация меню Данные.
   
3. 
   Указать в окне Консолидация в списке Функция функцию, с помощью которой будет осуществляться подведение итогов.
   
4. 
   Установить курсор в строку Ссылка.
   
5. 
   Выделить первую исходную область.
   
6. 
   Щелкнуть по кнопке Добавить окна Консолидация и нажать клавишу Del.
   
7. 
   Повторить действия 4-6 для остальных исходных областей.
   
8. 
   Нажать кнопку OK.
   

• 
  таблиц MSExcel, организованных в виде списка или базы данных;
  
• 
  таблиц, полученных в результате консолидации данных;
  
• 
  других сводных таблиц;
  
• 
  внешних источников данных.
  

• 
  пункт Сводная таблица… меню Данные;
  
• 
  кнопка Мастер сводных таблиц на Панели инструментов Сводные таблицы.
  

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

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

• 
  в область Данные поместить поля, по которым при создании сводной таблицы будут производиться вычисления.
  

• 
  щелкнуть мышью по сводной таблице;
  
• 
  на Панели инструментов Сводные таблицы щелкнуть по кнопке Сводная таблица и выбрать в раскрывающемся списке команду Формулы – Вычисляемое поле;
  
• 
  щелкнуть по кнопке Добавить, а затем – по кнопке ОК;
  
• 
  расположить вычисляемое поле в требуемом месте отчета.
  

• 
  выделить любую ячейку сводной таблицы;
  
• 
  щелкнуть по кнопке Обновить данные на Панели инструментов Сводные таблицы.
  

• 
  установить курсор в любую ячейку сводной таблиц;
  
• 
  на Панели инструментов Сводные таблицы открыть список Сводная таблица и выбрать в нем команду Выделить – Таблица целиком при активизированной кнопке Разрешить выделение;
  
• 
  в меню Правка выбрать пункт Очистить – Все.
  

1. 
   Откройте файл Склад.xls.
   
2. 
   Вставьте в таблицу два новых листа – Лист4 и Лист5.
   
3. 
   Скопируйте таблицу с Листа1 на Лист4 и Лист5.
   
4. 
   Перейдите на Лист4.
   
5. 
   Осуществите автоматическое подведение итогов в таблице по столбцу Итого для каждой организации. Для этого:
   

• 
  отсортируйте данные в столбце Организации таблицы по алфавиту;
  
• 
  выделите какую-нибудь ячейку внутри таблицы;
  
• 
  выберите пункт Итоги… меню Данные;
  
• 
  в раскрывающемся списке При каждом изменении в выберите Организация;
  
• 
  из списка Операция выберите Сумма;
  
• 
  в списке Добавить итоги установите флажок рядом с полем Итоги;
  
• 
  установите флажки Заменить текущие итоги и Итоги под данными;
  
• 
  нажмите кнопку ОК.
  

6. 
   Используя символы структуры, приведите таблицу к следующему виду:
   

7. 
   Перейдите на Лист5.
   
8. 
   Осуществите автоматическое подведение итогов в таблице по столбцу Количество для каждого товара.
   
9. 
   Сохраните и закройте текущий файл.
   
10. 
    Создайте новую рабочую книгу и дайте ей имя Хлеб.
    
11. 
    Создайте в рабочей книге Хлеб следующую таблицу:
    

    Реализация хлебобулочных изделий
    Наименование	Кол-во	Цена	Сумма
    Булочная №1
    Городской	50	9	450
    Ржаной	75	7	525
    Лаваш	20	10	200
    Итого			1175
    Булочная №2
    Городской	120	9	1080
    Ржаной	100	7	700
    Лаваш	20	10	200
    Кулич	120	15	1800
    Итого			3780
    Чайная "Золотой самовар"
    Выпечка	100	5	500
    Ржаной	5	7	35
    Лаваш	50	10	500
    Итого			1035
    Всего			5990

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

• 
  выделите ячейку, определяющую положение итоговой таблицы, например, А22;
  
• 
  выберите пункт Консолидация… меню Данные;
  
• 
  в окне Консолидация в списке Функция укажите функцию Сумма;
  
• 
  установите курсор в строку Ссылка;
  
• 
  выделите первую исходную область A4:D7;
  
• 
  щелкните в окне Консолидация кнопку Добавить и нажмите клавишу Del;
  
• 
  повторите предыдущие три действия для диапазонов A9:D13, A15:D18;
  
• 
  установите флажок Использовать в качестве имен значения левого столбца и нажмите ОК.
  

13. 
    Осуществите консолидацию данных исходной таблицы для вычисления минимального объема продаж хлебобулочных изделий.
    
14. 
    Сохраните и закройте текущий документ.
    
15. 
    Создайте в файле Сводная таблица следующую таблицу:
    

    Точка	Наименование	Кол-во	Цена	Сумма
    Булочная №1	Городской	50	9	450
    Булочная №1	Ржаной	75	7	525
    Булочная №1	Лаваш	20	10	200
    Булочная №2	Городской	120	9	1080
    Булочная №2	Ржаной	100	7	700
    Булочная №2	Лаваш	20	10	200
    Булочная №2	Кулич	120	15	1800
    Чайная	Выпечка	100	5	500
    Чайная	Ржаной	5	7	35
    Чайная	Лаваш	50	10	500

16. 
    На основании имеющихся данных постройте сводную таблицу. Для этого:
    

• 
  вызовите Мастер сводных таблиц;
  
• 
  на первом шаге мастера установите флажки Создать таблицу на основе данных, находящихся в списке или базе данных MicrosoftExcel и Вид создаваемого отчета сводная таблица;
  
• 
  на втором шаге мастера укажите диапазон $A$1:$E$11;
  
• 
  на третьем шаге установите флажок Поместить таблицу в новый лист, создайте макет будущей таблицы (кнопка Макет…): поместите мышью кнопку Точка в область Страница, кнопку Наименование – в область Строка, кнопку Цена – в область Столбец, кнопку Сумма – в область Данные, нажмите ОК, а затем кнопку Готово.
  

17. 
    Используя фильтры сводной таблицы (кнопки ), выведите на экран данные по ржаному и городскому хлебу Булочной №1.
    
18. 
    Перетащите кнопку Точка в область Строка. Таблица приняла вид:
    

19. 
    Сохраните текущий документ и закройте MSExcel.
    

1. 
   Для данной таблицы осуществите автоматическое подведение итогов по столбцу Количество для каждого наименования.
   

   Точка	Наименование	Кол-во	Цена	Сумма
   Булочная №1	Городской	50	9	450
   Булочная №1	Ржаной	75	7	525
   Булочная №1	Лаваш	20	10	200
   Булочная №2	Городской	120	9	1080
   Булочная №2	Ржаной	100	7	700
   Булочная №2	Лаваш	20	10	200
   Булочная №2	Кулич	120	15	1800
   Чайная	Выпечка	100	5	500
   Чайная	Ржаной	5	7	35
   Чайная	Лаваш	50	10	500

   1   2   3   4   5   6   7