Табличный процессор MS Excel 2010

Размещено на http://www.allbest.ru//

Размещено на http://www.allbest.ru//

функция форматирование таблица автосумма

Семестровая работа

«Табличный процессор MSExcel 2010»

Задание 1

Начислить материальную помощь. Расчетная матпомощь назначается по двум условиям:

при наличии более двух детей - в размере одной минимальной зарплаты на каждого ребенка;

в возрасте свыше 60 лет - в размере двух минимальных зарплат.

Задание 2

В столбце Адрес соединить текст (через пробел) из столбцов Улица, Дом, Квартира.

В Столбце Отбор написать «Да», если в столбце Улица текст начинается с буквы «Г», в противном случае написать «Нет».

Задание 3

Вычислить значение кусочно-ломаной функции y(x) и построить ее график (точечная диаграмма).

cos(x), при x<0

y(x) = 5, при 0?x<2

x, при x?2

Задание 4

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

Задание 5

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

Теоретические вопросы

Вопрос 1

Каким образом задаются условия в диалоговом окне при использовании условного форматирования?

Условное форматирование позволяет применять форматирование ячеек избирательно или автоматически на основании их значений. К примеру, вы можете задать условное форматирование таким образом, чтобы все ячейки с отрицательными значениями закрашивались в красный цвет. Когда вы вводите или меняете значение в ячейке, Excel проверяет его и сравнивает с условиями, заданными в правилах. Если значение отрицательное, фон ячейки закрасится, иначе останется без изменений.

Условное форматирование - это простой способ определить ячейки с ошибочными записями или значениями определённого типа. Вы можете использовать формат (например, красная заливка), чтобы легко идентифицировать определенные ячейки.

Выделяем диапазон ячеек, к которому мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Больше...

Выберем «больше 50».

Все ячейки, значение которых больше 50, окрасились в другой цвет.

Вопрос 2

Транспонирование таблиц (специальная вставка).

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

Транспонировать данные в Excel можно с помощью:

"Специальной" вставки с галочкой "Транспонировать";

Функции Excel =трансп();

и с помощью сводной таблицы.

Способ транспонирования - специальная вставка

Транспонируем данные с помощью специальной вставки с галочкой "транспонировать".

Для этого выделяем необходимые данные и копируем их с помощью кнопки "копировать" или с помощью сочетания клавиш ctrl+c.

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

1.

2.

3.

После нажатия откроется диалоговое окно, в котором ставим галочку "транспонировать" и нажимаем ОК.

Данные переворачиваются, и мы получаем нужное нам строчное представление:

Вопрос 3

Что такое функции в Microsoft Excel и каким образом можно вызвать функцию?

В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач.

Функция Excel - это заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат

Способы вызова функции:

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

Ввод с помощью кнопки «сигма». Одно из средств облегчить и ускорить работы с функциями -- кнопка сигма на панели инструментов «Стандартная». В ней разработчики Microsoft «спрятали» пять часто используемых функций: СУММ, СРЗНАЧ, СЧЁТ, МАКС, МИН

С помощью кнопки в строке формул

С помощью пункта меню "Вставка" --> "Функция";

В поле имени разворачиваем список --> другие функции

Вопрос 4

Для каких случаев удобно использовать круговые диаграммы?

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

Круговые диаграммы рекомендуется использовать, если:

Требуется отобразить только один ряд данных.

Все значения, которые требуется отобразить, неотрицательны.

Почти все значения, которые требуется отобразить, больше нуля.

Количество категорий не более семи.

Категории соответствуют частям общего круга.

Вопрос 5

Каким образом можно задать параметры функции? Назовите несколько способов.

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции описывает операцию, которую эта функция выполняет, например, СУММ.

Аргументы функции Excel - задают значения или ячейки, используемые функцией, они всегда заключены в круглые скобки. Открывающая скобка ставится без пробела сразу после имени функции. Например, в формуле «=СУММ(B1:K1)», СУММ -- это имя функции, а В2 и К1 -- ее аргументы.

Эта формула суммирует числа в ячейках В1, и К1. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки, например функция ПИ(). При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. В функции можно использовать до 30 аргументов.

Можно вводить функции в рабочем листе прямо с клавиатуры или с помощью команды Функция меню Вставка. Если выделить ячейку и выбрать команду Вставка/Функция, Excel выведет окно диалога Мастер функций. Открыть это окно можно также с помощью кнопки Вставка функции на строке ввода формул.

Пример задания параметров формулы вручную:

Вопрос 6

Что такое логическое выражение и какие бывают виды выражений?

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

Функция ЕСЛИ. Результат: Возвращает одно значение, если аргумент лог_выражение при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция ЕСЛИ используется для проверки значений формул и организации переходов в зависимости от результатов этой проверки. Результат проверки определяет значение, возвращаемое функцией ЕСЛИ.

Создадим ряд чисел (1;9). Зададим условие: если значение ячейки больше 4, то 1, иначе 0.

Функция И. Результат: Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Например, зададим условие: значение ячейки В1 больше 4 и меньше 4

Результат логического умножения - ЛОЖЬ.

Функция ИЛИ. Возвращает логическое значение ИСТИНА, если хотя бы один аргумент функции вернет истинное значение.

Тот же самый пример, но с функцией ИЛИ.

Функция НЕ. Принимает в виде аргумента всего одно логическое значение и меняет его на противоположное, т.е. значение ИСТИНА она изменит на ЛОЖЬ и наоборот.

В данном случае обратим значение ячейки В5.

Функция ИСТИНА. Не принимает никаких аргументов и просто возвращает логическое значение «ИСТИНА».

Функция ЛОЖЬ. Аналогична функции ИСТИНА, за исключением то, что возвращает противоположный результат ЛОЖЬ.7. Подключение дополнительных функций.

Вопрос 7

Подключение дополнительных функций.

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

Для этого необходимо выполнить Файл/параметры/надстройки/надстройки Excel/пакет анализа.

Далее на вкладке Данные появляется функция Анализ данных.

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

Вопрос 8

Как создать формулу, если она должна содержать функции? Каким образом используют “Мастер функций”?

Есть несколько вариантов ввода функций в ячейки.

вставка стандартной функции нажатием на кнопку fx в строке формул (или командой Вставка \ Функция); в появившемся окне выбирается нужная функция из нужной категории и заполняется окно функции;

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

копирование функции из одной ячейки в другую, в том числе с помощью операции «автозаполнение»; при этом следует помнить, что ссылки без префикса $ будут пересчитаны. Введите данные в начальную ячейку или смежные ячейки (если задается правило заполнения). Выберите ячейку или ячейки, в которых установлено правило заполнения. Установите курсор мыши на Маркере заполнения, маленьком квадратике в правом нижнем углу активной ячейки. Перетащите мышью Маркер заполнения (по горизонтали или по вертикали) до последней ячейки, которую хотите заполнить по образцу. Отпустите кнопку мыши.

Также используется вызов Мастера функции. Мастер функций позволяет находить функции при помощи ключевых слов. Хоть этот помощник и является очень полезным, все же могут возникнуть затруднения при его использовании. Если у вас нет определенного опыта работы с функциями в Excel, то гораздо проще будет найти нужную функцию через библиотеку. Для более продвинутых пользователей Мастер функций является мощным средством быстрого поиска нужной функции.

Допустим, необходимо найти среднее значение из имеющегося диапазона чисел.

И с помощью Мастера функций мы получаем среднее значение имеющегося диапазона.

Вопрос 9

Какие возможности при организации вычислений дает применение в функции ЕСЛИ составных или сложных условий?

Функцией ЕСЛИ удобно пользоваться для создания логических условий при отборе данных. Функция удобна тем, что как условия, так и возвращаемые значения могут быть заданы другими функциями а, следовательно, можно создавать достаточно сложные логические конструкции.

Функцию ЕСЛИ можно вкладывать внутрь друг друга до 7 раз. Пример применения функции ЕСЛИ для преобразования из двоичной в десятеричную систему исчисления.

Формула для ввода в первую ячейку:

=СУММ(ЕСЛИ(A3=1;8;0);ЕСЛИ(B3=1;4;0);ЕСЛИ(C3=1;2;0);ЕСЛИ(D3=1;1;0))

Вопрос 10

Статистические функции; способы вызова (автосумма).

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

Примеры статистических функций:

Зададим ряд значений случайным распределением от 1 до 9 с помощью функции =СЛУЧМЕЖДУ(1;9). Для статистического анализа применим функции:

СРЗНАЧ() - Вычисляет среднее арифметическое аргументов.

СРОТКЛ() - Вычисляет среднее абсолютных значений отклонений точек данных от среднего.

КВАДРОТКЛ() - Вычисляет сумму квадратов отклонений.

СРГЕОМ() - Вычисляет среднее геометрическое.

Способы вызова автосуммы

1 способ:

Выделить диапазон ячеек с числами, щелкнуть на раскрывающей стрелке кнопки Автосумма и выбрать Итоги/Сумма; результат вычисления появится ниже по столбцу (или правее по строке). Если было выделено несколько столбцов, то результат вычисления появится под каждым из них.

2 способ:

Выделить диапазон ячеек, для которых нужно применить функцию Автосумму. На панели Формулы выбрать значок Автосумма/Сумма

3 способ

Этот способ позволяет выбрать любой, даже несвязанный диапазон для вычисления: активизировать ячейку для помещения результата, щелкнуть на панели Формула кнопкиАвтосумма и выбрать нужную функцию; диапазон ячеек с числами, расположенный выше по столбцу (или левее по строке) будет выделен подвижной пунктирной рамкой; выделение несмежных диапазонов выполняют при нажатой клавише <Ctrl>.

Размещено на Allbest.ru