Что такое функция рабочего листа

Опубликовано: 17.09.2024

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

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

Excel содержит массу клеточных функций, но прежде следует обратить внимание на следующие (сгруппированы по категориям):

 СУММ(), ОКРУГЛ(), ОКРВВЕРХ(), ОКРВВЕРХ(), ОТБР();

 ВЫБОР(), ПОИСКПОЗ(), ГПР(), ВПР(), ИНДЕКС(), СЧЁТ(), СЧЁТЕСЛИ(),

СУММЕСЛИ() МИН(), МАКС();

 СЕГОДНЯ(), ДЕНЬНЕД(), ГОД(), МЕСЯЦ(), ДЕНЬ(), ДАТАМЕС(),

 функции работы с массивами (раздел 4.15).

Замечания.

1. Некоторые из перечисленных функций не входят в ядро Excel и становятся доступны только при загрузке дополнительного Пакета анализа из меню Сервис+Надстройки. Все такие функции помечены в тексте двумя галочками.

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

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

4.1. Функция суммирования

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

Функция имеет следующий формат:

СУММ(<число1>;<число2>; . ) – возвращает сумму чисел, входящих в список аргументов. Список может содержать до 30 элементов. Если в суммируемом блоке встречаются нечисловые элементы, они участвуют в суммировании как нули.

Выражение, содержащее функцию суммирования, можно ввести с клавиатуры, а можно с помощью пиктограммы Автосуммирование  (см. раздел 2.4) или комбинации клавиш Alt+=.

Функция суммирования аналогична простому перечислению слагаемых со знаком плюс. Следующие выражения в Excel совершенно эквивалентны: =СУММ(A1:A3) и =A1+A2+A3. В каких же случаях следует пользоваться функцией суммирования, а в каких сложением? Функция СУММ() удобна, если слагаемых много и они находятся в смежных ячейках. Очевидно, что гораздо проще набрать выражение СУММ(D1:D400), нежели адреса четырехсот слагаемых D1+D2+D3+ … + D400. Кроме того, при большом числе элементов это может оказаться технически невозможным, поскольку длина клеточного выражения ограничена. Имеется еще одно отличие. Если среди слагаемых имеется нечисловой элемент, он будет пропущен функцией СУММ(…), но приведет к ошибке при сложении через знак плюс.

Другое удобство заключается в том, что функция автоматически реагирует на изменение (включение/удаление строк в диапазоне суммирования) размеров таблицы. На рис. 4.1-1 приведены результаты суммирования данных из области А1:А2 в числовом и формульном виде с использованием обеих формул. Они одинаковы (результат равен 6).

/>1

Рис. 4.1-1 Рис. 4.1-2

На рис. 4.1-2 в диапазон суммирования между первой и второй строками вставлена строка, содержащая цифру 5. Видим, что результат, полученный с помощью функции СУММ(), правильный, поскольку функция автоматически учла новое слагаемое (выражение =СУММ(A1:A2) изменилось на =СУММ(A1:A3)). Формула же =А1+А2 осталась прежней и, как следствие, результат неверный. Такое адаптивное поведение вообще характерно для любых функций Excel – они, в основном, правильно учитывают изменения в структуре таблиц.

Однако здесь имеются два исключения – вставка строки непосредственно над областью обработки (*) и непосредственно над строкой (**), содержащей суммы (рис. 4.1-3).

/>1

/>1

/>3

/>3

Функция СУММ() не откликается на эти действия, продолжая считать своими аргументами только те клетки, которые были аргументами и до вставок (на рисунке заштрихованы), игнорируя новые строки. Таким образом, пользователю в каждом подобном случае предстоит редактировать итоговую строку, что, конечно, очень обременительно. В нашем примере в А6 придется изменить выражение =СУММ(A3:A4) на =СУММ(A2:A5).

Что можно предпринять для учета этих обстоятельств? Нужно сделать так, чтобы крайние верхняя и нижняя строки перестали быть таковыми.

1. Неудобств, связанных с вставкой данных над областью суммирования, можно легко избежать, если включить в нее заголовок столбца, т.е. изменить формулу в A4 на =СУММ(A1:A3). Такой заголовок обязательно должен быть нечислового типа, иначе он будет участвовать в суммировании. Если же он содержит числа, можно поступить иначе – непосредственно под заголовком, но выше собственно области данных включить пустую строку, участвующую в суммирования. Если затем такую строку скрыть, то внесение новой строки вверху видимого диапазона суммирования повлечет правильную автоматическую коррекцию итоговой функции.

2. Для корректной вставки строки непосредственно над строкой, где формируется сумма, также целесообразно иметь пустую строку, включенную в область суммирования. В этом случае добавление новой строки над суммой (точнее над пустой строкой) не потребует какой-либо правки итоговой функции. Неудобством является сама пустая строка – она может попасть в готовый напечатанный документ.

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

 Начиная с версии Excel-2000 предусмотрен механизм, во многом снимающий эту проблему. Соответствующая установка имеется в меню Сервис+Параметры+вкладка Правка. Если здесь установить флажок Расширять форматы и формулы в списках, то дополнение таблицы в любом ее месте влечет автоматическое копирование в новую строку/столбец и форматов и формул из предыдущих строк/столбцов. Чтобы это свойство проявилось нужно только, чтобы повторяющиеся форматы и формулы были, по крайней мере, в трех из пяти предшествующих рядах таблицы. При этом не только автоматически добавляются формулы в заполняемой строке, но и корректируются итоговые выражения, находящиеся под/справа таблицы. Однако следует отметить, что указанный механизм может не работать в случаях сложных итоговых функций и в самом начале таблицы.

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

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


Рис. 1. Активная ячейка имеет полужирное начертание, выполненное с помощью условного форматирования

Получение информации о форматировании ячейки

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

Предупреждение. Эти функции не всегда обновляются автоматически — изменение форматирования не приводит к пересчету формул Excel. Чтобы вызвать глобальный пересчет формул (и обновить все пользовательские функции), нажмите клавиши <Ctrl+Alt+F9>. В функцию можно также добавить следующий оператор: Application.Volatile. При наличии этого оператора пересчет функции производится после нажатия клавиши <F9>.

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

Следующая функция возвращает ИСТИНА, если используемая в качестве аргумента ячейка выделена курсивом.

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

Следующая функция возвращает целое число, соответствующее индексу цвета заливки ячейки. Если ячейка не имеет заливки, то функция возвращает значение 4142. Эта функция не может использоваться для определения цветов заливки таблиц (которые создаются с помощью команды Ctrl+T) или сводных таблиц. В подобных случаях воспользуйтесь объектом DisplayFormat.

Беседа с рабочим листом

Функция Saylt применяет синтезатор речи Excel для озвучивания аргумента.

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

=ЕСЛИ(СУММ(А:А)>25000;Saylt( " Цель достигнута " ))

Если сумма значений в столбце А превышает 25 000, вы услышите синтезированный голос, сообщающий о том, что цель достигнута. Метод Speak можно также включить в конец длинной процедуры, и компьютер известит вас о том, что выполнение процедуры завершено.

Отображение даты сохранения файла или вывода файла на печать

Рабочая книга Excel содержит несколько встроенных свойств документа, к которым можно получить доступ с помощью свойства BuiltinDocumentProperties объекта Workbook. Следующая функция возвращает дату и время последнего сохранения рабочей книги.

К свойствам можно обратиться по номеру, например, BuiltinDocumentProperties(5), или по имени, как в примере выше. Получить перечень свойств можно с помощью процедуры BDPNames(), код которой приведен в приложенном Excel-файле.

Значения, возвращаемые этой функцией, совпадают со значениями даты и времени, которые отображаются в разделе Связанные даты после выбора команды Файл –> Сведения (рис. 2). Обратите внимание, что на значения даты и времени оказывает влияние свойство AutoSave. Поэтому время последнего сохранения необязательно имеет отношение ко времени сохранения файла пользователем.


Рис. 2. Сведения о рабочей книге; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Не ко всем свойствам можно получить доступ с помощью BuiltinDocumentProperties. Например, при попытке получить доступ к свойству Number of Bytes, указывающему размер файла, будет генерироваться ошибка.

Основы иерархии объектов

Объектная модель Excel представляет собой определенную структуру: одни объекты содержатся в других объектах. На вершине этой иерархии находится объект Application. Excel содержит другие объекты, в которые, в свою очередь, вложены более низкоуровневые объекты и т.д. Следующая иерархия показывает, как в этой структуре представлен объект Range.

Объект Application
Объект Workbook
Объект Worksheet
Объект Range

Следующая функция получает один аргумент (диапазон) и возвращает имя рабочего листа, который содержит указанный диапазон. При этом используется свойство Parent объекта Range. Свойство Parent возвращает объект, содержащий объект Range.

Следующая функция возвращает название рабочей книги для конкретной ячейки. Обратите внимание, что эта функция использует свойство Parent дважды. Первое свойство Parent возвращает объект Worksheet, а второе свойство Parent возвращает объект Workbook.

Следующая функция переносит это упражнение на следующий логический уровень, обращаясь к свойству Parent трижды. Такая функция возвращает имя объекта Application для заданной ячейки. Указанная функция всегда будет возвращать значение Microsoft Excel.

Подсчет количества ячеек между двумя значениями

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

Оператор Application.Volatile вызывает выполнение функции всякий раз, когда пересчитываются формулы на рабочем листе. Оператор Rows.Count возвращает количество строк на рабочем листе (используется именно он, а не жестко заданное значение, из соображений совместимости; новые версии Excel могут включать большее количество строк на рабочем листе). Ссылка rng.Column возвращает номер столбца левой верхней ячейки в аргументе rng. Благодаря ссылке rng.Parent функция работает корректно, даже если аргумент rng ссылается на другой лист или рабочую книгу. Метод End (с аргументом xlUp) эквивалентен переходу к последней ячейке столбца и нажатию <End> и <Т>. Функция IsEmpty проверяет, пуста ли ячейка. Если ячейка пуста, функция возвращает пустую строку. Без этого оператора пустой ячейке соответствовал бы результат 0.

Код функции LastlnRow можно найти в модуле VBA приложенного Excel-файла.

Соответствует ли строка шаблону

Следующая функция возвращает значение ИСТИНА, если строка соответствует заданному шаблону. Функция представляет собой «оболочку», позволяющую использовать в формулах оператор VBA Like.

Функция IsLike принимает два аргумента: text — текстовая строка или ссылка на ячейку, содержащую текстовую строку; pattern — строка, содержащая групповые символы согласно таблице (рис. 3).


Рис. 3. Групповые символы

Представленная ниже формула возвращает ИСТИНА, так как * соответствует любому количеству символов. Она возвращает ИСТИНА, если первый аргумент — любой текст, начинающийся с g.

=IsLike( " guitar " , " g* " )

=IsLike( " Unit1 " , " Unit? " )

Следующая формула возвращает ИСТИНА, так как первый аргумент является одним из символов списка во втором аргументе.

=IsLike( " а " , " [aeiou] " )

Следующая формула возвращает ИСТИНА, если ячейка А1 содержит один из символов: а, е, i, о, u, А, Е, I, О, U. При использовании функции ПРОПИСН (UPPER) в аргументе функция становится нечувствительной к регистру.

=IsLike(ПРОПИСН(А1), ПРОПИСН( " [aeiou] " ))

Следующая формула возвращает ИСТИНА, если в ячейке А1 находится значение, начинающееся с 1 и состоящее ровно из трех цифр (т.е. любое целое число от 100 до 199).

Возвращение из строки n-го элемента

ExtractElement — специальная функция рабочего листа, которая извлекает элемент из текстовой строки.

В этой процедуре используется VBA-функция Split, возвращающая массив констант, из которого состоит текстовая строка. Массив начинается с нулевого элемента (а не с первого), поэтому текущий элемент имеет индекс n – 1.

Например, если ячейка содержит следующий текст " 123-456-789-0133-8844 " , вы можете использовать функцию ExtractElement для извлечения любых подстрок между дефисами. Следующая формула возвращает 0133, т.е. четвертый элемент в строке.

Функция ExtractElement принимает три аргумента:

  • txt — текстовая строка, из которой извлекается подстрока (символьная строка или ссылка на ячейку);
  • n — целое число, представляющее номер извлекаемого элемента;
  • Separator — отдельный символ, используемый как разделитель.

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

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

Функция SpellDollars возвращает текст, в который преобразуется исходное число – сумма в долларах и центах. Например, формула =SpellDollars(23,45) возвращает строку " двадцать три и 45/100 доллара " (рис. 4). Обратите внимание, что отрицательные числа заключаются в круглые скобки. Код функции SpellDollars приведен в приложенном Excel-файле.


Рис. 4. Примеры использования функции SpellDollars

Универсальная функция

Можно сделать так, чтобы одна функция рабочего листа работала как несколько функций. StatFunction имеет два аргумента: диапазон (rng) и операция (ор). В зависимости от значения аргумента ор функция возвращает значение, вычисленное с помощью одной из следующих функций Excel: СУММ, СРЗНАЧ, МЕДИАНА, МОДА, СЧЁТ, МАКС, МИН, ДИСП, СТАНДОТКЛОН. Например, результат формулы =StatFunction($A$33:$A$37;C36) зависит от содержимого ячейки С36, в которой должна быть текстовая строка с именем одной из допустимых функций. В нашем случае – СЧЁТ. Код функции StatFunction приведен в модуле VBA приложенного Excel-файла.


Рис. 5. Примеры использования функции StatFunction

Функция SheetOffset

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

Функция VBA SheetOffset обеспечивает установку относительных ссылок на рабочие листы. Например (рис. 6), можно сослаться на ячейку А1 следующего рабочего листа с помощью такой формулы: =SheetOffset(1; А1). Первый аргумент представляет лист и может быть положительным, отрицательным или нулевым. Второй аргумент должен быть ссылкой на одну ячейку. Можете скопировать эту формулу в другие листы, и в скопированных формулах будет использована относительная ссылка.

ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL. ВЫЧИСЛЕНИЯ НА РАБОЧЕМ ЛИСТЕ. ФУНКЦИИ РАБОЧЕГО ЛИСТА

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

По умолчанию Excel выполняет пересчет всегда, когда изменения воздействуют на значения ячеек. Если пересчитывается достаточно много ячеек, в левой части строки состояния появляются слова «Расчет ячеек» и некоторое число. Число показывает процент выполненного перерасчета ячеек. Процесс пересчета можно прервать. При вводе какой-либо команды и значения в ячейку во время выполнения пересчета Excel приостановит обновление вычислений и продолжит его, когда пользователь закончит операцию.

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

Циклическая ссылка — это формула, которая зависит от своего собственного значения. При обнаружении циклической ссылки Excel выдает сообщение об ошибке. Многие циклические ссылки могут быть разрешены. Для установки этого режима следует установить флажок Итерации на вкладке Вычисления команды Параметры меню Сервис. В этом случае Excel пересчитывает заданное число раз все ячейки во всех открытых листах, которые содержат циклическую ссылку. Если установлен флажок Итерации, можно задать предельное число итераций (по умолчанию 100) и относительную погрешность (по умолчанию 0,001). Excel выполняет пересчет указанное предельное число раз или до тех пор, пока изменение значений между итерациями не станет меньше заданной относительной погрешности. При использовании циклических ссылок целесообразно установить ручной режим вычислений. В противном случае программа будет пересчитывать циклические ссылки при каждом изменении значений в ячейках.

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

В общем виде любая функция может быть записана в виде:

Существуют следующие правила ввода функций:

1. Имя функции всегда вводится после знака «=».

2. Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.

3. Между именем функции и знаком « ( » пробел не ставится.

4. Вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.

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

Не нашли то, что искали? Воспользуйтесь поиском:

Рабочие листы в Excel

Рабочий лист представляет собой набор ячеек, в которых вы храните и обрабатываете данные. По умолчанию каждая книга Excel содержит три листа (в Excel 2010 и более ранних версиях).

Как выбрать рабочий лист

Когда вы открываете Excel, программа автоматически открывает лист с названием Sheet1. Название листа видно на ярлычке в нижней части окна документа.

Примечание переводчика: В русифицированной версии Excel листы называются – Лист1, Лист2 и т.д.


Для выбора какого-либо другого листа в книге, просто кликните по ярлычку Sheet2 или Sheet3.

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

По умолчанию рабочие листы названы Sheet1, Sheet2 и Sheet3. Чтобы дать листу более информативное имя, следуйте инструкции ниже:

  1. Кликните правой кнопкой мыши по ярлычку листа Sheet1.
  2. Выберите Rename (Переименовать).



Введите, к примеру, “Sales 2010”.

Как вставить рабочий лист

Вы можете вставить столько листов, сколько хотите. Чтобы быстро вставить новый лист, нажмите на ярлычок Insert Worksheet (Вставить лист) в нижней части окна документа.



Как переместить рабочий лист

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

    Например, кликните по ярлычку Sheet4 и поместите его перед Sheet2.

Как удалить рабочий лист

Чтобы удалить лист, кликните правой кнопкой мыши по ярлычку листа и выберите команду Delete (Удалить).

    Для примера удалите листы Sheet4, Sheet2 и Sheet3.

Как скопировать рабочий лист

Представьте , что у вас есть продажи за 2010 год и вам нужно создать такой же лист на 2011 год, но с другими данными. Вы можете заново создать лист, но это отнимет очень много времени. Намного проще скопировать весь лист и изменить только значения.

  1. Кликните правой кнопкой мыши по ярлычку листа Sales 2010.
  2. Из контекстного меню выберите пункт Move or Copy (Переместить или скопировать).


Появится одноимённое диалоговое окно.
Кликните по Move to end (переместить в конец) и поставьте галочку напротив параметра Create a copy (Создать копию).



Нажмите ОК.

Примечание: Вы можете даже скопировать лист в другую книгу Excel, выбрав нужную книгу из раскрывающегося списка (см. диалоговое окно на рисунке выше).

Функции Excel (по категориям)

Функции упорядочены по категориям в зависимости от функциональной области. Щелкните категорию, чтобы просмотреть относящиеся к ней функции. Вы также можете найти функцию, нажав CTRL+F и введя первые несколько букв ее названия или слово из описания. Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.

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

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

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

Используйте эту функцию, когда нужно взять определенную строку или столбец и найти значение, находящееся в той же позиции во второй строке или столбце.

Эта функция используется для поиска данных в таблице или диапазоне по строкам. Например, можно найти фамилию сотрудника по его номеру или его номер телефона по фамилии (как в телефонной книге). Посмотрите это видео об использовании функции ВПР.

С помощью этой функции можно найти элемент в диапазоне ячеек, а затем вернуть относительное расположение этого элемента в диапазоне. Например, если диапазон a1: A3 содержит значения 5, 7 и 38, то функция формула = MATCH (7; a1: A3; 0) возвращает число 2, поскольку 7 — второй элемент диапазона.

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

Функция РАЗНДАТ вычисляет количество дней, месяцев или лет между двумя датами.

Эта функция возвращает число дней между двумя датами.

FIND и НАЙТИБ найдите одну текстовую строку в другой текстовой строке. Они возвращают номер начальной позиции первой текстовой строки из первого символа второй текстовой строки.

Эта функция возвращает значение или ссылку на него из таблицы или диапазона.

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

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

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

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

Возвращает отдельное значение вероятности биномиального распределения.

Возвращает одностороннюю вероятность распределения хи-квадрат.

Возвращает обратное значение односторонней вероятности распределения хи-квадрат.

Возвращает тест на независимость.

Соединяет несколько текстовых строк в одну строку.

Возвращает доверительный интервал для среднего значения по генеральной совокупности.

Возвращает ковариацию, среднее произведений парных отклонений.

Возвращает наименьшее значение, для которого интегральное биномиальное распределение меньше заданного значения или равно ему.

Возвращает экспоненциальное распределение.

Возвращает F-распределение вероятности.

Возвращает обратное значение для F-распределения вероятности.

Округляет число до ближайшего меньшего по модулю значения.

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

Возвращает результат F-теста.

Возвращает обратное значение интегрального гамма-распределения.

Возвращает гипергеометрическое распределение.

Возвращает обратное значение интегрального логарифмического нормального распределения.

Возвращает интегральное логарифмическое нормальное распределение.

Возвращает значение моды набора данных.

Возвращает отрицательное биномиальное распределение.

Возвращает нормальное интегральное распределение.

Возвращает обратное значение нормального интегрального распределения.

Возвращает стандартное нормальное интегральное распределение.

Возвращает обратное значение стандартного нормального интегрального распределения.

Возвращает k-ю процентиль для значений диапазона.

Возвращает процентную норму значения в наборе данных.

Возвращает распределение Пуассона.

Возвращает квартиль набора данных.

Возвращает ранг числа в списке чисел.

Оценивает стандартное отклонение по выборке.

Вычисляет стандартное отклонение по генеральной совокупности.

Возвращает t-распределение Стьюдента.

Возвращает обратное t-распределение Стьюдента.

Возвращает вероятность, соответствующую проверке по критерию Стьюдента.

Оценивает дисперсию по выборке.

Вычисляет дисперсию по генеральной совокупности.

Возвращает распределение Вейбулла.

Возвращает одностороннее P-значение z-теста.

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

Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.

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

Возвращает n-й, или ранжированный, элемент в множестве. Используется для возвращения одного или нескольких элементов в множестве, например лучшего продавца или 10 лучших студентов.

Определяет вычисленное множество элементов или кортежей путем пересылки установленного выражения в куб на сервере, который формирует множество, а затем возвращает его в Microsoft Office Excel.

Возвращает число элементов в множестве.

Возвращает агрегированное значение из куба.

Функция ЛИСТЫ и формулы для работы с другими листами в Excel

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

Формулы с использованием ссылок на другие листы Excel

Предположим у нас имеется фирма DecArt в которой работают сотрудники и им ежемесячно начисляется зарплата. У данной фирмы имеются сведения о среднемесячной зарплате в Excel, а данные по ней размещены на разных листах: на листе 1 размещены данные о зарплате, на листе 2 премия в процентах. Нам необходимо вычислить размер премии в рублях, при том чтобы данные эти были размещены на втором листе.

Для начала рассмотрим пример работы с листами в формулах Excel. Пример 1:

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

Функция ЛИСТЫ для подсчета количества листов в рабочей книге

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

В данной функции всего 1 аргумент – «Ссылка» да и то необязательный для заполнения. Если его не заполнять тогда функция возвращает общее количество листов, созданных в текущей рабочей книге файла Excel. При необходимости можно заполнить аргумент. Для этого в нем необходимо указать ссылку на рабочую книгу, в которой необходимо подсчитать общее количество листов, созданных в ней.

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

На рисунке ниже показано примерное количество листов:


Чтобы организовать подсчет всех листов, необходимо воспользоваться функцией ЛИСТЫ. Просто ставим знак равенства «=» и вписываем функцию, не заполняя ее аргументов в скобках. Вызов данной функции показан ниже на рисунке:

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

Таким образом мы узнали, что в нашей фирме имеется 12 документов, содержащихся в рабочей книге Excel. Этот простой пример наглядно иллюстрирует работу функции ЛИСТЫ. Данная функция может стать полезной для руководителей, офисных сотрудников, менеджеров по продажам.

Ссылки на другие листы в шаблонах документов

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

В результате у нас получился простейший шаблон для подсчета расходов на 1 банкет.

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

Самым распространённым среди пользователей является табличный процессор Microsoft Excel.

Microsoft Excel применяется в бухгалтерском учете, экономическом и финансовом анализе, банковском деле, в любом бизнесе, статистике, научных расчетах и еще во многих других областях.

Microsoft Excel умеет складывать, вычитать, умножать, делить и выполнять множество других операций.

Простейшим средством обработки числовых данных на компьютере является стандартная программа Windows «Калькулятор». Однако возможности Калькулятора ограничены, с его помощью трудно обрабатывать большие массивы числовых данных, статистические данные и т.д. Для этих целей используются электронные таблицы Microsoft Excel.

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

Основные функции электронных таблиц Excel:

· Ввод исходной информации в ячейки (форматирование таблиц);

· Редактирование и корректирование исходных данных;

· Обработка данных по формулам;

· Построение графиков и диаграмм;

· Статистическая обработка данных;

· Упорядочивание по признаку;

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

Электронная таблица – это просто прямоугольная таблица, состоящая из строк и столбцов.

Дата № школы Кол –во учащихся Кол – во отличн. Кол-во хорошистов Кол – во троечн. Кол – во двоечников
1 сентября № 30
1 сентября №29
1 сентября №40
1 сентября №6

Типы и формат данных.

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

Число представляет собой некую последовательность символов, в которую входят цифры, а также знаки «+», «-» в начале последовательности или «,» (как разделитель целой и дробной части). Например:

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

Формула представляет собой последовательность символов, которая начинается со знака «=». В формулу могут входить данные разного типа (числа, адреса ячеек, функций), соединённые между собой знаками арифметических операций. Например: =А4+С5*В7.

Набираемая в ячейку формула отображается в строке формул, а в ячейку после нажатия клавиши «Enter» заносится результат выполнения данной формулы.

В формуле могут находиться:

ü знаки арифметических действий: +, -, *, /, ^ (знак возведения числа в степень), знак %;
ü числа, строки (они берутся в кавычки);
ü ссылки на ячейки и диапазоны ячеек (как на текущем листе, так и на других листах книги) для определения порядка вычислений, скобки;
ü встроенные функции.

В Excel имеется большое количество функций, с помощью которых можно проводить вычисления и другие действия, относящиеся к разным областям знаний. При использовании встроенной функции после знака = следует ввести ее имя, а затем в скобках аргументы функции — данные, которые используются в расчетах. Аргументами функции могут быть числа, ссылки на ячейки или диапазоны ячеек, а также другие встроенные функции (они называются вложенными). Рассмотрим конкретные примеры:

ü =А2+В2 — сложение значений двух ячеек;
ü =А1*0,8 — умножение числа из ячейки А1 на 0,8;
ü =D1^2+1 — возведение числа из ячейки D1 в квадрат и прибавление единицы к результату;
ü =СУММ(А1: А5) — суммирование значений из диапазона ячеек А1: А5. Это пример использования встроенной функции. Здесь СУММ — имя функции, А1: А5 — диапазон ячеек, ее единственный аргумент, заключенный в скобки;
ü =МУМНОЖ(B1:B2;B7:C7) — вычисление произведения матриц B1:B2 и B7:C7. Эта функция имеет два аргумента, которые являются массивами данных из выделенных диапазонов. При наличии у функции нескольких аргументов они отделяются друг от друга точкой с запятой.

В качестве аргументов функций вы можете использовать ссылки на ячейки и диапазоны на текущем и других листах. В последнем случае перед адресом ячейки или диапазона следует ввести название листа, отделенное штрихами, и поставить разделитель !, например «Лист1»! В2, «Лист 3»! А1: С4. Штрих вводят, нажав клавишу Э при активной английской раскладке.

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

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

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

Часто применяемые на практике функции вынесены в меню кнопки , которая находится в группе Редактирование на вкладке Главная.

Простейшие расчеты

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

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

Среднее — вызывает функцию =СРЗНАЧ(), с помощью которой можно подсчитать арифметическое среднее диапазона ячеек (просуммировать все данные, а затем разделить на их количество.

Число — вызывает функцию =СЧЕТ(), которая определяет количество ячеек в выделенном диапазоне.

Максимум — вызывает функцию =МАКС(), с помощью которой можно определить самое большое число в выделенном диапазоне.

Минимум — вызывает функцию =МИН() для поиска самого маленького значения в выделенном диапазоне.

Дана электронная таблица:

В ячейку D2 ввели формулу =(А2*В1 +С1). В результате в ячейке D2 появится значение:

D2=(10*2+4)=24

В ячейке D2 записана формула: =A2*(B2+C2). Ее скопировали в ячейку D3. Какое значение будет выведено в ячейке D3?

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

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

Вы можете отказаться от редактирования ячейки, даже если уже начали выполнять его. Для этого просто нажмите клавишу Esc. При этом в ячейке будут восстановлены исходные данные. Для отмены уже совершенного действия нажмите стандартное сочетание клавиш Ctrl+Z или кнопку Отменить на Панели быстрого доступа.

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

Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными. До сих пор рассказывалось об относительных ссылках на ячейки и диапазоны, которые состоят только из номера строки и буквы столбца, например В2 или D4:D8.

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

Абсолютные адреса.

Абсолютная ссылка записывается с символом $ перед буквенной и числовой частью.Адрес ячейки при копировании формулы не изменяется ($B$3)

Использование относительных адресов в формулах иногда становится источником ошибок. Чтобы отменить автоматическое изменение адресов данной ячейки, можно назначить ей абсолютный адрес. Для этого необходимо поставить перед номером столбца и (или) перед номером строки знак доллара «$». Знак «$», указанный перед номером столбца, означает, что этот номер не будет изменяться при операциях копирования формул, вставки и удаления.

Например, в адресе $A5 не будет меняться номер столбца, в адресе B$7 – не будет меняться номер строки, в адресе $D$10 не будет меняться ни адрес строки, ни адрес столбца.

Текст. Если последовательность символов не является ни числом, ни формулой, то она считается текстом и не подвергается каким-либо преобразованиям. Например: 345,78 – это число, а 345.78 – это текст; или а1237 – это текст,A7+B8*4– это текст, так как отсутствует символ «=».

Основные форматы

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

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

Числовой формат является наиболее универсальным. В отличие от общего формата, в числовом формате можно задать количество отображаемых цифр дробной части (число десятичных знаков). При отображении дробных чисел выполняется округление в соответствии с заданным количеством цифр дробной части. Например, если установлен формат с отображением двух чисел дробной части, то число 567,897 будет представлено как 567,90. Это же число будет отображено как 568, если задать формат без отображения цифр дробной части (установить значение счетчика Число десятичных знаков равным нулю).


Выбрав формат Числовой, можно настроить его характеристики

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

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

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

Если в ячейке таблицы находится дата, то можно изменить формат ее отображения.


Способ отображения даты нужно выбрать в списке Тип

Различают полный и сокращенный форматы представления даты. Полный формат отражает день, месяц и год. В сокращенном формате может быть представлен день и месяц, иногда — только месяц или день. Еще раз следует обратить внимание на символ-разделитель, который используется при записи дат. В России — это точка.

При отображении содержимого ячейки в процентном формате реальное значение ячейки умножается на 100, и после числа выводится знак процента. Например, если в ячейку записать число 0,2 и установить формат Процентный, то в этой ячейке будет отображено 20,00 %.

Выбор ячеек.

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

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

2. Для выбора целых столбцов или строк можно использовать маркеры строк и столбцов по краям рабочей области.

3. Чтобы выбрать весь рабочий лист нужно щелкнуть на кнопке выделения всей таблицы (в верхнем левом углу рабочей области).

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

Автозавершение

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

Автозавершение при вводе текста

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

Ошибки в формулах

Иногда после введения формулы в ячейке вместо результата появляется текстовое сообщение об ошибке. Это связано с тем, что при проведении вычислений система столкнулась с каким-либо противоречием. Вот список наиболее часто встречающихся сообщений в ячейках:

Операции с ячейками

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

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

2. Для того чтобы реально удалить ячейки выбранного диапазона (что сопровождается изменением структуры таблицы), надо выбрать диапазон и дать команду Правка4Удалить. При этом открывается диалоговое окно Удаление ячеек, в котором можно выбрать направление смещения ячеек, занимающих освобождающееся место. Создать в таблице «дырку», в которую невозможен ввод данных нельзя.

3. По команде Правка4Копировать или Правка4Вырезать ячейки выбранного диапазона обводятся пунктирной рамкой. Даже при вырезании ячеек их содержимое продолжает временно хранить в таблице.

4. Для вставки ячеек, копируемых из буфера обмена, надо сделать текущей ячейку в верхнем левом углу области вставки и дать команду Правка4 Вставить. Ячейки из буфера обмена вставляются в указанное место. Если выполняется операция перемещения, то после вставки ячейки, из которых перемещаются данные, очищаются.

5. Копирование и перемещение ячеек можно также производить методом перетаскивания. Для этого надо установить указатель мыши на границу текущей ячейки или выбранного диапазона. После того как он примет вид стрелки, можно произвести перетаскивание. Если при перетаскивании использовать правую кнопку мыши, то после ее отпускания откроется специальное меню, позволяющее выбрать производимую операцию.

Запуск редактора Paint

Чтобы запустить редактор нужно выполнить команду:

Графический редактор Paintодновременно может работать только с одним документом, поэтому окно документа является частью окна программы.

Для завершения работыс редактором выполнить команду:меню

тема: Электронные таблицы. Назначение и использование.

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

Самым распространённым среди пользователей является табличный процессор Microsoft Excel.

Microsoft Excel применяется в бухгалтерском учете, экономическом и финансовом анализе, банковском деле, в любом бизнесе, статистике, научных расчетах и еще во многих других областях.

Microsoft Excel умеет складывать, вычитать, умножать, делить и выполнять множество других операций.

Простейшим средством обработки числовых данных на компьютере является стандартная программа Windows «Калькулятор». Однако возможности Калькулятора ограничены, с его помощью трудно обрабатывать большие массивы числовых данных, статистические данные и т.д. Для этих целей используются электронные таблицы Microsoft Excel.

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

Основные функции электронных таблиц Excel:

· Ввод исходной информации в ячейки (форматирование таблиц);

· Редактирование и корректирование исходных данных;

· Обработка данных по формулам;

· Построение графиков и диаграмм;

· Статистическая обработка данных;

· Упорядочивание по признаку;

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

Электронная таблица – это просто прямоугольная таблица, состоящая из строк и столбцов.

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

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

Некоторые функции, такие как СУММ (SUM), SIN (SIN) и ФАКТР (FACT), являются эквивалентами длинных математических формул, которые можно создать самим. Другие функции, такие как ЕСЛИ (IF) и ВПР (VLOOKUP), в виде формул реализовать невозможно.

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

Быстро получить информацию о функциях можно также с помощью кнопки Вставка функции.

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например, СУММ (SUM) или СРЗНАЧ (AVERAGE) описывает операцию, которую эта функция выполняет. Аргументы функции Excel задают значения или ячейки, используемые функцией. Например, в следующей формуле СУММ – это имя функции, а С3:С5 – ее единственный аргумент. Эта формула суммирует числа в ячейках С3, С4 и С5:

Некоторые функции, такие как ПИ (PI) и ИСТИНА (TRUE), не имеют аргументов. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки:

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

В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, следующая функция имеет три аргумента, но суммирует числа в 29 ячейках (первый аргумент, А1:А5, ссылается на диапазон пяти ячеек от А1 до А5 и т.д.):

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

Комбинацию функций можно использовать для создания выражения, которое Excel сводит к единственному значению и интерпретирует его как аргумент. Например, в следующей формуле: SIN(A1*ПИ()) и 2*COS(A2*ПИ()) – это выражения, которые вычисляются и используются в качестве аргументов функции СУММ:

Типы аргументов

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

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

Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 327, 209 и 176:

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

В качестве аргумента функции могут использоваться текстовые значения. Например:

=ТЕКСТ(ТДАТА();«Д МММ ГГГГ»).

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

Аргументы ряда функций могут принимать только логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например, первый аргумент функции ЕСЛИ (IF) в следующей формуле является логическим выражением, которое использует значение:

=ЕСЛИ(А1=ИСТИНА, «Новая», «Старая»)& «цена».

Если значение в ячейке А1 равно ИСТИНА, то выражение А1=ИСТИНА возвращает значение ИСТИНА, и функция ЕСЛИ возвращает строку Новая, а формула в целом возвращает текстовое значение Новая цена.

В качестве аргумента функции можно указать имя диапазона. Например, если выбрать команду Присвоить подменю Имя меню Вставка и назначить диапазону С3:С6 имя Получено, то для вычисления суммы чисел в ячейках С3, С4, С5 и С6 можно использовать формулу:

Аргументом функции может быть массив. Некоторые функции, такие как ТЕНДЕНЦИЯ (TREND) и ТРАНСП (TRANSPOSE) требуют задания массива аргументов. Другие функции не требуют задания массива, но могут использовать такие аргументы. Массивы могут содержать числовые, текстовые или логические значения.

В одной функции можно использовать аргументы различных типов. Например, в следующей формуле аргументами являются имя диапазона (Группа 1), ссылка на ячейку (A3) и числовое выражение (5*3), а сама формула возвращает единственное числовое значение:

Ввод функций в рабочем листе

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

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

В этом окне сначала выбирают категорию (или Полный алфавитный перечень) в списке Категория и затем в алфавитном списке Функция указывают нужную функцию. В качестве альтернативы после выбора категории можно щелкнуть на имени любой функции в списке Функция и нажать клавишу, соответствующую первой букве нужного имени. Чтобы ввести функцию, необходимо нажать кнопку ОK или клавишу Enter.

Excel введет знак равенства, имя функции и пару круглых скобок. Затем Excel откроет второе окно диалога Мастера функций (без строки заголовка).

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


Рис. 2.2. Окно диалога Мастер функций – шаг 1 из 2

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

После нажатия кнопки ОК или клавиши Enter созданная функция появится в строке формул.

Некоторые функции, такие как ИНДЕКС (INDEX) имеют несколько форм (вариантов задания аргументов). Если выбрать такую функцию в списке Функция, Excel откроет дополнительное окно диалога Мастера функций, как на рис. 2.2, в котором можно выбрать нужную форму функции.

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

Читайте также: