Содержание

Символы в экселе для формул

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

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

Примечание: Формулы в Excel начинаются со знака равенства.

Введите оператор. Например, для вычитания введите знак «минус».

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

Нажмите клавишу ВВОД. В ячейке с формулой отобразится результат вычисления.

Просмотр формулы

При вводе в ячейку формула также отображается в строке формул.

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

Ввод формулы, содержащей встроенную функцию

Введите знак равенства «=», а затем — функцию. Например, чтобы получить общий объем продаж, нужно ввести «=СУММ».

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

Скачивание книги «Учебник по формулам»

Подробные сведения о формулах

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

Формула также может содержать один или несколько таких элементов, как функции, ссылки, операторы и константы.

1. Функции. Функция ПИ() возвращает значение числа пи: 3,142.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы. Оператор ^ (крышка) применяется для возведения числа в степень, а * (звездочка) — для умножения.

По умолчанию Excel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, не более 16 384 столбцов), а строки — номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

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

В приведенном ниже примере функция СРЗНАЧ вычисляет среднее значение в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

3. Восклицательный знак (!) отделяет ссылку на лист от ссылки на диапазон ячеек.

Примечание: Если название упоминаемого листа содержит пробелы или цифры, его нужно заключить в апострофы (‘), например так: ‘123’!A1 или =’Прибыль за январь’!A1.

Различия между абсолютными, относительными и смешанными ссылками

При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.

Трехмерные ссылки нельзя использовать в формулах массива.

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

Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

Вставка или копирование. Если вставить листы между листами 2 и 6, Microsoft Excel прибавит к сумме содержимое ячеек с A2 по A5 на новых листах.

Удаление . Если удалить листы между листами 2 и 6, Microsoft Excel не будет использовать их значения в вычислениях.

Перемещение . Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.

Перемещение конечного листа . Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Удаление конечного листа . Если удалить лист 2 или 6, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. При использовании стиля R1C1 в Microsoft Excel положение ячейки обозначается буквой R, за которой следует номер строки, и буквой C, за которой следует номер столбца.

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

Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее

Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца

Относительная ссылка на строку, расположенную выше текущей ячейки

При записи макроса в Microsoft Excel для некоторых команд используется стиль ссылок R1C1. Например, если записывается команда щелчка элемента Автосумма для вставки формулы, суммирующей диапазон ячеек, в Microsoft Excel при записи формулы будет использован стиль ссылок R1C1, а не A1.

Чтобы включить или отключить использование стиля ссылок R1C1, установите или снимите флажок Стиль ссылок R1C1 в разделе Работа с формулами категории Формулы в диалоговом окне Параметры. Чтобы открыть это окно, перейдите на вкладку Файл.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

Операции сравнения (Табл.2.3) используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

Текстовая операция & (амперсант) (Табл. 2.4) объединяет текст, заключенный в кавычки или находящийся в ячейках.

Объединение текста (Предполагая, что ячейка А10 содержит Город, а ячейкаВ10 -Тула)

Адресные операции (Табл. 2.5) используются для группировки ячеек и диапазонов при вычислениях.

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

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

В Excel часто необходимо использовать при вычислениях значения, которые повязанных с символами, знаком числа и типом.

Функция СИМВОЛ дает возможность получить знак с заданным его кодом. Функция используется, чтоб преобразовать числовые коды символов, которые получены с других компьютеров, в символы данного компьютера.

Функция ТИП определяет типы данных ячейки, возвращая соответствующее число.

Функция ЗНАК возвращает знак числа и возвращает значение 1, если оно положительное, 0, если равно 0, и -1, когда – отрицательное.

Примеры использования функций СИМВОЛ, ТИП и ЗНАК в формулах Excel

Пример 1. Дана таблица с кодами символов: от 65 – до 74:

Необходимо с помощью функции СИМВОЛ отобразить символы, которые соответствуют данным кодам.

Для этого введем в ячейку В2 формулу следующего вида:

Как использовать функцию СИМВОЛ в формулах на практике? Например, нам нужно отобразить текстовую строку в одинарных кавычках. Для Excel одинарная кавычка как первый символ – это спец символ, который преобразует любое значение ячейки в текстовый тип данных. Поэтому в самой ячейке одинарная кавычка как первый символ – не отображается:

Для решения данной задачи используем такую формулу с функцией =СИМВОЛ(39)

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

Значение 39 в аргументе функции как вы уже догадались это код символа одинарной кавычки.

Как посчитать количество положительных и отрицательных чисел в Excel

Пример 2. В таблице дано 3 числа. Вычислить, какой знак имеет каждое число: положительный (+), отрицательный (-) или 0.

Аргумент функции: Число – любое действительное числовое значение.

Сначала посчитаем количество отрицательных и положительных чисел в столбцах «Прибыль» и «ЗНАК»:

А теперь суммируем только положительные или только отрицательные числа:

Как сделать отрицательное число положительным, а положительное отрицательным? Очень просто достаточно умножить на -1:

Можно еще упростить формулу, просто поставить знак оператора вычитания – минус, перед ссылкой на ячейку:

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

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

Проверка какие типы вводимых данных ячейки в таблице Excel

Пример 3. Используя функцию ТИП, отобразить тип данных, которые введены в таблицу вида:

Функция ТИП возвращает код типов данных, которые могут быть введены в ячейку Excel:

Типы данных	Код
Числовой	1
Текстовый	2
Логический	4
Значение ошибки	16
Массив	64

Аргумент функции: Значение – любое допустимое значение.

Типы данных используемые в таблице Excel

[expert_bq id=»1570″]Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Ряд данных – группа связанных точек данных диаграммы, отображающая значение строк или столбцов листа. Каждый ряд данных отображается по-своему. На диаграмме может быть отображен один или несколько рядов данных. На круговой диаграмме отображается только один ряд данных.

Одновременное отображение даты и времени в ячейке Excel

Основы программирования. Инструментальные средства MS Office | Анализ данных в Excel

#ДЕЛ/О! – результат деления на число 0;
#Н/Д – введены недопустимые данные;
#ЗНАЧ! – использование неправильного вида аргумента в функции;
#ЧИСЛО! – неверное числовое значение;
#ССЫЛКА! – удалена ячейка, на которую ссылалась формула;
#ИМЯ? – неправильное имя в формуле;
#ПУСТО! – неправильно указан адрес дапазона.

Анализ данных в Excel

Microsoft Excel предлагает средства для анализа статистических данных. Такие встроенные функции, как СРЗНАЧ (AVERAGE), МЕДИАНА (MEDIAN) и МОДА (MODE), могут использоваться для проведения анализа данных. Если встроенных статистических функций недостаточно, необходимо обратиться к пакету Анализ данных.

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

1. Выберите в меню Сервис команду Анализ данных. При первом выборе этой команды Excel загружает файл с диска. Затем на экране появится окно диалога Анализ данных (рис. 2.19).

2. Чтобы использовать какой-либо из инструментов анализа, выберите его имя в списке и нажмите кнопку ОК.

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

Встроенные статистические функции Microsoft Excel применяются при проведении статистического анализа данных. В данном разделе мы ограничимся обсуждением наиболее часто используемых статистических функций. Кроме них Excel также предлагает более сложные функции ЛИНЕЙН (LINEST), ЛГРФПРИБЛ (LOGEST), ТЕНДЕНЦИЯ (TREND) и РОСТ (GROWTH), которые работают с числовыми массивами.

Для использования Описательная статистика в меню Сервис выберите команду Анализ данных, затем в списке Инструменты анализа окна диалога Анализ данных выберите инструмент Описательная статистика и нажмите кнопку ОК. Появится окно диалога, показанное на рис. 2.20.

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

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

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

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

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

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

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

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

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

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

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

– установите переключатель в положение По вспомогательной оси.

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

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

– Диаграмма – Тип диаграммы – на вкладках Стандартные или Нестандартные выберите необходимый тип.

Для использования типов диаграмм конус, цилиндр или пирамида в объемной диаграмме или гистограмме выберите в поле Тип диаграммы в меню Стандартные пункт Цилиндр, Конус или Пирамида, а затем установите значок в поле Применить к.

Процедура изменения цветов, узора, ширины линии или типа рамки для маркеров данных, области диаграммы, области построения, сетки, осей и подписей делений на плоских и объемных диаграммах, линий тренда и планок погрешностей на плоских диаграммах, а также стенки и основания на объемных диаграммах:

– установить указатель на изменяемый элемент диаграммы и дважды нажать кнопку мыши;

– при необходимости выбрать вкладку Узор и указать нужные параметры.

Для указания эффекта заливки необходимо выбрать соответствующую команду, а затем указать нужные параметры на вкладках Градиентная, Текстура и Узор.

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

Размер списка ограничен размерами одного рабочего листа, т.е. список может иметь не более 256 полей и не более 65 535 записей. Полями принято называть столбцы списка, а записями – строки.

Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям:

– список обязательно должен содержать строку заголовков;

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

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

Excel обладает мощными средствами для работы со списками. Это:

– создание итоговой сводной таблицы на основе данных списка.

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

При вводе данные можно добавлять непосредственно в ячейки, а можно воспользоваться специальной формой ввода (рис. 2.21).

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

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

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

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

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

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

В Excel существует два типа фильтров: Автофильтр и Расширенный фильтр.

Перед тем как использовать Автофильтр, выделите любую ячейку списка. Затем выберите команду Данные – Фильтр – Автофильтр. При включении Автофильтра возле имен полей списка появятся кнопки со стрелками.

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

Рис. 2.22. Вид меню, содержащего команды и список значений поля

Обратите внимание на цвет стрелок на кнопках Автофильтра: если Автофильтр включен, кнопки окрашиваются в синий цвет.

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

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

Кроме команды Все, в раскрывающемся меню кнопок Автофильтра есть еще одна команда Первые 10. которая используется для полей числового типа или дат. Эта команда покажет «горячую десятку» вашего списка.

Пусть необходимо узнать расходы за последние три дня. Щелкните по кнопке Автофильтра в столбце Дата, выберите в раскрываемся меню команду Первые 10. в диалоговом окне сделайте установки, как на рис. 2.23.

Рис. 2.23. Диалоговое окно установки расходов за последние 3 дня

Иногда стандартных условий Автофильтра оказывается недостаточно. Для создания собственного Автофильтра необходимо:

– для выбранного поля (например, Менеджер) из раскрывающегося меню кнопки Автофильтра выбрать команду (Условие…);

– в диалоговом окне Пользовательский автофильтр (рис. 2.24) задать условия отбора значений списка.

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

Для полей числового типа или дат используются следующие правила:

– И, когда интересует область между двумя числами или датами;

– ИЛИ, если интересует область вне интервала, заданного двумя числами или датами.

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

С помощью Расширенного фильтра (рис. 2.25) можно:

– помещать результат отбора данных на другое место и даже на новый лист рабочей книги;

Чтобы воспользоваться Расширенным фильтром, необходимо задать диапазон критериев.

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

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

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

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

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

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

Если отфильтрованный список должен быть помещен на другой лист рабочей книги, сначала переходят на этот лист и только потом обращаются к команде Данные – Фильтр – Расширенный фильтр.

[expert_bq id=»1570″]Но если вы предполагаете, что в дальнейшем в зависимости от обстоятельств вам может понадобиться и другая информация из списка, скопируйте строку, содержащую названия полей списка, целиком. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если задан сложный критерий, то придется отменять составляющие условия отбора по очереди. Иногда бывает проще отказаться от Автофильтра, выбрав команду Данные – Фильтр – Автофильтр, а потом установить Автофильтр снова.