LINEBURG


<< Пред. стр.

страница 46
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

3 450 Цемент 450
5 3
Асбест
I I
6 2


Рис. 5.29. Функции категории Ссылки и массивы

6. Адрес ячеек или диапазонов ячеек — функция АДРЕС.
Адрес ссылки выдается в виде текста в двойных кавычках. Ссылка представ­
ляется в виде номера строки и столбца, на пересечении которых находится
ячейка. Ссылка может быть относительной или абсолютной в определенном
стиле (А1 или R1C1), включать имя листа рабочей книги.
Формула в ячейке А1 =АДРЕС(4;5;1;2;"Пример 11") дает ссылку на ячейку
'Пример 1Г!$Е$4. В формуле использованы параметры (слева направо): 4 —
номер строки, 5 — номер столбца, 1 — абсолютная ссылка, 1 — формат ссыл­
ки А1, Пример 11 — имя листа.
7. Значение из ссылки — функция ДВССЫЛ.
Ссылка задается в виде текстовой строки. Например, в ячейке А2 содержится
формула вида =ДВССЫП(АДРЕС(4; 5 ; 1; 1) ;1). Эта формула дает результат —
значение из ячейки Е4 (если лист не указан, используется текущий).
8. Определение числа строк в заданном диапазоне ячеек — функция ЧСТРОК.
Например, формула в ячейке A3 =ЧСТР0К(С2 :Е5) дает значение 4.
9. Определение числа столбцов в заданном диапазоне ячеек — функция
ЧИСЛСТОЛБ.
386 Глава 5. Электронная таблица Microsoft Excel 2000


Например, формула в ячейке А4 =ЧИСПСТ0ЛБ (С2 : Е5) дает значение 3.
10. Определение начального номера столбца ссылки (диапазона ячеек или име­
нованного блока ячеек) — функция СТОЛБЕЦ.
Например, формула в ячейке А5 =СТ0ЛБЕЦ(С2 : Е5) дает значение 3.
11. Определение начального номера строки ссылки (диапазона ячеек или имено­
ванного блока ячеек) — функция СТРОКА.
Например, формула в ячейке А6 =СТРОКА(С2:Е5) дает значение 2.
Формулы этой категории можно успешно комбинировать друг с другом. На­
пример, для именованного блока Блок, которому соответствует диапазон ячеек
С2:Е5, требуется определить значения начальной и конечной ячеек блока, в
данном случае — ячеек С2 и Е5. Результат записать в ячейки В1 и В2 соответ­
ственно.
Ф о р м у л а в ячейке В 1 : =ДВССЫЛ (АДРЕС(СТРОКА(Блок) ;СТ0ЛБЕЦ(Блок) ; 4 ; 1 ) ; 1 ) .
Формула определяет начальные координаты блока — номер строки и номер
столбца с помощью функций СТРОКА, СТОЛБЕЦ. С помощью функции АДРЕС
строится адрес начальной ячейки блока. С помощью функции Д С Ы опре­
ВС Л
деляется содержимое начальной ячейки блока Блок — ячейки С2. В данном
случае это число — 125.
Ф о р м у л а в я ч е й к е В2: =ДВССЫЛ(АДРЕС(СТРОКА(Блок) + ЧСТР0К(Блок)-1;
СТОЛБЕЦ(Блок) + ЧИСЛСТОПБ(Блок) - 1 ; 4 ; 1 ) ; 1 ) .
Формула вычисляет начальный номер строки блока — функция СТРОКА, чис­
ло строк в блоке — функция ЧСТРОК для определения номера последней
строки блока. Вычисляет начальный номер столбца блока — функция СТОЛБЕЦ,
число столбцов в блоке — функция ЧИСЛСТОЛБ для определения номера по­
следнего столбца в блоке.
С помощью функции АДРЕС строится адрес последней ячейки блока. С помо­
щью функции Д С Ы определяется содержимое этой ячейки — ячейки Е5.
ВС Л
В данном случае это число — 450.
12. Выбор по заданному номеру (индексу) объекта перечисления (диапазона
ячеек, блоков или значений из указанного списка констант) — функция
ВЫБОР.
Например, имеется ряд диапазонов ячеек: Блок и А2 :А6. В ячейке ВЗ вычисля­
ется число строк в выбранном диапазоне: =ЧСТР0К(ВЫБ0Р(2 ;Блок;А2 :А6)).
Результат — 5.
13. Получение значения из области ссылки по относительному номеру — функ­
ция ИНДЕКС.
Область ссылки может быть одномерной, двумерной, содержать несколько
диапазонов ячеек. Относительный номер строки и столбца в указанном диа­
пазоне ячеек является индексом ссылки. Например, первую ячейку диапазо­
на ячеек Блок можно определить как И Д К (Блок; 1; 1), последнюю ячейку
НЕ С
диапазона ячеек Блок можно определить как И Д К (Блок; 3 ; 4).
НЕС
В ячейке В4 введена формула =ИНДЕКС(Блок; 1; 1), результат формулы —
значение ячейки С2.
387
Базовые информационные технологии Microsoft Excel


14. Для определения позиции искомого значения в одномерном диапазоне яче­
ек — функция ПОИСКПОЗ.
Учитывается тип сопоставления:
О 1 — поиск наибольшего значения, которое не превосходит искомое (мас­
сив значений упорядочен по возрастанию);
О 0 — поиск первого равного искомому значения (массив значений в произ­
вольном порядке);
О - 1 — поиск наименьшего значения, которое превосходит искомое (массив
значений упорядочен по убыванию).
ВНИМАНИЕ
Функция ПОИСКПОЗ не различает регистры букв при сопоставлении текстов

Например, в ячейке В5 введена формула =ПОИСКПОЗ(1000; Е2 : Е5 ;0), которая
определяет позицию заданного числа — 1000 в диапазоне ячеек — Е2:Е5.
Результат поиска — номер позиции 3.
15. Просмотр данных в блоках ячеек — функция ПРОСМОТР.
Функция ПРОСМОТР обеспечивает различные режимы поиска:
О проверка наличия искомого значения в массиве (если значение существу­
ет, выводится само значение, в противном случае — сообщение об ошибке
#Н/Д — нет данных);
О поиск искомого значения в векторе просмотра и вывод соответствующего
ему значения из вектора результата.
Например, требуется по заданному значению кода материала — ячейка В1
определить соответствующее этому коду название материала. Коды материа­
лов представлены в ячейках С2:С5, названия — в ячейках D2:D5.
В ячейке В6 введена формула =ПР0СМ0ТР(В1 ;С2 :С5 ;D2:05), которая возвра­
щает значение из вектора результата (наименование материала) для найден­
ного в векторе просмотра (код материала) значения, в данном случае — Ас­
бест.
ВНИМАНИЕ
Перед выполнением функции ПРОСМОТР диапазон ячеек сортируется в порядке возрас­
тания значений вектора просмотра.

16. «Вертикальный» просмотр массива — функция ВПР.
Функция ВПР обеспечивает «вертикальный» просмотр — в первом столбце
блока ячеек с учетом типа сопоставления:
О ИСТИНА (или 1) — точное сравнение;
О ЛОЖЬ (или 0) — приближенное сравнение.
Если искомое значение найдено, отображается значение из указанного столб­
ца в текущей строке.
388 Глава 5. Электронная таблица Microsoft Excel 2000


Например, в ячейке F1 введена формула =ВПР(125;Блок;2; 1), которая воз­
вращает значение из столбца 2 текущей строки, в данном случае — Асбест.
17. «Горизонтальный» просмотр массива — функция ГПР.
Функция ГПР выполняет «горизонтальный» просмотр — в первой строке
блока ячеек с учетом типа сопоставления:
О ИСТИНА (или 1) — точное сравнение;
О ЛОЖЬ (или 0) — приближенное сравнение.
Если искомое значение найдено, отображается значение из указанной строки
текущего столбца.

ВНИМАНИЕ
Для функций ВПР, ГПР диапазон ячеек должен быть упорядочен по первому столбцу или
первой строке.

18. Выделение диапазона ячеек требуемой конфигурации — функция СМЕЩ.
Диапазон ячеек требуемой высоты и ширины может быть получен путем
смещения от указанной ссылки на заданное число строк и столбцов в нуж­
ном направлении.
Например, требуется выделить ячейки блока Блок, содержащие наименова­
ние материалов, начиная с материала Глина. В ячейки F2:F4 введен массив
формул вида: {=СМЕЩ(Блок;1;1;3;1)}. Задается смещение от начала блока
ячеек Блок на 1 строку вниз и на 1 столбец вправо. Выделяется диапазон
ячеек высотой 3 строки и шириной 1 столбец.
19. Транспонирование диапазона ячеек — функция ТРАНСП.
Для «разворота» на 90° блока ячеек используется функция ТРАНСП, которая
вводится как массив формул.
Например, требуется транспонировать исходный блок ячеек С2:Е5 в ячейки
A10:D12. В ячейки A10:D12 вводится формула массива {=ТРАНСП(С2 : Е5)}.
20. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Категория «Текстовые функции»
Функции данной категории обеспечивают работу с текстом, находящимся в ячей­
ках таблицы или вводимым в виде текстовых констант в формулы. Некоторые
встроенные функции категории Текстовые изучаются на конкретных примерах
(см. ниже).

Пример 12
Последовательность действий.
1. Открыть файл Примеры.XLS с помощью команды меню Файл • Открыть.
2. Вставить новый лист, если необходимо, с помощью команды меню Вставка •
Лист.
3. Выполнить переименование листа с помощью команды меню Формат • Лист •
Переименовать, лист — Текстовые.
389
Базовые информационные технологии Microsoft Excel


4. Заполнить ячейки А1:А4, начиная с А1, значениями:
О 0,234567
О 0,234567
О 17/06/2002
О 17/06/02
5. В ячейки В1:В4, начиная с В1, ввести формулы для преобразования числа
или даты в текст (рис. 5.30).

23р
= ТЕКСТ(А1."# 0 # р " )

= ТЕКСТ(й2"0 0#р") 023р

2002 год17 Июнь
= ТЕКСТ(АЗ "ГГГГ ""год"" ДЦ ММММ ")

= ТЕКС Т ( А 4 " М М М М - ГГ") Июнь-02


Рис. 5.30. Функции категории Текстовые

6. В ячейку С1 ввести формулу преобразования текста в число: =ЗНАЧЕН("0,23р.").
Формула дает результат 0,23. Это обратное преобразование функции ТЕКСТ.
7. В ячейку С2 ввести формулу сцепления текстовых строк:
=СЦЕПИТЬ("ПРИМЕР " ; " " ; "СЦЕПЛЕНИЯ СТРОК ТЕКСТА " ; " " ; " Д Л Я ВЫВОДА В
ОДНОЙ ЯЧЕЙКЕ"). В результате в ячейке С2 выводится строка текста «ПРИМЕР
СЦЕПЛЕНИЯ СТРОК ТЕКСТА ДЛЯ ВЫВОДА В ОДНОЙ ЯЧЕЙКЕ».
8. В ячейку СЗ ввести формулу для определения длины текста в ячейке С2:
=ДЛСТР(С2).
9. В ячейку С4 ввести формулу для определения первого вхождения в строку
текста в ячейке С2 сочетания букв «СТ» с учетом регистра, поиск вести с на­
чала строки текста: =НАЙТИ("СТ";С2;1). При поиске без учета регистра ис­
пользуется функция ПОИСК.
10. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Категория «Дата и время»
Даты и время могут представляться в числовом или текстовом формате. Функ­
ции данной категории обеспечивают работу с датой и временем, находящимися
в ячейках таблицы или вводимыми в виде констант в формулы. Некоторые
встроенные функции категории Дата и время изучаются на конкретных приме­
рах (см. ниже).

Пример 13
Последовательность действий.
1. Открыть файл Примеры.XLS с помощью команды меню Файл • Открыть.
2. Вставить новый лист, если необходимо, с помощью команды меню Вставка •
Лист.
3. Выполнить переименование листа с помощью команды меню Формат • Лист •
Переименовать, лист — Дата и время.
390 Глава 5 Электронная таблица Microsoft Excel 2000


4 Ввести в столбец А, начиная с ячейки А1, формулы для вычисление
О текущей даты 1 =СЕГОДНЯ(),
О текущей даты и времени =ТДАТА (),
О даты в числовом формате, аргументы функции задаются по частям (год,
месяц, день) =ДАТА(2002,6,17),
О перевод даты из текстового формата в числовой формат, аргумент задает­
ся как строка текста =ДАТАЗНАЧ("17 06 2002"),
О вычисление даты, отстоящей от указанной даты на определенное количе­
ство месяцев =ДАТАМЕС("17 06 2002",-6) Результат вычисления воз­
вращается в числовом формате, например, как значение 37424, что соответ­
ствует дате 17 12 01
О вычисление последней даты месяца, отстоящей от заданной даты на ука­
занное число месяцев =КОНМЕСЯЦА(ДАТА(2002,б,17),-6) Возвращает
значение 37256, что соответствует дате 31 12 01
О определение номера года, месяца и дня для даты, заданной в числовом
формате
=Г0Д(37424) дает год 2002,
=МЕСЯЦ(37424) дает месяц 6,
=ДЕНЬ(37424) дает число 17
О определение количества рабочих дней в заданном интервале дат с учетом
праздников, которые задаются в виде списка значений
ЧИСТРАБДНИ(ДАТА(2002,1,1),ДАТА(2002,12,31),Е20 Е28),
где Е20 Е28 — массив дат праздников Результат вычисления числа рабо­
чих дней для 2002 года — 252
О определение даты рабочего дня, отстоящего от указанной даты на опреде­
ленное количество рабочих дней вперед или назад
РАБДЕНЬ(ДАТА(2002,6,17).15.Е20 Е28)
Вычисляется дата, отстоящая от даты 17 06 2002 на 15 рабочих дней Дата
рабочего дня - 08 07 2002
О вычисление разницы количества лет, месяцев и дней между двумя кален­
дарными датами с помощью функции РАЗНДАТ с использованием различ-
, ных форматов "Y", "YM", "MD"
Например, дата начала трудового стажа — 02 10 1978, дата окончания трудо­
вого стажа - 12 09 2002
РАЗНДАТ(ДАТА(1978,10,2) ,ДАТА(2002,9,12), "Y") - вычисление целого чис­
ла лет В данном случае — 23
РАЗНДАТ (ДАТА (1978,10, 2) ,ДАТА(2002 , 9 , 1 2 ) , "YM") - вычисление целого чис­
ла месяцев В данном случае — 11
РАЗНДАТ (ДАТА (1978,10,2) , ДАТА (2002 ,9,12) , "MD") - вычисление целого чис­
ла дней В данном случае — 10


1
Используется таймер операционной системы, так называемая системная дата
391
Базовые информационные технологии Microsoft Excel


О Определение доли года для временного интервала с помощью функции
ДОПЯГОДА(ДАТА(2002;1;1);ДАТА(2002;6;17);0).
Интервал времени: 01.01.2002-17.06.20002 составляет величину 0,46111,
или 46,11 % длины года.
5. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Категория «Математические»
Это наиболее популярная категория встроенных функций, обеспечивающая раз­
нообразные вычисления.

Пример 14
Последовательность действий.
1. Открыть файл ПримерыЖБ с помощью команды меню Файл • Открыть.
2. Вставить новый лист, если необходимо, с помощью команды меню Вставка •
Лист.
3. Выполнить переименование листа с помощью команды меню Формат • Лист •
Переименовать, лист — Математические.
4. Ввести в столбец А, начиная с ячейки А1, формулы:
О определение знака выражения: =ЗНАК(-1234) дает - 1 , так как число отри­
цательное;
О округление числа до ближайшего целого нечетного числа:
=НЕЧЁТ(1бб, 666667) дает 167;
О округление числа до ближайшего целого четного числа:
=ЧЁТН(166, 666667) дает 168;
О округление числа до ближайшего меньшего целого числа:
=ЦЕЛ0Е(166, 666667) дает 166;
О вычисление целой части частного от деления:
=ЧАСТНОЕ(20*1000;120) дает 166;
О отбрасывание дробной части числа: =ОТБР(166,666667;0) дает 166;
О округление числа до ближайшего целого или до ближайшего кратного
указанному значению: =0КРВВЕРХ(166,666667; 10) вычисляет 170;
О округление числа до ближайшего меньшего по модулю целого числа:
=ОКРВНИЗ(20/120*1000;10) вычисляет 160;
О округление числа до указанного количества десятичных разрядов:
ОКРУГЛ(166,666667;3) вычисляет 166,667;
О округление числа до ближайшего по модулю большего целого:
=ОКРУГПВВЕРХ( 166,666667;3) вычисляет 166,667;
О округление числа до ближайшего меньшего по модулю целого:
0КРУГЛВНИЗ( 166,66667:3) вычисляет 166,666.
5. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.
392 Глава 5. Электронная таблица Microsoft Excel 2000


Категория «Информационные, проверка свойств
и значений»
Информационные функции предназначены для определения типа данных, хра­
нимых в ячейке. Возвращают значения ИСТИНА или ЛОЖЬ.

Пример 15
Встроенные функции категории Ссылки и массивы изучаются на конкретных
примерах (см. ниже). Последовательность действий.
1. Открыть файл Примеры.XLS с помощью команды меню Файл • Открыть.
2. Вставить новый лист, если необходимо, с помощью команды меню Вставка •
Лист.
3. Выполнить переименование листа с помощью команды меню Формат • Лист •
Переименовать, лист — Проверка.
4. Ввести в столбец А, начиная с ячейки А1, формулы:
О вычисление логической константы =ЕЛ0ГИЧ(5=5) дает ИСТИНА;
О проверка отсутствия данных (Нет Данных) в ячейке: =ЕНД(А1) дает ЛОЖЬ.
О проверка отсутствия текста в ячейке: =ЕНЕТЕКСТ(А1) дает ИСТИНА;
О проверка типа числа: четное/нечетное:
= ЕНЕЧЕТ(45/14) дает ИСТИНА; =ЕЧЕТ(45/14) дает ЛОЖЬ;
О проверка наличия ошибки любого типа, кроме #Н/Д:
= ЕОШ(5б*6/0) дает ИСТИНА;
О проверка наличия ошибки любого вида: =ЕОШИБКА(56*6/0) дает ИСТИНА;
О проверка отсутствия в ячейке какого-либо значения:
= ЕПУСТ0(В1) дает ИСТИНА, если В1 не содержит значения;
О проверка типа ссылки:
= ЕССЫЛКА(БазаДанных) дает ИСТИНА, если БазаДанных — имя блока;
О проверка текстового значения: =ЕТЕКСТ(А1) дает ЛОЖЬ, если в А1 — дру­
гое значение (например, логическая константа);
О проверка числового значения: =ЕЧИСЛО(35) дает ИСТИНА;
О возврат информации об операционной системе. Учитывается тип пара­
метра функции ИНФОРМ, который задается как текстовая строка в двой­
ных кавычках (табл. 5.11).
В ячейки А11-А20 ввести формулы для получения информации об операцион­
ной обстановке с помощью функции ИНФОРМ.

Таблица 5 . 1 1 . Параметры встроенной функции ИНФОРМ

Параметр Значение

directory Путь текущего каталога, установленного командой Сервис •
Параметры, вкладка Общие
memavail Количество свободной памяти в байтах
393
Базовые информационные технологии Microsoft ExceJ


Параметр Значение

Количество памяти, используемой для данных
memused
Количество активных рабочих листов текущей рабочей книги
numfile
Диапазон ячеек от начала листа до текущего положения курсора
origin
Текущая версия операционной системы в виде текста
osversion
recalc Текущий режим повторного вычисления; возвращается
«Автоматически» или «Вручную»
Версия Microsoft Excel
release
system Название операционной системы: Macintosh = «mac», Windows =
«pedos»
totmem Общее количество доступной памяти в байтах, включая уже
используемую память


О формирование недоступного значения в ячейке: =НД(), результатом функ­
ции является #Н/Д (нет данных);
О подсчет количества пустых ячеек в заданном диапазоне:
=СЧИТАТЬПУСТ0ТЫ(А1:В21);
О определение типа данных: =ТИП(16/0). Результат функции закодирован:
1 — тип данных число, 2 — тип данных строка, 4 — тип данных логиче­
ская константа, 16 — тип данных ошибка, 64 — тип данных массив.
О определение типа ошибки: =ТИП.ОШИБКИ(16/0). В табл. 5.12 приведены
коды ошибок, формируемые Microsoft Excel.
Таблица 5.12. Коды ошибок и их типы

Код Ошибка Код Ошибка
#имя?
1 5
#ПУСТО!
2 #ДЕЛ/0! 6 #ЧИСЛО!
#н/д
3 #ЗНАЧ! 7
#н/д
4 #ССЫЛ! Любое другое

<< Пред. стр.

страница 46
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign