Ссылки на ячейки из разных листов

1. На Листе 1 в ячейке А10 запишите 150.

2. На Листе 2 в ячейке А10 запишите 200.

3. В ячейку А12 на Листе 1 надо записать сумму этих двух ячеек.

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

5. Активизируйте ячейку А12 и запишите в строке формул: =

6. Выделите ячейку А10 на Листе 1 и наберите: +

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

8. Выделите ячейку А10 и нажмите Enter.

9. EXCEL вернет Вас в ячейку А12 Листа 1.

В строке формул будет: =A10+Лист2!A10

Упражнение № 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

Ссылка на ячейки из других книг

1. Создайте новую книгу. Для этого выполните команду:

Файл ® Создать…

2. На Листе 1 в ячейке А1 наберите 150.

3. Создайте другую книгу и в ячейку А1 этой книги введите 400.

4. Выполните команду: Окно ® Расположить…

5. В появившемся диалоговом окне «Расположение окон» выберите опцию: рядом, сверху вниз, слева направо, каскадом.

6. В ячейке А2 Книги 2 надо получить результат от сложения:

кн.2яч.А1 + кн.1яч.А1

7. В ячейке А2 Книги 2 набираем: =

8. Выделяем ячейку А1 Книги 2 и набираем: +

9. Переходим в Книгу 1, выделяем ячейку А1 и нажимаем клавишу Enter.

10. В строке формул будет записано:

=A1+[Книга1]Лист1!$A$1

Упражнение № 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 пробелами)

Использование имен в формулах

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

«Сальдо на конец месяца = Сальдо на начало месяца + поступление – выбытие»

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

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

2. Имена не должны иметь сходства со ссылками, например, Z$10 0 или R1C1.

3. Использование пробела запрещено. В качестве разделителей слов можно использовать символы подчеркивания и точки, например: «Первый.Квартал» или «Процентная_Ставка».

4. Имя может содержать не более 255 символов.

5. Имя может состоять из строчных или прописных букв, хотя Microsoft Excel их не различает. Например, если создано имя «Продажа», а затем в той же книге создано другое имя «ПРОДАЖА», то второе имя заменит первое.

Упражнение № 7 Создание имени

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

2. Запишите в ячейку А5:

12550,85 (это Цена за 1 единицу основного средства)

3. В ячейке А6 введите: 10 (это Количество)

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

=А5*А6 (Количество*Цена)

Порядок действий:

1. Выделите ячейку А5.

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

3. Запишите – Цена.

4. Нажмите ENTER.

5. Выделите ячейку А6.

6. Зайдите в текстовое поле имени и запишите – Количество.

7. Нажмите ENTER.

8. Выделите ячейку А7.

9. Зайдите в текстовое поле имени и запишите Сумма.

10. Нажмите ENTER.

11. Проверить работу формулы.

Если имеется несколько имен, то прокруткой открываем Список имен, выбираем нужное, курсор устанавливается на ячейку, которой присвоено данное имя.

Упражнение № 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 написано: