Формула - если(условие;истина;ложь)

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

Общие сведения

Краткий обзор. Основные термины и понятия

Табличный процессор Microsoft Excel – средство, с помощью которого можно выполнять самые разнообразные повседневные вычисления.

Электронные таблицы Excel широко применяются для инженерных вычислений.

Лист на экране – это лишь одна страница текущего документа, который называется книгой Еxcel. По умолчанию каждая новая книга содержит 3 листа. Но один файл может содержать до 255 листов, обозначенных от Лист 1 до Лист 255.

Каждый лист книги отображается ярлычком. Ярлычки расположены над строкой состояния в нижней части экрана.

Если щелкнуть на ярлычке Лист 2 excel выведет этот лист на экран. Лист можно переименовать (для удобства) – установить курсор на ярлык листа и дважды щелкнуть, написать новое имя листа, или установить курсор на ярлык листа и нажать правую кнопку, выбрать переименовать и написать новое имя.

Лист разделен на 256 столбцов, обозначенных буквами от A до IV, и 65536 строк, пронумерованных от 1 до 65536.

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

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

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

Вставить и убрать строку формул – вид – строка формул.

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

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

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

ЗАДАНИЕ 1.

Ввести в ячейки А1:А5 числа, в ячейке А10 вычислить сумму содержимого в этих ячейках. Установить курсор в ячейку А10 – знак =, затем щелкнуть на А1+, на А2+ и так далее до А5, нажать enter.

В ячейке А8 посчитать сумму чисел в этих ячейках, использую значок автосуммы -S, аналогично можно посчитать сумму, использую автосумму по строке.

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

Пример 1: чтобы заполнить диапазон ячеек В1:В15 последовательностью значений от 1 до 15, ввести в соседние ячейки В1-В2 числа 1 и 2, выбрать эти две ячейки в качестве исходных и перетащить маркер автозаполнения.

Заполните последовательность четных и нечетных чисел.

Заполнить таблицы ПРОДАЖА ПО КВАРТАЛАМ, ОВОЩИ, НАПИТКИ. Каждую таблицу на отдельном листе. Назвать листы – овощи, продажа, напитки.

 

напитки

Как видно из таблицы НАПИТКИ, при копировании формулы в колонке СУММА - столбец Е – цена х количество, из строки 3 в строки до 7, номера строк, используемые в формуле как часть идентификаторов ячеек, увеличиваются.

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

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

Копирование ниже приводит к увеличению номеров строк.

Иногда нужна абсолютная адресация. Например, в таблице НАПИТКИ, колонка % вычисляет процент прибыли по каждому напитку к общей прибыли, то есть общая прибыль используется в нескольких местах, и это значение находится в определенной ячейке. Чтобы закрепить эту ячейку, нужно вставить знак $ в адрес, например, $G$7.

Во время копирования идентификатор любой строки или столбца, перед которым стоит знак $, останется неизменным.

Знак доллара можно ввести вручную: переход на анг.язык – shift+4. но необязательно вводить вручную: ввести адрес – F4. Преобразует ячейку - $G$7 – при однократном нажатии, многократное нажатие приведет к изменению количества и расположения знака доллара.

В таблице НАПИТКИ заполнить графу %, используя абсолютную адресацию.

Пример 2. Даны векторы а(-3,-1,2), в=(1,0,5),.

- найти длину вектора а.

- найти скалярное произведение векторов а и в. Проверить, будут ли эти векторы ортогональны.

Решение

Для векторов а =(х₁, у₁, z₁) b=(x₂,y₂,z₂) справедливы формулы:

1. Квадрат длины вектора а: а² =x²₁+y²₁+z²₁;

2. Скалярное произведение векторов а и в: а*в=х₁*х₂+у₁*у₂+z₁*z₂. Если векторы ортогональны, то а*в=0;

 

1 Вычисляем квадраты координат вектора а, в столбце D - ячейка D2: знак =; щелкаем в ячейке с координатой х – В2, знак возведения в квадрат – shift +6, enter;

2. Протягиваем кнопку Автозаполнения вниз – посчитали квадраты координат вектора а - 4 9 1.

3. Выделяем заполненный столбец D и протягиваем кнопку автозаполнения вправо – заполняется столбец E – квадраты координат вектора b - 0 1 16.

4. Выделяем заполненные столбцы D и E до строки 6 включительно и нажимаем кнопку Автосуммы на панели инструментов - появляются величины квадратов длин векторов а и b соответственно 14 и 17.

5. Вычисление скалярного произведения по заданной формуле – надо перемножить соответствующие координаты векторов a и b, в столбце F – ячейка F2: знак =; щелкаем в ячейке B2, знак умножения *, щелкаем в ячейке С2, enter - вычислили х1* х2; кнопку Автозаполнения протягивается вниз; вычисляется скалярное произведение - выделяется столбец F до строки 8 включительно – знак Автосуммы на панели инструментов. В ячейке F8 – значение скалярного произведения – 1. Скалярное произведение не равно 0, следовательно, векторы а и b не ортогональны.

ЗАДАНИЕ 2. Форматирование ячеек

Изменить способ изображения содержимого ячеек можно с помощью меню ФОРМАТ, панели форматирования, контекстного меню. Меню ФОРМАТ – шесть вкладок.

Вкладка Число – управляет отображением содержимого ячеек – числовой, процентный, форматы для отображения даты и т.д.

Вкладка выравнивание позволяет управлять размещением отображаемого содержимого ячейки. Можно объединять ячейки, управлять ориентацией, переносом текста. По умолчанию Excel выравнивает текст по левому краю, а числа – по правому. Можно изменить способ выравнивания через меню или кнопкой на панели инструментов.

Вкладка шрифт позволяет изменять стиль, цвет шрифта (как в Word).

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

Вкладка вид устанавливает фоновый цвет.

В таблице ПРОДАЖА ПО КВАРТАЛАМ объединить ячейки заголовка таблицы – выделить ячейки – Формат – Ячейки – Выравнивание – Объединить ячейки. Можно кнопкой «объединить и поместить в центре» (а®) на панели инструментов.

Сделать границы для ячеек таблицы: выделить таблицу – формат – граница – выбрать нужную границу, нужную линию. Можно на панели инструментов – кнопку клеточки – выбор нужных границ. Аналогично объединить ячейки в таблице НАПИТКИ. Сделать границы, как показано на образце – линии, а для заголовка – все границы.

По умолчанию текст в ячейках не переносится. При вводе длинного текста текст просто переходит в соседние ячейки. Чтобы перенести – выделить ячейку – Формат – Ячейки – Выравнивание – Переносить по словам.

Чтобы изменить ширину столбцов: Формат – Столбец – в числовом поле ширина столбца указать ширину.

Подвести курсор к линии разделения заголовков столбцов (например, для таблица ОВОЩИ столбцов А и В – растянуть столбец наименование) нажать левую кнопку и перетаскивать линию вправо до нужной ширины.

Заполнить таблицу Банк. Для Excel справедливы операции из меню правка – копирование, заменить.

БАНК

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

1 Как выполнить ввод формулы в ячейки, автозаполнение?

2 Как выполняется вставка листов, строк, столбцов?

3 Как посчитать по формуле в Excel?

4 Как выполнить объединение ячеек, перенос по словам, изменить ориентацию?

5 Как выполняется копирование значений, формул из одной ячейки в другую?

6 Как расширить столбцы, строки, переименовать листы?

 

ЗАДАНИЕ 3

МЕНЮ ВСТАВКА

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

Помимо простых арифметических вычислений Excel имеет множество функций. Чтобы применить функцию Вставка – Функция – Выбор категории – Выбор функции.

Пример 3: вычислить sin, cos заданных углов. Заполняем столбец значений аргумента – X. Значение функции выводится в столбце Y. Установить курсор в столбце Y – вставка – функции – математические – sin, в появившемся окне в поле запроса указываем адрес аргумента. Аналогично вычисляем и по другим функциям. Для тригонометрических функций аргумент должен быть выражен в радианах. Функции позволяют перевести градусы в радианы и наоборот. Выполнить эти действия.

Пример 4: дана система линейных уравнений; найти определитель матрицы, посчитать обратную матрицу, решить систему уравнений:

7x1+4x2-x3=13

3x1+2x2+3x3=3

2x1-3x2+x2=10

Чтобы решить линейную систему уравнений, надо посчитать обратную матрицу, затем умножить ее на столбец свободных членов.

1. Найти определитель: установить курсор в ячейке для значения определителя F2, выделить матрицу коэффициентов - A1:C3, вставка – функции – математические, МОПР(вычисляется определитель) - 102.

2. Найти обратную матрицу: выделить ячейки для обратной матрицы A5:C7, вставка- функции – математические – МОБР (вычисляется обратная матрица) – выделяется исходная матрица (A1:C3)- Ctrl + Shift + Enter. Если результатом вычисления является матрица, то всегда нажимаются все три клавиши.

3. Чтобы найти решение, надо полученную обратную матрицу умножить на столбец свободных членов: выделяется столбец для вектора решения F5:F7 – вставка - функции – математические – МУМНОЖ (перемножает матрицы) – появляется окно, в котором указано два поля – для двух перемножаемых матриц – выделяется обратная матрица (в первом поле – A5:A7), выделяется столбец свободных членов (во втором поле D1:D3) - Ctrl + Shift + Enter (так как результатом является вектор). Решение 0, 3, -1.

Для таблиц ОВОЩИ и ПРОДАЖА ПО КВАРТАЛАМподсчитать среднее, мах и мin по каждому виду овоща и так далее. Использовать функции.

Условная функция ЕСЛИ

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

ФОРМУЛА - ЕСЛИ(УСЛОВИЕ;ИСТИНА;ЛОЖЬ)

Операторы, которые можно использовать с функцией ЕСЛИ: =. < >, >=, <=

Пример 5: составить таблицу температур: если Т>0, то написать «тепло, если T<0 – «холодно».

А	В	С
Дни недели	температура
Понедельник		=если (В3>0; «тепло»; «холодно»)
Вторник
Среда	-2

Вносим в С3 формулу условия: Вставка – Функции – Логические - в окне три числовых поля 1-логический оператор, 2 – истина, 3 – ложь, протягиваем кнопку автозаполнения.

Задача: если куплено товара больше, чем на 1000 тенге, то делается скидка 10%, иначе скидка =0. Если делается скидка, то выводится запись «вам предоставляется скидка 10%». Иначе – «спасибо за покупку». Подсчитать скидку. Вывести сумму уплаты. Выполнить эту задачу.

Вставка диаграмм

Чтобы вставить диаграмму – Вставка – Мастер Диаграмм – Тип – Вид. Мастер диаграмм состоит из 4 шагов: выбор типа, вида, расположение, на каком листе.

Сделать диаграммы для таблиц ОВОЩИи ПРОДАЖАПОКВАРТАЛАМ. Изменить размер и расположение диаграммы. Щелкнуть на диаграмме – появятся черные квадратики – маркеры изменения размеров. Поместить курсор на маркере и двигать маркер. Размер изменится.

Изменить расположение легенды, сделать заголовок к диаграмме, изменить шрифт легенды и осей.

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

1 Как производится вставка функций?

2 Как выполняется перевод радианов в градусы и обратно?

3 Как выполняется вставка математических формул?

4 Для чего используются логические функции и как они применяются в Excel?

5 Как построить диаграмму для заданной таблицы?

ЗАДАНИЕ 4

МЕНЮ ДАННЫЕ

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

Просортировать таблицу ПРОДАЖА ПО КВАРТАЛАМ по кварталам. Данные – Сортировка – выбирается поле сортировки.

Просортировать таблицу Банк по полю дебет – тенге и кредит-тенге.

Заполнить таблицу МОРОЖЕНОЕ. Отсортировать по наименованию, по адресату.

В таблице МОРОЖЕНОЕ приведены данные работы цеха по выпуску мороженого по кварталам: в каком квартале какой вид мороженого производился и на какую сумму (поля: квартал, наименование, сумма), куда отправлялось мороженое (поле адресат), например, сеть – это лотки по продаже мороженого, точка – фирменные кафе.

МОРОЖЕНОЕ

 

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

Чтобы вычислить итоги через меню данные – Данные – Итоги.

Вычислить итоговую сумму по кварталам, используя операцию итоги: установить курсор внутри таблицы мороженое - Данные – Итоги – в появившемся окне в поле «при каждом изменении» установить «квартал», в поле операция установить «сумма», в поле «добавить итоги по» установить «сумма». Аналогично посчитать максимум, минимум, среднее значение.

Подсчитать итоги по полю наименование, адресат (предварительно сделать сортировку).

Вернуть таблице первоначальный вид.

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

1 Для чего предназначена фильтрация как выполнить фильтрацию?

2 Какие функции выполняет меню Итоги?

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

 

 

ВАРИАНТЫ ЛАБОРАТОРНОЙ РАБОТЫ 4 (см. пример 2)

Даны векторы а, в:

- найти длину вектора а.

- найти скалярное произведение векторов а и в. Проверить, будут ли эти векторы ортогональны

Варианты	Вектор а	Вектор b	варианты	Вектор а	Вектор b
	5, -4, 3	1,2,-8	16.	4,2,5	-1,0,6
	-3,1,0	7,1,-5	17.	9,1,4	8,2,-3
	5,-3,-1	7,1,4	18.	5,2,6	1,8,-2
	1,-5,4	5,1,4	19.	2,-4,8	5,4,7
	7,-5,0	2,8,6	20.	3,2,5	4,0,-3
	8,-5,4	2,1,8	21.	2,9,6	0,6,4
	3,-5,8	6,3,9	22.	2,-5,4	5,3,4
	7,2,2	-5,7,-7	23.	6,2,4	7,2,-7
	-9,5,0	6,3,4	24.	0,6,0	5,3,4
	8,6-,4	10,5,1	25.	4,2,0	1,-7,8
	9,-5,0	0,6,4	26.	4,-10,9	5,7,4
	5,-3,-1	5,8,3	27.	8,2,-3	1,-7,1
	3,5,7	8,4,1	28.	2,-6,4	5,-3,9
	9,-7,0	7,8,7	29.	2,-4,8	5,4,7
	6,4,6	0,6,-2	30.	6,-3,1	5,8,4

ВАРИАНТЫ ЛАБОРАТОРНОЙ РАБОТЫ № 4 (см. пример 4)

Дана система линейных уравнений; найти определитель матрицы, посчитать обратную матрицу, решить систему уравнений

вар	система	вар	система	вар	система
	2x1- x2+3x3= - 4 x1+3x2 - x3=11 x1- 2x2+2x3=-7		4x1+ x2 - 3x3= 9 x1+ x2 – x3= - 2 8x1+3x2-6x3= 12		2x1 – 3x2 – 4x3= 1 7x1 – 9x2 – x3= 3 5x1 – 6x2 = 3x3= 7
	3x1- 2x2+4x3=12 3x1+4x2- 2x3= 6 2x1 - x2 - x3=-9		2x1 – x2 + 2x3= 8 x1 + x2 + 2x3= 11 4x1 + x2 + 4x3= 22		-3x1 + 5x2 + 6x3= - 8 3x1 + x2 + x3= - 4 x1 - 4x2 – 2x3= - 9
	3x1+ x2 + 2x3=-3 2x1+2x2 + 5x3= 5 5x1+3x2 + 7x3=1		2x1 – x2 – 3x3= - 9 x1 + 5x2 + x3= 20 3x1 + 4x2 + 2x3=15		x1 + 4x2 – x3= -9 4x1 - x2 +5x3= -2 3x2 – 7x3= -6
	3x1 - x2 + x3=-11 5x1+ x2 + 2x3=8 x1 + 2x2 + 4x3=16		3x1 – x2 + x3= 9 5x1 + x2 + 2x3= 11 x1 + 2x2 + 4x3= 19		5x1 – x2 – 2x3= 1 3x1 - 4x2 + x3= 7 2x1 + 3x2 - 3x3= 4
	2x1 + 3x2 + x3=12 2x1 + x2 + 3x3=16 3x1 + 2x2 + x3=8		3x1 – 3x2 + 2x3 =2 4x1 – 5x2 + 2x3 =1 x1 – 2x2 =5		3x1 + 4x2 – 2x3=11 2x1 - x2 - x3=4 3x1 – 2x2 + 4x3=11
	8x1 + 3x2 – 6x3= - 4 x1 + x2 – x3= 2 4x1 + x2 – 3x3= - 5		2x1 + 8x2 – 7x3=0 2x1 – 5x2 + 6x3=1 4x1 + 3x2 – x3=7		5x1 + 6x2 – 2x3= 2 2x1 + 3x2 – x3= 9 3x1 + 3x2 – x3= 1
	2x1 + 3x2 + 4x3=12 7x1 – 5x2 + x3= - 33 4x1 + x3= - 7		5x1 – 5x2 – 4x3= - 3 x1 – x2 + 5x3= 1 4x1 – 4x2 – 9x3= 0		4x1 – 9x2 + 5x3=1 7x1 – 4x2 + x3=11 3x1 + 5x2 – 4x3=5
	4x1 + x2 + 4x3=19 2x1 – x2 + 2x3=11 x1 + x2 + 2x3=8		3x1 + 4x2 + x3=2 x1 + 5x2 – 3x3=4 2x1 – x2 + 4x3=5		3x1 + 2x2 – 4x3=8 2x1 + 4x2 – 5x3=1 5x1 + 6x2 – 9x3=2
	6x1 + 3x2 – 5x3=0 9x1 + 4x2 – 7x3=3 3x1 + x2 – 2x3=5		8x1 - x2 + 3x3=2 4x1 + x2 + 6x3=1 4x1 – 2x2 – 3x3=7		3x1 – 5x2 + 3x3=4 x1 + 2x2 + x3=8 2x1 – 7x2 + 2x3=1
	3x1 – 2x2 – 5x3= 5 2x1 + 3x2 – 4x3=12 x1 – 2x2 + 3x3= - 1		3x1 + x2 – 2x3=6 5x1 – 4x2 + 2x3=4 -2x1 + 5x2 – 4x3=0		4x1 – 3x2 + x3=3 x1 + x2 – x3=4 3x1 – 4x2 + 2x3=2

 

ВАРИАНТЫ ЛАБОРАТОРНОЙ РАБОТЫ № 4 (см. пример 5)

1. Составить таблицу вычисления стоимости покупки с учетом скидки. Скидка в 10% предоставляется, если сумма покупки больше 1000 тенге.

2. Составить таблицу проверки знания истории архитектуры. вопрос: кто архитектор Исаакиевского собора? Дается три варианта ответа: Доменико Тезини, Огюст Монферран, Карл Росси. Если ответ неправильный, то выдается сообщение «Вы ошиблись, архитектор Исаакиевского собора – Огюст Монферран».

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

4. Составить таблицу, которая проверяет, делятся ли на три данные целые числа.

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

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

7. Составить таблицу определения стоимости разговора по телефону с учетом скидки 20%, предоставляемой по субботам и воскресеньям.

8. В очереди за билетами стоят девушки и юноши. Какое количество девушек и юношей стоит в очереди?

9. Скольким девушкам и скольким юношам в вашей группе понравился фильм «Троя»?

10. Составить таблицу проверки знания. Вопрос – кто автор романа «Путь Абая»? Возможные варианты: Г. Мусрепов, Г. Мустафин, М. Ауэзов.