Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа №5. Обработка таблиц в MS Excel
Цель работы: овладение навыками обработки данных в таблицах.
Цель работы: овладение навыками преобразования таблицы в список (базу данных) фильтрации данных в таблицах с помощью инструмента «Автофильтр».
Преобразование таблицы в список
Список отличается от таблицы следующими признаками:
1. Каждый столбец списка имеет свое имя. Причем имена столбцов занимают одну верхнюю строку.
2. Список отделяется от данных, расположенных на рабочем листе, со всех сторон пустыми строками и столбцами.
3. В столбце могут быть данные только одного типа.
В приведенном примере строка «ИТОГО» не входит в список, т.к. отделена от списка пустой строкой. Чтобы сделать строку скрытой, ее надо выделить, щелкнуть по ней правой кнопкой мышки и в контекстном меню выбрать команду «Скрыть»
Автофильтрация данных
Поставить курсор в любое место списка и выполнить команду «ДанныеФильтр».
В списке в строке заголовков появятся кнопки «Автофильтрации».
Фильтрация по точному значению
1. Нажать кнопку «Автофильтра» в любом столбце.
2. В открывшемся списке выбрать нужное точное значение и щелкнуть по нему левой кнопкой мыши (Например: Поставщик=Альтаир).
3. Все строки, которые не содержат «Альтаир» будут скрыты.
4. Полученный результат скопировать и поместить на свободное место.
5. Для возврата к полному списку нажать кнопку «Автофильтрации» в том столбце, по которому проводилась фильтрация и выбрать «Выделить все».
Фильтрация по шаблону
Под шаблоном будем понимать любой набор символов, которые содержатся (или не должны содержаться) в строке. Фильтрация проводится только по символьным столбцам.
1. Нажать кнопку в любом символьном столбце и выбрать «Текстовые фильтры»
2. Выбрать в верхней строке нужный оператор сравнения и установить требуемое значение.
Выполнить два вида фильтрации по шаблону:
- по начальным символам (оператор сравнения «начинается с»);
- по подстроке (оператор сравнения «содержит»).
Фильтрация по диапазону значений
Фильтрация выполняется так же, как и по шаблонам, но отличается следующим:
- фильтрация выполняется по числовому столбцу;
- нужно установить два оператора сравнения («больше или равно» и «меньше или равно»), связанных логической функцией «И».
В данном случае фильтрация проводится по диапазону 100<=Цена<=200.
Цель задания:
1. Выполнить сортировки по одному столбцу: символьному и числовому.
2. Выполнить сортировку по двум столбцам.
3. Создать пользовательский список.
4. Выполнить сортировку по пользовательскому списку.
Сортировка
Поставить курсор в таблицу. Выполнить команду «ДанныеСортировка»
В окне «Сортировка» установить флажок «Мои данные содержат заголовки».
В списке «Сортировать по» выбрать требуемый столбец, в списке «Сортировка» - «значения», установить нужный порядок сортировки.
Перед сортировкой по двум столбцам для наглядности добавить в таблицу дополнительные строки с повторяющимися символьными данными и отличными – числовыми.
Сортировка по двум столбцам выполняется аналогично сортировке по одному столбцу, но дополнительно необходимо нажать кнопку «Добавить уровень».
Сортировка по пользовательскому списку.
Цель работы: овладение навыками подведения промежуточных итогов.
Подвести итоги по суммарным и максимальным показателям.
Подведение итогов
Перед подведением итогов обязательно выполнить сортировку по столбцу, по которому будут подводиться итоги.
1. Поставить курсор в таблицу.
2. Выполнить команду «ДанныеПромежуточные итоги».
3. В окне «Промежуточные итоги» в списке «При каждом изменении в:» выбрать столбец, по которому будут подводиться итоги.
4. В списке «Операция:» выбрать операцию, которая будет выполняться при подведении итогов (Сумма или Максимум).
После выполнения подведения итогов в левой части окна EXCEL появляется дополнительное поле с кнопками, с помощью которых можно скрывать и показывать исходные данные.
Консолидация таблиц позволяет собирать данные из нескольких исходных таблиц, которые могут располагаться в той же самой книге, где находится итоговый лист, или в других книгах. В несвязанной консолидации при изменении данных в исходных таблицах в итоговой таблице данные не изменяются.
Консолидация таблиц
1. На свободном месте листа поместить заголовки столбцов итоговой таблицы. Заголовки столбцов итоговой таблицы должны точно соответствовать заголовкам столбцов исходных таблиц, данные из которых будут помещены в итоговую таблицу.
2. Выделить заголовки итоговой таблицы и выполнить команду «ДанныеКонсолидация».
3. В окне «Консолидация» в списке «Функция» выбрать функцию для обработки данных.
4. В настройке «Использовать в качестве имен» обязательно поставить флажки в «подписи верхней строки» и значения левого столбца.
5. Поставить курсор в поле «Ссылка» и выделить область таблицы для консолидации. Выделение области таблицы начинать со столбца, имя которого совпадает с именем левого столбца итоговой таблицы. После выделение области нажать кнопку «Добавить». Аналогичные действия выполнить с остальными таблицами.
Вариант 1
Вариант 2
Вариант 3
Вариант 4
Вариант 5
Вариант 6
Вариант 7
Вариант 8
Вариант 9
Вариант 10
Вариант 11
Вариант 12
Вариант 13
Вариант 14
Вариант 15
В таблицах рекомендуется использовать следующие функции
(адреса ячеек зависят от расположения таблицы).
|
Функция |
|
Поиск значения в таблице: =Е9-Е9*ВПР(А9;$С$4:$D$6;2;ЛОЖЬ) |
|
Формирование текста из нескольких изменяющихся частей: =СЦЕПИТЬ(“Цена на “; ДЕНЬ($D$3);”.”;МЕСЯЦ($D$3)”.”ГОД($D$3)) Вычисление сложного процента: =БЗ($D$2;ОТБР(($D$4-$D$3)/30);0;-Е6) |
|
Использование функций ЕСЛИ =D8*C8+ЕСЛИ(D8>$F$5;(D8-$F$5)*C8*$C$5;0) |
|
Вложенные функции ЕСЛИ и составное условие с функцией И: =ЕСЛИ(И(B9>=$A$4;B9<=$B$4);D9*$C$4;ЕСЛИ(И(B9>=$A$5;B9<=$B$5);D9*$C$5;D9*$C$6)) |
|
Вложенные функции ЕСЛИ с выбором текстовых констант: =ЕСЛИ(G16=$C$4;"выполнен";ЕСЛИ(G16>C4;"перевыполнен на";"не выполнен на")) |
|
Вычисление количества рабочих дней за любой период времени: =ЧИСТРАБ(С2;СЗ) |
|
Получение целого числа отбрасыванием дробной части: ОТБР(Всего шт./В упаковке шт.) |
|
Определение количества периодов уценки: =ЦЕЛОЕ((Сегодня-Дата пост)/Через каждые); Вычисление уценки со сложным процентом (процент на процент): =БЗ(-Величина уценки; Число периодов уценки;0;-Цена руб.) |
|
Текущая дата: =СЕГОДНЯ() Поиск значения в таблице: =ГПР(D9;$B$3:$D$4;2) Вычисление сложного процента: =БЗ(Е9;1;0;-В9)-В9 |
|
Вычисление рейтинга: =СУММПРОИЗВ($С$3:$Е$3;С6:Е6) Вычисление итоговой оценки: =ОКРУГЛ(Е6/100;0) Нахождение максимального рейтинга: =MAKC(F6:F16) Поиск фамилии с максимальным рейтингом: =ИНДЕКС(A7:A17;ПОИСКПОЗ(F19;F7:F17;0)) |
|
Функция ЕСЛИ с текстовой константой в условии: =ЕСЛИ(F8="Есть";E8*$C$5;E8) |
|
Определение количества хабов на 16 портов: =ОКРУГЛВВЕРХ(Рабочих станций/16;0) |
|
Функция ЕСЛИ с условием по диапазону значений: =ЕСЛИ(И(E7>=$B$4;E7<=$D$4);F7*$F$4;0) |
|
Текущая дата: =СЕГОДНЯ() |
|
Поиск названия изделия, приносящего максимальные убытки: =ЕСЛИ(МИН(I5:I11)<0;ИНДЕКС(B5:B11;ПОИСКПОЗ(МИН(I5:I11);I5:I11;0));"нет") Величина максимальных убытков:=ABS(МИН(I5:I11)) |
|
Количество строк ЧСТРОК(B8:B15) |
|
РАНГ() – ранг числа из диапазона |
PAGE 15