4.1 Фильтрация
Excel оснащен весьма полезной при работе с большими таблицами: фильтрацией, т.е. возможностью видеть не всю таблицу, а только ту часть ее, которая нам сейчас нужна.
Создадим таблицу следующего вида:
В каждую ячейку введем данные.
В ячейку С10 введем формулу =сумм (С2:С9), таким же образом введем формулы в ячейки D10:H10.
Левой кнопкой мыши выделим все ячейки, с помощью правой кнопки мыши выберем Формат ячеек.
Во вкладке Граница выбираем внешние и внутренние.
Во вкладке Вид выберем нужный цвет.
Во вкладке Шрифт выберем начертание – полужирный, размер 10.
Выделим все строки. На панели форматирования выберем цвет шрифта.
Выделяем мышкой первые два столбца таблицы, выбираем в меню Данные команду Фильтр.
А в ней - строку Автофильтр.
Тут же в каждой ячейке первой строки появится по кнопочке со стрелкой, обозначающей наличие какого-то списка.
Щелкнем по стрелочке в столбце Город. Появится список всех введенных нами городов. Выберем строку “Москва”, будут показаны только результаты работы московских агентов.
Добавим примечания в 10 строку, в ячейках C10:H10.
Подводим курсор мыши в ячейку C10, выберем команду Вставка/Примечание и введем примечания так как указано ниже:
Создадим диаграмму для этой таблицы:
Выберем команду Вставка/Диаграмма.
Во вкладке стандартные выберем тип – Гистограмма.
Вид- первую диаграмму.
Нажимаем кнопку Далее.
Во вкладке диапазон данных выберем ряды в столбцах.
Нажимаем Далее.
Во вкладке Заголовки введем название диаграммы (количество проданных деталей), нажмите готово.
Выделите всю таблицу, щелкайте мышью по тому месту диаграммы, где нужно изменить.
Во вкладке Вид нажмите кнопку Способы заливки.
Во вкладке Градиентная установите флажок два цвета, выберите нужные цвета.
Тип штриховки - от центра, выберите нужный вариант, и нажмите Ok.
Диаграмму можно растянуть, установить мышь в угол области диаграммы, нажать левой кнопкой, удерживая его растянуть таким образом чтобы его правая и левая граница совпадала с размером таблицы.
Если в таблице нажать кнопочку со стрелкой (в ячейке город), и выбрать только какой-нибудь один из городов, то в диаграмме будут результаты только тех городов, которые мы выбрали. Таким же образом можно выбрать только результаты покупок каких-нибудь отдельных клиентов.
Например, выберем только Московских клиентов, получим диаграмму следующего вида:
Сохранение документа:
После создания таблицы и диаграммы, выполните команду файл/сохранить как.
В окне сохранение документа введем имя файла Книга1.
Тип файла Книга Microsoft excel.
И выберем папку, куда хотим поместить этот документ.
4.2 Программа для проверки знания умножения
Создадим таблицу следующего вида:
Выделим ячейки от A1:E12.
Правой кнопкой мыши выбираем Формат ячеек.
Во вкладке Граница выбираем внешние и внутренние, тип линии и цвет.
Выделяем первую строку, правой кнопкой мыши выбираем Формат ячеек.
Во вкладке Вид - выбираем нужный цвет, таким же образом выделяем те ячейки которые должны быть одного цвета.
В ячейки b1 и a2:a11 можно вводить любые числа, а в ячейках b2:b11 вводим ответы. Это программа для самопроверки.
В ячейку c1 введем слово ”проверка”.
В ячейки c2 введем формулу =если(b2=b1*a2;”молодец”;”подумай”), таким же образом в ячейки c3:c11. Это формула проверяет правильность введенного ответа.
В ячейку d1 введем слово “ошибка”.
В ячейку d2 введем формулу =если (и (с 2=”подумай”;1);1;0). Также для ячеек d3:d11 нужно написать подобную формулу. Если в проверке было слово “подумай”, то в ошибке будет стоять единица. Это удобно чтобы подсчитать общее количество ошибок.
В ячейку d12 введем формулу, которое вычисляет общее количество ошибок сумм(d2:d11).
В ячейку e11 введем слово ”оценка”.
В ячейку e12 введем формулу которая будет оценивать ваше знание =если(d12>3;”плохо”;если(d12=0;”отлично”;”хорошо”)). Это формула проверяет : если количество ошибок больше трех, то оценка “плохо”, если ошибок нет то “отлично”, в других случаях “хорошо”.
Рассмотрим пример, в котором есть ошибки:
Ссылка на документ
Данный пример позволяет увидеть ту часть таблицы данного документа в другом документе с помощью трехмерных ссылок.
Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке.
Создадим таблицу следующего вида, и сохраним его под именем Книга23:
Выделим ячейки А1:G16.
Правой кнопкой мыши выбираем Формат ячеек.
Во вкладке граница выбираем Граница внешние и внутренние.
Выделяем 11-ую строчку правой кнопкой мыши, выбираем объединить ячейки.
В ячейки b10:g10 вводим формулы =сумм(b2:b9), для каждого столбца своя буква.
В ячейку b12 вводим формулу, которая считает общее количество проданных деталей за 6 дней только в Москве =СУММ(B2:G2)+СУММ(B4:G4)+СУММ(B6:G6)+СУММ(B8:G8).
В ячейку b13 введем формулу =(СУММ(B3:G3)).
В ячейку b14 =(СУММ(B5:G5)).
В ячейку b15 =(СУММ(B7:G7)).
В ячейку b16 =(СУММ(B9:G9)).
В ячейках c12:g16 нужно убрать внутренние границы. Выделяем эти ячейки, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница убираем внутренние.
Все оставшиеся ячейки заполняем так как указано в примере.
Создадим таблицу следующего вида, и сохраним его под именем Книга5:
Выделяем ячейки a1:b6, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница выбираем внешние и внутренние.
В ячейку b2 введем формулу, которая ссылаясь на дугой документ, присваивает то значение, которое мы хотим увидеть в этом документе.
В ячейку b2 введем формулу =[книга23.xls]лист1!b12.
В ячейку b3 введем формулу =[книга23.xls]лист1!b13.
В ячейку b4 введем формулу =[книга23.xls]лист1!b14.
В ячейку b5 введем формулу =[книга23.xls]лист1!b15.
В ячейку b6 введем формулу =[книга23.xls]лист1!b16.
.1 Консолидация данных
Консолидация данных это сбор и объединение данных из разных исходных областей. Исходные области могут располагаться на любом листе или книге, на других открытых листах или книгах. Существует несколько способов консолидации. Рассмотрим консолидацию с помощью трехмерных ссылок (трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов), что является наиболее предпочтительным способом. При использовании трехмерных ссылок отсутствуют ограничения по расположению данных в исходных областях.
Итак, сначала сделайте такую таблицу:
Для этого:
Введите имена строк и столбцов таблицы.
Выберите пункт меню Формат/ячейки.
Во вкладке Границы сделайте внутренние и внешние рамки.
Во вкладке Вид измените цвет ячеек таблицы.
Во вкладке Шрифт измените цвет текста.
Теперь нужно установить в этой таблице ссылки на соответствующие ячейки исходной таблицы. Для этого используем данные из файла таблица.doc, т.е. данные из такой таблицы:
Чтобы вставить ссылки в таблицу сделайте следующее:
Выделите первую пустую ячейку таблицы.
В этой ячейке поставьте знак " = ".
Перейдите в файл таблица.doc, где находятся исходные данные.
Выделите ячейку с нужными данными в исходной таблице.
Нажмите Enter. В ячейку вставится ссылка на ячейку исходной таблицы.
Теперь сделайте диаграмму по этой таблице. Она должна выглядеть так:
Для этого:
Выделите таблицу.
На панели инструментов Стандартная нажмите кнопку Мастер диаграмм.
Шаг первый. Выберите тип диаграммы График.
Шаг второй. Выберите диапазон данных. Расположите ряды в строках.
Шаг третий. Во вкладке Линии сетки поставьте основные линии по оси Х.
Шаг четвертый. Расположите диаграмму на имеющемся листе.
Теперь в получившейся диаграмме щелкните два раза по нижней оси. В появившемся окне Формат оси выберите вкладку Шкала и выключите флажок Пересечение с осью Y между категориями.
Щелкните два раза по полю диаграммы. Измените цвет поля.
Теперь, щелкнув два раза по линии графика, также измените ее цвет.
Щелкните два раза по вертикальным линиям сетки диаграммы. Во вкладке Вид появившегося окна Формат линий сетки измените тип и цвет линии.
Повторите то же самое с горизонтальными линиями сетки.
Отредактируйте размер диаграммы.