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

НАЧАЛЬНЫЕ СВЕДЕНИЯ ОБ EXCEL

Microsoft Excel для Windows является мощным программным средством для работы с таблицами данных, позволяющих упорядочивать, анализировать и графически представлять различные виды данных. Сразу же после появления в 1985 году Microsoft Excel получил признание как наиболее мощная и популярная электронная таблица. Excel 7.0 поддерживает новейшую технологию работы с электронными таблицами.

После запуска Excel на экран выводится пустая книга – это основное рабочее пространство. Облик Excel зависит от типа монитора. Мы будем рассматривать его для 14-15 дюймового монитора.

Обычно экран содержит 5 областей:

ü окно книги, которое занимает большую часть экрана;

ü строку меню;

ü две или больше панелей инструментов;

ü строку формул;

ü строку состояний.

Все вместе эти пять областей называются рабочей областью EXCEL.

Файл Microsoft EXCEL является рабочей книгой, состоящей из одного или нескольких листов. Листы могут быть пяти типов: рабочие листы, листы диаграмм, модули Visual BASIC, листы диалога, листы макросов Microsoft EXCEL.

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

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

Видимый на экране рабочий фрагмент листа – это только малая часть огромной таблицы высотой 16384 ряда и шириной 256 столбцов. С помощью команд и инструментов можно выполнять нужные вычисления. Применяя такие функции EXCEL как копирование, перемещение, сортировка, консолидация, составление диаграмм и сводных таблиц, можно сортировать, перегруппировывать, анализировать и требуемым образом представлять данные на экране монитора и в документах. Можно использовать Microsoft EXCEL и для составления шаблонов деловой документации, таких, например, как счета общих затрат, ведомости торговой прибыли и т. д.

ВВОД ДАННЫХ

Рабочий лист состоит из столбцов и строк. Столбцы идут вертикально и озаглавлены буквами. Строки расположены горизонтально и обозначены цифрами. Ячейки нумеруются согласно позиции в строке и столбце, как в игре «Морской бой». Сочетание позиций определяет адрес ячейки (например, А1, Е7 и т.д.). С помощью мыши или с помощью клавиш управления курсором можно выделить ячейку. В этом случае ячейка становится текущей и в нее можно вводить данные с клавиатуры или редактировать ее содержимое.

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

Надо нажать на кнопку “Office”, после чего выбрать “Параметры Excel ” –> закладка “Формулы” –> “Стиль ссылок R1C1″

Если поставить «флажок» – будут цифровые обозначения столбцов.

ВЫДЕЛЕНИЕ ЯЧЕЕК

Можно выделять смежные и несмежные ячейки:

Shift + щелчок мыши – выделяет смежные ячейки;

Ctrl + щелчок мыши – выделяет несмежные ячейки.

 

Упражнение № 1

1. Щелкните по ячейке В1.

2. Удерживая Shif t, щелкните по ячейке D5.

3. Увидите зону выделенных ячеек.

4. Щелкните где-нибудь мышкой – выделение отменится.

5. Удерживая клавишу Ctrl, щелкните мышью по ячейкам А1, В5, С6.

6. Отмените выделение.

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

Упражнение № 2

1. Введите в ячейку В1: Бюджет на 1996 финансовый год

2. В ячейки А3, А4 введите: Составил Дата

3. В ячейки А6 введите: Исходные темпы роста

4. В ячейки В7, В8 введите: Рост объема

5. В ячейки С7 и С8 введите: 1,5 0,9

6. В ячейку А10 введите: Отчет

Упражнение № 3

Перейдите на Лист 2.

1. Введите в ячейку А1 число 1, а в ячейку А2 – 2.

2. Выделите с помощью мыши ячейки А1:А2 (поставьте указатель мыши «белый крестик» на ячейку А1, а затем при нажатой левой кнопки мышки перетащите указатель на ячейку А2).

3. Установите указатель в правом нижнем углу ячейки А2 так, чтобы он принял форму 9 «черный крестик». Этот указатель называется маркер заполнения.

4. Нажмите левую кнопку мыши и удерживая ее перетащите маркер заполнения до ячейки А12.

В данном упражнении Вы использовали функцию автозаполнения. С помощью функции Автозаполнения (AutoFill) Microsoft EXCEL «узнает» ряд стандартных последовательностей:

· 1, 2, 3,...

· название месяцев

· название дней недели и т.д.

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

Упражнение № 4

Для использования элементов управления форм в Microsoft Office Excel 2007 необходимо включить вкладку Разработчик. Для этого выполните указанные ниже действия.

· Нажмите кнопку Microsoft Office и выберите пункт Параметры Excel.

· Откройте вкладку Основные, установите флажок Показывать вкладку «Разработчик» на ленте и нажмите кнопку ОК.

1. Выберите команду Вставить в меню Разработчик

2. Щелкните по Списки (Custom Lists).

3. В поле Элементы Списка (List Enter) наберите: Приход (клавиша Enter), Затраты на товары (клавиша Enter), Полная выручка (клавиша Enter), Статьи расходов (клавиша Enter), Реклама (клавиша Enter), Аренда помещений (клавиша Enter), Налоги и выплаты (клавиша Enter), Проценты по кредитам (клавиша Enter), Расходы всего (клавиша Enter), Прибыль (клавиша Enter).

4. Щелкните по кнопке Добавить (Add). Ваш новый список включен в Списки (Custom Lists).

5. Щелкните по кнопке ОК.

Упражнение № 5

С помощью функции Автозаполнения введите на Листе 1, начиная с ячейки D13, созданную последовательность. Если Вы забыли как это делать, вернитесь к упражнению №3.

Упражнение № 6

Импортирование данных в список

Если где-то в рабочей книге уже есть некоторый список, то этот список можно загрузить в диалоговое окно Списки (Custom Lists).

1. Пусть у Вас в строке ячеек D15:J15 имеется Список:

	D	E	F	G	J
	ФИО	Дни	Начислено	Удержано	Выдано

 

2. Выделите этот блок ячеек.

3. В меню Разработчик выберите команду Добавить.

4. Щелкните по вкладке Списки (Custom Lists).

5. Адрес выделенного диапазона ячеек появится в поле Импорт списка из ячеек (Import from Cells).

6. Щелкните по кнопке Импорт (Import), а затем по кнопке ОК.

Упражнение № 7

1. Щелкните по ярлыку Лист 2. Этим самым Вы перейдете на Лист 2 Книги.

2. В любой ячейке наберите: ФИО (нажмите клавишу Enter).

3. Снова выделите эту ячейку.

4. Потащите маркер заполнения. Список готов.

Упражнение № 8

Удаление информации из ячеек (из группы ячеек)

1. Выделите любую ячейку с информацией.

2. Нажмите клавишу Del или Backspace.

3. Вместо удаленной информации наберите любую другую информацию.

РАБОТА С РАБОЧИМИ КНИГАМИ

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

Выбирать различные рабочие листы из рабочей книги можно, щелкая на соответствующих ярлычках в нижней части листов. Используя кнопки прокрутки, можно также перемещаться по рабочим листам. Можно выделять сразу несколько ячеек. Сочетание Shift и щелчка мыши выделяет смежные ячейки. Сочетание Ctrl и щелчка мыши выделяет ячейки в любом порядке. Каждая новая рабочая книга имеет несколько чистых рабочих листов. Это количество можно изменять, добавляя или удаляя их. Можно менять названия листов.

Упражнение № 9

Удаление листа

1. Щелкните правой кнопкой мыши на ярлычке Лист 2 (Sheet 2).

2. В контекстном меню выберите команду Удалить (Delete).

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

4. Щелкните по кнопке ОК. Диалоговое окно закроется - Лист 2 удалится.

Упражнение № 10

Вставка листа

1. Щелкните правой кнопкой мыши на ярлычке Лист 3 (Sheet 3).

2. В контекстном меню выберите команду Добавить (Add).

3. Откроется диалоговое окно «Вставка».

4. Убедитесь в том, что значок Лист (Worksheet) выделен и щелкните по кнопке ОК. Новый лист будет вставлен слева от текущего листа.

Упражнение № 11

Переименование листов

1. Щелкните два раза левой кнопкой мыши на ярлычке Лист 1 (Sheet 1).

2. Ярлычок Лист 1 (Sheet 1) станет активным.

3. Наберите: Бюджет 2000.

4. Нажмите клавишу Enter. Рабочему листу Лист 1 (Sheet 1) присвоено имя Бюджет 2000.

Упражнение № 12

Перетаскивание листов

1. Щелкните по ярлычку Листа «Бюджет 2000».

2. Удерживайте левую клавишу мыши, протащите ярлычок листа. Лист скопируется.

Упражнение № 13

Сохранение файла

1. В меню Файл (File) выберите команду Сохранить как... (Save as...).

2. В появившемся диалоговом окне «Сохранение документа» выберите свою папку (например, Мои документы).

3. В поле Имя файла (File Name) наберите имя файла lab1.

4. Щелкните по кнопке Сохранить (Save) или нажмите клавишу Enter.
Практическая работа № 2

Упражнение № 1

Создание простых формул

В ячейку А1 введите: =50*5+(20-2*5)

Нажмите клавишу ENTER.

В ячейке А1 появится число 260.

Набираемая формула отражается в строке формул (рис. 1).

Рисунок 1 – Строка формул

При выполнении арифметических и логических операций сохраняется приоритет действия.

Задание № 1

В ячейке B5 подсчитайте сумму отчислений в «соцстрах», если:

1. Общий фонд заработной платы составил: 49521,0 руб.

2. Начислено премий: 12380,3 руб.

3. Выделено помощи: 1233,0 руб.

4. Отчисления в «соцстрах» составляют: 5,4 % от общей суммы.

Упражнение № 2

Использование ссылок

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

Например, в ячейку B1 надо записать сумму значений ячеек A5 и A1.

1. Введите в ячейку А1 – 235, а в ячейку А5 – 365.

2. Сделайте активной ячейку B1.

3. В строке формул запишите: =

4. С помощью мышки выделите ячейку A5.

5. В ячейке B1 будет записано «=A5».

6. Наберите: +

7. Мышкой выделите ячейку A1.

8. Нажмите Enter.

9. В ячейке В1 появится сумма ячеек А1 и А2 (600).

Упражнение № 3

Упражнение № 4

Ссылки на ячейки из разных листов (другой вариант)

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

Задание № 2

Удалите все записи на Листе 1 и Листе 2.

На Листе 1 записана информация, изображенная на рис 2.

На Лист 2 встолбец «Сальдо на начало» нужно перенести значение столбца «Сальдо на конец» из Листа 1 (рис. 2).

1. Заполните Лист 1 и Лист 2 как показано на рис. 2.

2. В ячейку D3 Листа 1 запишите формулу: =А3+В3-С3

3. Нажмите ENTER.

Рисунок 2 – Лист 1 и Лист 2

4. Установите курсор на маркер автозаполнения и протяните его вниз по столбцу (скопируйте формулу).

5. Активизируйте ячейку D3 на Листе 2.

6. В строке формул наберите: =

7. Активизируйте Лист 1.

8. Выделите на нем ячейку D3.

9. Нажмите ENTER. Формула скопировалась.

Упражнение № 5

Упражнение № 6

Работа с текстом

Текстовые значения можно объединять.

Нам необходимо записать в ячейку А18: Морозова Александра Андреевна.

1. Закройте не сохраняя Книгу 2 и Книгу 3 (Упражнение № 5).

2. В ячейку Листа 1 А14 запишите: Морозова

3. В ячейку А15 – Александра

4. В ячейку А16 – Андреевна

5. В ячейке А18 наберите:

=а14&а15&а16

значения символьных констант или переменных объединяются знаком & – амперсант.

6. нажмите ENTER.

7. В ячейке А18 появится:

МорозоваАлександраАндреевна (без пробелов)

8. Наберите в ячейке А20:

=А14&” “&А15&” “&А16

9. Нажмите ENTER.

10. В ячейке A20 появится:

Морозова Александра Андреевна (c пробелами)

Упражнение № 8

Определение имени с использованием команды «ИМЯ»

Эта команда позволяет использовать текст в соседних ячейках для присвоения имен и переопределения имен.

1. Выделите ячейку А4 Листа 2.

2. Зайдите в текстовое поле имени и запишите: изделие_1

Чтобы использовать этот текст в качестве имени для ячейки B4, необходимо:

1. Выделить ячейку B4.

2. Выполнить команду:

Введите имя в поле Имя в строке формул, а затем нажмите клавишу ВВОД.

3. Щелкнуть мышкой в поле Формула.

4. На рабочем листе выделите ячейку B4.

5. В поле формулы появится ссылка:

=Лист2!$B$4

6. Нажмите клавишу ОК.

При следующем открытии окна «Присвоить имя». Это имя уже будет в списке имен.

Упражнение № 9 Сохранение файла

1. В меню Файл (File) выберите команду Сохранить как...(Save as...).

2. В появившемся диалоговом окне «Сохранение документа» выберите свою папку.

3. В поле Имя файла (File Name) наберите имя файла lab2.

4. Щелкните по кнопке Сохранить (Save) или нажмите клавишу Enter.

Практическая работа № 3

ФУНКЦИИ EXCEL

Использование функций

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

Microsoft EXCEL имеет более 300 встроенных функций, в том числе и логических, функций дат, финансовых, математических и т.д. Это – категории функции. Каждой категории соответствует свой набор функций. Сведения о функциях можно получить из «Справочной системы».

Синтаксис функций

Функция состоит из двух частей – имени и аргумента. Запись функции, также как и формулы, начинается со знака “ = ”. ИМЯ функции описывает операцию, которую она выполняет. Например СУММ – суммирование; СРЗНАЧ – нахождение среднего значения и т.д. АРГУМЕНТЫ (ОПЕРАНДЫ) задают значения или ячейки, над которыми выполняются действия. Аргументы должны быть заключены в скобки. Аргументы могут быть записаны через “: ” или через “; ”. Например: =СУММ(А1:В12) означает, что суммируются значения ячеек в диапазоне от А1 до В12, но =СУММ(А1;В12) означает сумму двух ячеек А1 и В12.

Упражнение № 1

Порядок расчета:

1. Подсчитайте Прибыль и Налог на прибыль (ячейки B4 и B5).

2. Используя функцию СУММ подсчитайте значение «Всего».

3. С помощью функции СРЗНАЧ рассчитайте «Среднее за квартал».

4. Столбец G подсчитывается по формуле: D/C*100%

Создайте таблицу и выполните расчеты

	A	B	C	D	E	F	G
		Январь	Февраль	Март	Всего	Среднее за квартал	% март к февралю
	Затраты
	Приход
	Прибыль	=В3–В2
	Налог на прибыль	=В4*20%

Требуется подсчитать средние затраты за январь, февраль, март:

1. В ячейке F1 запишите «Среднее за квартал».

2. Выделите ячейку F2, затем на Панели инструментов щелкните по кнопке Мастер функций (Function Wizard).

3. Откроется диалоговое окно Мастера функций.

4. В списке Категория (Function Category) выберите Статистические (Statistical).

5. В окне Функция (Function) появится список статистических функций.

6. В этом окне надо выделить строку СРЗНАЧ (AVERAGE) и щелкнуть по кнопке ОК.

7. Откроется окно Мастер функций – шаг 1 из 2 (Function Wizard – step 1 of 2).

8. В появившемся окне в поле Число 1 укажите диапазон ячеек B2:D2.

9. Щелкните в поле Число 1 (Number 1).

10. Выделите диапазон ячеек от B2 до D2. В процессе выделения диапазона в поле диалогового окна «Значение» (VALUE) указывается средняя величина содержимого выделенных ячеек. В поле Число 1 (Number 1) появились адреса B2:D2.

11. Щелкните по кнопке ОК. В ячейке F2 появится среднее значение.

12. Выделите ячейку Е2, установите в ней указатель мыши в правый нижний угол и протяните при нажатой левой кнопке мыши в следующую ячейку F3.

13. Отпустите клавишу мыши. В ячейке F3 будет «средний приход».

14. Остальные вычисления проведите, используя полученные знания в предыдущих упражнениях.

ВНИМАНИЕ: Принцип работы с функцией AVERAGE распространяется на все остальные функции.

Математические функции

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

Рассмотрим функцию суммирования, ее синтаксис выглядит так:

=СУММ(число1;число2;…)

Аргумент «числа» может содержать до 30 элементов, каждый из которых может быть или const или ссылкой на ячейку. Функция =СУММ() соответствует автосуммированию (å). При выполнении СУММ() можно назначить имя диапазону ячеек. Ячейки могут быть и не смежными (чтобы выделить несмежные ячейки, надо, удерживая CTRL щелкнуть мышкой по ячейкам). Чтобы назначить имя диапазону ячеек, надо:

1. Выделить диапазон ячеек.

2. Назначить имя диапазону.

3. В дальнейшем в формуле можно использовать это имя.

Задача № 1

Требуется определить итоги лицензирования а/м используя следующие данные:

А	В	С	D	E	F
Вид собственности	Легковые а/м	Автобусы	Грузовые а/м	Опасные грузы	Специальные
Федеральные
Муниципальные
Частная
С иностр. капит.
Итого

1. Перейдите на Лист 2 и создайте приведенную выше таблицу.

2. Определите имя для ячеек B2:B5 – легковые.

3. В ячейке В6 запишите:

=СУММ(легковые)

Функция =СУММ() достаточно гибкая, но при добавлении ячейки в конец (начало) диапазона могут возникнуть трудности. Например, введите следующую информацию:

C2 Õ 100

C3 Õ 200

C4 Õ 100

C5 Õ 100

C6 Õ =СУММ(C2:C5) Õ 500

Вставим строку 6 и внесем в нее число. Например, 100. Результат не изменится. Надо в ячейку C7 ввести: =СУММ(C2:C6)

Функция ABS – возвращает абсолютное значение чисел, ее синтаксис: =ABS(число)

Например: На Листе 3 в ячейке А1 записано – 200, в ячейку А2 введем =ABS(A1). Возвращает значение 200.

Функция ЗНАК – возвращает знак числа, ее синтаксис:

= ЗНАК(число)

Упражнение № 2

Если число положительное, функция ЗНАК возвращает 1, если число отрицательное, то -1, если число равно 0, то 0. Например, у нас есть следующие данные:

A	B	C	D	E
	Остаток на нач.	Приход	Расход	Остаток
АО «РАССВЕТ»

1. В ячейке Е2 запишите:

=ЗНАК(B2+C2-D2)

2. Функция возвращает - 1.

Функция ОКРУГЛ – округляет число до указанного количества десятичных разрядов, ее синтаксис:

=ОКРУГЛ(число;кол-во_цифр)

Аргумент число – это число, ссылка на ячейку. Аргумент кол-во цифр может быть положительным или отрицательным. Указывает на то количество цифр, до которого округляется. Пример:

Формула Результат

= ОКРУГЛ(897,457;-2) 900

= ОКРУГЛ(897,457;-1) 900

= ОКРУГЛ(897,457;0) 897

= ОКРУГЛ(897,457;1) 897,5

Функция ЧЕТН – округляет число до ближайшего четного числа, ее синтаксис:

ЧЕТН(число)

Пример:

ЧЕТН(5) Õ 6

НЕЧЕТ(7,2) Õ 9

Функция целое – округляет число до ближайшего меньшего целого, ее синтаксис:

=целое(число)

Пример:

=целое(185,37) Õ 185.

=целое(200,99) Õ 200.

Функция корень – возвращает значение квадратного корня, ее синтаксис:

=Корень(число)

Функция остат – возвращает остаток от деления, ее синтаксис:

=ОСТАТ(число;делитель)

Пример:

=остаток(33;4) Õ 1.

Функция степень – возвращает результат возведения в степень, ее синтаксис:

=степень(число;степень)

Log10(число) – возвращает десятичный логарифм

Log(число;основание_логарифма) – возвращает логарифм числа по заданному основанию.

Запись log(12;2) то же что и log₂ 12.

LN(число) – возвращает натуральный логарифм.

Текстовые функции

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

Функция ЗНАЧЕН – преобразует текстовый аргумент в число, ее синтаксис: =ЗНАЧЕН(текст)

Аргумент ТЕКСТ может быть строкой, заключенной в двойные кавычки или ячейкой в которой содержится текст. Преобразуемые текстовые значения могут быть записаны в любом допустимом формате. Например: =ЗНАЧЕН(А1) А1 содержит «0025» Ответ: 25

Функция длстр – возвращает количество символов в тестовой строке, ее синтаксис: =ДЛСТР(текст)

Аргумент текст может быть как текстовым, так и числовым.

Упражнение № 3

Определить длину текста в ячейке

В ячейке А1 наберите текст: «Функции EXCEL».

1. В ячейке В1 введите:

=ДЛСТР(А1)

2. Нажмите ENTER.

3. В ячейке В1 будет «13».

Или в ячейке А1 записано число 156, если в ячейке В2 набрать функцию =ДЛСТР(А1), то получим 3.

Ячейка, на которую ссылается функция ДЛСТР может содержать другие текстовые функции. Пусть в ячейку А1 записано выражение:

=ПОВТОР(«В»;25)

Если в ячейку В2 ввести функцию =ДЛСТР(А1), то получим 25.

Функция СЖПРОБЕЛЫ – удаляет из текста лишние пробелы и между словами оставляет по одному пробелу, ее синтаксис:

=СЖПРОБЕЛЫ(текст)

Пусть в ячейке А1 записано:

#### Тарасов ##### Алексей ###### Петрович

В ячейку А2 запишите:

=СЖПРОБЕЛЫ(А1)

нажмите ENTER и посмотрите на результат.

Функция СОВПАД – проверяет идентичность двух текстов: ИСТИНА, если они идентичны, ЛОЖЬ – в противном случае, ее синтаксис: =СОВПАД(текст1;текст2)

Например, в ячейке А1 написано:

СТАТИСТИЧЕСКИЕ ФУНКЦИИ

Для этого в ячейке А2 записываем функцию: =ПРОПИСН(А1)

Функция СТРОЧН – делает все буквы в тексте строчными, ее синтаксис: =СТРОЧН(текст)

Изучите действие функции СТРОЧН, на основе предыдущего примера.

Функция ПРОПНАЧ – делает прописной первую букву в каждом слове текста, преобразуя все другие буквы в строчные, ее синтаксис: =ПРОПНАЧ(текст)

 

Работа с элементами строк

Функция ЛЕВСИМВ – возвращает самые левые символы из текстовой строки, ее синтаксис:

=ЛЕВСИМВ(текст;число_знаков)

Функция ПРАВСИМВ – возвращает самые правые символы из текстовой строки, ее синтаксис:

=ПРАВСИМВ(текст;число_знаков)

Функция ПСТР – возвращает заданное число символов из строки текста, начиная с указанной позиции, ее синтаксис:

=ПСТР(текст;начальная_позиция;количество_символов)

Функция СЦЕПИТЬ – объединяет несколько текстовых элементов в один, ее синтаксис:

=СЦЕПИТЬ(текст1;текст2;…)

Задание № 2

Пусть задан массив данных:

	A	B	C
	Иванов	Петр	Иванович

Используя функции, предназначенные для работы с элементами строк, необходимо:

- записать в ячейку А3: Иванов П И

- а в ячейке А4 выделить инициалы – ПИ.

\

Логические функции

Функция ЕСЛИ – возвращает одно значение, если указанное условие истинно, и другие, если оно ложно, ее синтаксис: =Если(логическое_выражение;выраж_если_истина;выражение_если_ложь)

Упражнение № 4

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

	А	В	С	D	E
	ФИО	экз. 1	экз. 2	экз. 3	отметка о стипенд.
	Иванов
	Косов
	Симонов

 

В ячейку Е2 записать:

=Если(и(В2>3;С2>3;D2>3);»да»;»нет»)

Вложенные функции

Если в ячейке А10 записана сумма вычетов из заработной платы для определения налогооблагаемой суммы. Этот вычет может быть равен одному минимальному заработку, двум, трем пяти. Каждому из них присваивается код: 21, 22, 23, 24. Запишите в ячейку А11 следующую формулу:

=ЕСЛИ(A10=1;»21»;ЕСЛИ(A10=2;»22»;ЕСЛИ(A10=3;»23»;ЕСЛИ(A10=4;»24»))))

Допускается до 7 вложений.

Функция ДАТ

Упражнение № 5

Создание упорядоченного ряда дат

1. Для создания упорядоченного ряда дат необходимо выполнить команду: Прогрессия (вкладка Главная, группа Редактирование, кнопка Заполнить)

В открывшемся диалоговом окне «Прогрессия» поставить флажки:

– расположение: по строкам

– тип: даты

– единицы: день

2. Нажмите кнопку ОК.

3. В ячейку А1 введите начальную дату.

4. При помощи функции автозаполнения создайте упорядоченный ряд дат.

Упражнение № 6

Форматирование дат и времени

1. Выполнить команду:

Главная - Ячейки - Формат …

2. В открывшемся диалоговом окне «Формат ячеек» выберите вкладку Число.

3. В поле Числовые форматы выберите Дата.

4. В поле Тип выберите нужный.

Упражнение № 7

Арифметические операции с датами:

1. В ячейку А1 введите: 1.03.99

2. В ячейке В2 запишите формулу:

=А1+100 Õ 9.06.99

3. Пусть в ячейке А5 записано: 27.07.99, а в ячейке А6 – 31.12.99.

4. В ячейке В5 запишите формулу: =А6-А5 ® 157.

Задание № 3

Изучите другие функции для работы с датами

=Сегодня() – возвращает текущее время.

ТДАТА() – возвращает текущую дату и время

ДЕНЬНЕД(десятичная_дата,тип)

тип = 1, неделя «воскресенье – суббота»

тип = 2, неделя «понедельник – воскресенье»

месяц(дата_как_число)

год(дата)

день(дата)

Дата_в_числовом_формате – это код дата-время, используемый в Microsoft Excel для вычислений с датами и периодами времени. Можно задать аргумент дата_в_числовом_формате как текст, например «15-4-1993» или «15-Апр-1993», а не как число. Текст автоматически преобразуется в дату в числовом формате.

Автосуммирование

Автосуммирование выполняет:

§ суммы по строкам;

§ суммы по столбцам;

§ суммы по строкам и столбцам.

Упражнение № 8

1. Составьте таблицу:

	А	B	C	D

 

2. Выделите Строку 1.

3. Нажмите кнопку Автосуммирование.

4. В ячейке D1 будет сумма по Строке 1 (700).

5. Выделите ячейку D1 и протащите мышью до D6 (суммирование по строкам размножено).

6. Выделите столбец А и нажмите кнопку Автосуммирование.

7. В ячейке А7 будет суммирование по столбцу.

8. Содержимое ячейки перетащите до ячейки D7.

Упражнение № 9

Введите таблицу чисел:

	А	B	C	D	E

Требуется подсчитать сумму строк 1, 2, 3 для каждого столбца, введя одну единственную формулу, для этого:

1. Выделите диапазон А4:Е4.

2. Введите формулу:

=A1:E1+A2:E2+A3:E3

3. Нажмите сочетание клавиш CTRL+SHIFT+ENTER.

4. EXCEL в строке формул выведет: {=A1:E1+A2:E2+A3:E3}, а в диапазоне A4:E4 будет записан результат суммирования.

5. Щелкните левой кнопкой мыши в поле Рабочего листа.

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

Упражнение № 10

Пусть задана таблица чисел:

	A	B	C	D

 

1. Нам необходимо найти произведение столбцов (А+В)*С, а результат записать в столбец D.

2. Создайте приведенную выше таблицу.

3. Выделите диапазон D1:D4.

4. Запишите формулу: =(А1:А4+В1:В4)*С1:С4

5. Нажмите сочетание клавиш: CTRL+SHIFT+ENTER

В первых двух задачах мы имели множественный результат. Формула массива позволяет подсчитать ОДИН результат на множестве значений.

Задача № 4

По данным таблицы предыдущей задачи найти сумму произведений столбцов А*В+А*С+В*С.

1. Выделите любую ячейку (например, Е5).

2. Запишите формулу:

=А1:А4*В1:В4+А1:А4*С1:С4+В1:В4*С1:С4

3. Нажать сочетание клавиш: CTRL+SHIFT+ENTER

Упражнение № 11

Использование констант в массиве

В месяце 22 рабочих дня. Необходимо подсчитать фактический заработок работников по формуле:

Начислено=Ставка / К-во рабочих дней в месяце * Отработано дней

Исходные данные представлены в таблице:

	A	B	C	D	E	F
	Cтавка		Количество дней в месяце
	ФИО	Отработано дней	Начислено
	Заводов А.К.
	Степанов Д.Г.
	Адамов П.В.
	Черный Т.М.

 

Для подсчета фактического заработка:

1. Выделите диапазон C4:C7.

2. Запишите формулу: =B1/D1*B4:B7

3. Нажмите сочетание клавиш: CTRL+SHIFT+ENTER

4. Построится формула: {=B1/D1*B4:B7}

Некоторые правила для формулы массива.

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

2. Для фиксации формулы массива надо нажать CTRL+SHIFT+ENTER.

3. Нельзя самим вводить фигурные скобки. Иначе EXCEL поймет это как текст.

4. Для изменения формулы массива надо выделить весь массив и активизировать формулу массива.

5. После изменения формулы нажать CTRL+SHIFT+ENTER.

Упражнение № 12

Сохранение файла

1. В меню Файл (File) выберите команду Сохранить как... (Save as...).

2. В появившемся диалоговом окне «Сохранение документа» выберите свою папку.