Как работать с формулами в программе excel: Работа с формулами эксель

Глава 10 Работа с формулами и функциями. Компьютер для бухгалтера

Глава 10 Работа с формулами и функциями

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

• Ввод формул в ячейку

• Функции

• Относительная и абсолютная адресация

• Защита ячеек, листов и книг

• Работа со списками

• Сводные таблицы

Ввод формул в ячейку

Формула в программе начинается со знака = или +. В формуле могут использоваться операторные скобки. Например, формула может выглядеть следующим образом: =А4/(В1+12).

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

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

Рассмотрим, как работать с формулами и функциями в программе.

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

Начнем с оформления документа:

1. Заполните ячейки значениями, приведенными в табл. 10.1.

Таблица 10.1. Шапка накладной

2. В ячейку D4 введем формулу =В4*С4 (рис. 10.1). Можно ввести формулу, набрав адреса ячеек с клавиатуры, но гораздо удобнее вводить формулы с помощью мыши.

Рис. 10.1. Ввод формулы в ячейку

3. В ячейку D4 введите знак равенства (=).

4. Щелкните в ячейке В4 (обратите внимание на то, что ее адрес при этом попал в редактируемую ячейку).

5. Наберите математический знак умножения (*).

6. Щелкните во второй ячейке, которая участвует в формуле, – С4.

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

После заполнения граф Цена и Количество в графе Сумма автоматически появится результат.

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

9. Если изменить значения граф Количество и Цена, в графе Сумма результат будет автоматически пересчитан.

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

Возможные сообщения об ошибках могут быть следующими:

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

в формуле может указываться диапазон ячеек: он показывается двоеточием. Например, диапазон ячеек от А1 до А10 будет указан так: А1:А10.

В формулах могут также использоваться операторные скобки и логические функции. Например, при вычислении подоходного налога формула будет выглядеть так: =(СЗ-400)*0,13, где в ячейке с адресом СЗ указан оклад сотрудника.

Функции

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

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

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

Чтобы познакомиться с набором функций Excel, щелкните на кнопке Вставка функции панели инструментов Стандартная. Откроется окно мастера функций.

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

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

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

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

Категория Математические позволяет вычислять математические функции, такие как сумма, произведение, частное, квадратный корень, логарифм и т. д. Желающие могут поупражняться в переводе арабских цифр в римские. Есть функция, возвращающая значение числа л. В этом разделе также представлено несколько разновидностей округления и суммирования. Здесь можно также упомянуть операции округления до ближайшего четного или нечетного, до меньшего целого и т. д. Функция, выполняющая суммирование (СУММ), дополнена функциями СУММ KB (сумма квадратов), СУММПРОИЗВЕД (сумма произведений), СУММКВРАЗН (сумма квадратов разностей), СУММРАЗНКВ (сумма разностей квадратов).

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

Категория Текстовые позволяет выполнять операции с текстами: подсчитать количество символов в ячейке, узнать код символа, поместить в данную ячейку некоторое количество символов из другой, проверить идентичность двух текстов, найти некоторый текст и заменить его другим.

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

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

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

Для вставки функции не обязательно пользоваться мастером функций – можно набирать функции и на клавиатуре.

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

Функция СУММ

Наиболее простой способ суммирования – сделать активной ячейку, в которую надо поместить сумму, и щелкнуть на кнопке Автосумма на панели инструментов Стандартная. Таким образом удобно суммировать данные в столбцах и строках (рис. 10.2).

Рис. 10.2. Суммирование данных

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

1. Откройте окно мастера функций (рис. 10.3), выберите категорию Математические, затем выберите функцию СУММ и щелкните на кнопке ОК.

Рис. 10.3. Выбор функции с помощью мастера функций

2. В открывшемся диалоговом окне наберите адрес ячейки или выберите ее щелчком мыши на рабочем листе.

3. Для того чтобы лист стал доступным, щелкните на кнопке справа в поле ввода.

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

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

Функция ЕСЛИ

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

Данный текст является ознакомительным фрагментом.

Глава 8 Работа с функциями XSLT и XPath

Глава 8
Работа с функциями XSLT и XPath
В этой главе мы изучим доступные вам в XSLT функции — как встроенные в XSLT, так и функции XPath. Вы уже встречались с этими функциями выборочно (например, с id, generate-id, position, count и т.д). В этой главе мы рассмотрим их все.Некоторые функции входят в XSLT,

Различия между функциями wait и waitpid

Различия между функциями wait и waitpid
Теперь мы проиллюстрируем разницу между функциями wait и waitpid, используемыми для сброса завершенных дочерних процессов. Для этого мы изменим код нашего клиента TCP так, как показано в листинге 5.7. Клиент устанавливает пять соединений с

Глава 4 Работа с формулами в редакторе Word

Глава 4 Работа с формулами в редакторе Word
• Математические формулы• Формулы, созданные в предыдущих версиях Word• Химический редактор ChemPen3D1Сложно представить себе дипломную или курсовую работу без формул. В работах по точным наукам (математике, физике, химии) без них

87. Делайте предикаты чистыми функциями

87. Делайте предикаты чистыми функциями
РезюмеПредикат представляет собой функциональный объект, который возвращает ответ да/нет, обычно в виде значения типа bool. Функция является «чистой» в математическом смысле, если ее результат зависит только от ее аргументов

Работа с формулами

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

9. Как правильно пользоваться функциями

9. Как правильно пользоваться функциями
ФУНКЦИИСТРОИТЕЛЬНЫЕ БЛОКИ ПРОГРАММЫСВЯЗЬ МЕЖДУ ФУНКЦИЯМИ: АРГУМЕНТЫ, УКАЗАТЕЛИ, ВОЗВРАТ ЗНАЧЕНИЯТИПЫ

Использование указателей для связи между функциями

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

Б.4. Взаимосвязь с библиотечными функциями ввода-вывода

Б.4. Взаимосвязь с библиотечными функциями ввода-вывода
Выше уже говорилось о том. что функции ввода-вывода стандартной библиотеки языка С реализованы на основе низкоуровневых функций. Иногда удобнее работать с одними, иногда — с другими.Если файл был открыт с помощью

22.

1. Сложные функции и сложности с функциями

22.1. Сложные функции и сложности с функциями
Функции могут принимать входные аргументы и возвращать код завершения.function_name $arg1 $arg2Доступ к входным аргументам, в функциях, производится посредством позиционных параметров, т.е. $1, $2 и так

Видеоплаты с функциями видеозахвата

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

Глава 3 Работа с математическими формулами

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

У18.

1 Эмуляция перечислимых типов однократными функциями

У18.1 Эмуляция перечислимых типов однократными функциями
Покажите, что при отсутствии unique-типов перечислимый тип языка Pascaltype ERROR = (Normal, Open_error, Read_error)может быть представлен классом с однократной функцией для каждого значения

6.2.7. Работаем с функциями

6.2.7. Работаем с функциями
Для выполнения задания нам понадобится материал разд. 5.2.7.Задание? Создать таблицу, как показано на рис. 6.34.? В ячейки диапазона F6: F13 записать формулы для подсчета суммы набранных баллов.? В ячейки G6:G13 внести формулы для отображения отметок о

Практическая работа 44. Создание простой таблицы с формулами

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

Работа с Excel 2007: Формулы

Начало работы

Начните со знака равенства

Для расчёта в Excel используют формулы как обучающий пример рассмотрим следующее:

Два компакт-диска, купленные в феврале, стоили 12,99 и 16,99 грн.. Их суммарная стоимость равна расходам на компакт-диски в этом месяце.

Эти значения можно сложить в Excel, введя в ячейку C6 простую формулу.

Формулы в Microsoft Excel всегда начинаются со знака равенства (=). Чтобы сложить числа 12,99 и 16,99, введите в ячейку C6 формулу

=12,99+16,99

Знак сложения (+) — это математический оператор, обозначающий суммирование значений.

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

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

Для вычисления разности используйте знак «минус», для вычисления произведения — знак «звездочка» (*), для вычисления частного — знак «косая черта» (/). Обязательно начинайте формулу со знака равенства.

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

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

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

 На вкладке Главная в группе Правка нажмите кнопку Сумма.

 Ячейки будут обведены цветовым индикатором, в ячейке B7 будет отображена формула.

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

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

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

Расходы за январь можно подсчитать, выбрав ячейку B7 и нажав кнопку Сумма в группе Правка на вкладке Главная. Таким образом будет введена функция «СУММ», складывающая значения по столбцу в интервале ячеек. Использование этой функции упрощает суммирование, если требуется сложить большое число значений, при этом нет необходимости набирать формулу.

По нажатии кнопки ВВОД в ячейке B7 будет отображен результат вычислений (95,94). Формула =CУММ(B3:B6) отображается в строке формул при каждом щелчке в ячейке B7.

Знаки «B3:B6» называются аргументом; аргументы сообщают функции «СУММ», какие значения требуется суммировать. Использование ссылки на ячейку (B3:B6) вместо самих значений в ячейках позволяет автоматически обновлять результат, если значение в ячейках изменится. Двоеточие (:) между B3 и B6 обозначает диапазон ячеек в столбце B, строки с третьей по шестую. Круглые скобки отделяют аргумент от функции.

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

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

Копирование формулы вместо создания новой

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

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

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

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

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

Типы ссылок

Относительные. Относительные ссылки в формуле автоматически изменяются при копировании в строке или столбце. Это сделало возможным копирование формулы, относящейся к январю, для вычисления февральских расходов в первом уроке. Как показывает приведенный здесь пример, при копировании формулы =C4*$D$9 из строки в строку относительная ссылка на ячейку изменяется с C4 на C5 и C6.
Абсолютные. Абсолютные ссылки на ячейки имеют постоянное значение. Абсолютные ссылки не изменяются при копировании формулы из ячейки в ячейку. Абсолютные ссылки обозначаются знаком доллара ($), например $D$9. На рисунке показано, что при копировании со строки на строку формулы =C4*$D$9 абсолютная ссылка на ячейку $D$9 не изменяется.

Смешанные. Смешанная ссылка является абсолютной по столбцу и относительной по строке или относительной по столбцу и абсолютной по строке. Например, ссылка $A1 является абсолютной по столбцу A и относительной по строке 1. При копировании смешанной ссылки из ячейки в ячейку ее относительная ссылка меняется, а абсолютная остается неизменной.

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

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

Введите размер скидки 0,07 в свободную ячейку D9, в ячейку D4 введите формулу, начинающуюся с =C4*. Затем введите знак доллара ($) и букву D, чтобы указать абсолютную ссылку на столбец D, и $9, чтобы указать абсолютную ссылку на строку 9. Результатом вычислений по формуле будет произведение значений ячеек C4 и D9.

Затем с помощью маркера заполнения  скопируйте формулу из ячейки D4 в ячейку D5. При копировании формулы относительная ссылка изменится с C4 на C5, а абсолютная ссылка на размер скидки в ячейке D9 останется неизменной ($D$9).
 

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

Нахождение среднего значения

Функцию «СРЗНАЧ» предназначена для вычисления среднего значения.

Excel введет формулу за вас. Щелкните по ячейке в которой вы хотите видеть результат. На вкладке Главная в группе Правка щелкните стрелку на кнопке Сумма и выберите в списке значение Среднее. В строке формул  в верхней части листа отобразится формула =СРЗНАЧ(кординаты ячеек). после чего нажимаете Enter. 

Формулу также можно ввести непосредственно в ячейку.

Примечание. Кнопка Сумма также находится на вкладке Формулы в группе Библиотека функций.

Непонятные знаки на листе

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

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

#ССЫЛКА! Ссылка на ячейку неверна. Ячейки могли быть удалены или вставлены поверх.

#ИМЯ? Опечатка в имени функции или использование имени, неизвестного программе Excel.

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

Руководство по созданию ссылок на другие файлы Excel с формулами и функциями

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

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

Разделы:

Шаги для ссылки на данные в отдельной рабочей книге (файл Excel)

Работа с формулой из разных книг

Ускорение ввода формул с использованием формул из разных книг

Отключены ссылки — Важное примечание I Закрытие или повторное открытие книги Excel

Внешние ссылки и скорость

Примечания

Шаги для ссылки на данные в отдельной книге (файл Excel)

1. Убедитесь, что обе книги Excel открыты одновременно.
2. Перейдите в книгу, в которую вы хотите ссылаться или «импортировать» данные, и выберите ячейку, в которую вы хотите поместить формулу.
3. Начните вводить формулу или функцию.

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

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

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

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

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

Сама формула также будет выглядеть немного странно, и я объясню это ниже.

Работа с кросс-книжной формулой

Эта новая формула сначала может показаться странной, и вот она:

 =СУММ('[Ссылка на другие файлы Excel из формул и функций 2.xlsx]Лист1'!$B $3:$B$6) 

Ссылка на другие файлы Excel из формул и функций 2.xlsx

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

Sheet1

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

$B$3:$B$6

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

Ускорение ввода формул с помощью формул для разных книг

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

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

Во-первых, нам нужно изменить ссылки на ячейки с абсолютных $B$3:$B$6 на относительные B3:B6 . Вы удаляете знаки доллара, и это позволяет изменять ссылки на ячейки при копировании формулы на листе.

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

И вот:

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

Ссылки отключены — важное примечание

При первом закрытии и открытии книги, в которой формулы используются для ссылки на другую книгу, Excel обычно останавливает обновление этих формул и получение новых данных из других файлов Excel. Это ОЧЕНЬ ВАЖНО, потому что пользователь может не понимать, почему данные, которые он видит, неточны или неактуальны. Таким образом, им нужно будет включить внешние ссылки.

Для большинства людей это легко сделать, так как появится очень яркое предупреждение, и вам нужно будет просто нажать кнопку 9.0079 Кнопка «Включить содержимое» в этом предупреждении:

Если вы не видите этого предупреждения и беспокоитесь о том, что ссылки не обновляются, перейдите в «Параметры Excel» и включите их там; как это сделать зависит от вашей версии Excel. Если вы используете Excel 2007 или более позднюю версию, выберите «Параметры» > «Центр управления безопасностью» (вкладка в левой части экрана) > «Настройки центра управления безопасностью» > «Внешнее содержимое» (вкладка в левой части экрана > и измените параметры по своему усмотрению.

Вы можете также см. это предупреждение при открытии книги:

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

Странные формулы после закрытия или повторного открытия книги Excel

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

 =СУММ('[Ссылка на другие файлы Excel из формул и функций 2.xlsx]Лист1'!$B$3:$B$6) 

к этому:

 =SUM('D:\Excel Related\Tutorials\Tutorial Content - General\[Ссылка на другие файлы Excel из формул и функций 2.xlsx]Sheet1'!B3:B6) 

Don’ не паникуйте и не волнуйтесь, когда это происходит. Если вы снова откроете другую книгу, эти ссылки вернутся к тому же виду, что и в первом примере.

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

Внешние ссылки и скорость

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

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

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

Notes

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

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

10 формул Excel, которые должен знать каждый аналитик данных в 2022 году

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

Для правильного использования Excel необходимо хорошее понимание формул программы. Эти формулы работают со значениями в ячейке или ячейках, выдавая определенную информацию, такую ​​как общая сумма или наименьшее значение в выбранных ячейках. У нас есть курс «Введение в аналитику данных», в котором основное внимание уделяется ряду аналитических задач на основе Excel. Курс состоит из 10 упражнений, и студенты изучат основы Excel, работая с набором данных, связанных с индустрией видеоигр.

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

Содержание

1. Concatenate
2. Vlookup
3. LEN
4. SUMIFS
5. ДНЯ/СЕДЕЛИЧЕСКИЕ ДНЕТЫ
6. . Заместитель
7. Minifs/maxifs
8. .0026
9. ОБРЕЗАТЬ
10. СЧЕТЧИКИ
11. ВЛЕВО/ВПРАВО

1. СЦЕПИТЬ

Начнем с простого. Concatenate позволяет объединить значения нескольких ячеек в одну. Это одна из самых мощных формул для проведения анализа данных, которая обычно используется для объединения текста, дат и чисел. Объединение компонентов URL-адреса или строк адреса — два типичных примера того, для чего используется формула. Здесь вы найдете полное пошаговое руководство по CONCATENATE.

2. ВПР

Вы, несомненно, встречали =ВПР, знакомое всем, кто работал в Excel. Формула позволяет искать данные, расположенные в вертикальных столбцах. Например, если у вас есть список месяцев года с прибылью, полученной в каждом месяце, функция =ВПР может представить данные за выбранный вами месяц. Здесь вы найдете пошаговое руководство по функции ВПР, а также стоит проверить функцию XПР.

3. ДЛИН

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

4. СУММЕСЛИМН

Функция =СУММЕСЛИ является важной формулой в мире анализа данных. Формула суммирует значения в ячейках, которые соответствуют выбранному числу. В приведенном выше примере формула суммирует числа в ячейках, которые больше числа 5. Здесь вы найдете подробное руководство по СУММЕСЛИ.

5. ДНИ/ЧИСТЫЕ РАБДНИ

Этот пункт говорит сам за себя. =DAYS определяет количество дней между двумя календарными датами и обычно используется для определения жизненного цикла продукта или сроков контракта. =NETWORKDAYS — его более полезный аналог, который используется для расчета количества рабочих дней между двумя датами. Можно сказать, что эта формула живет до выходных.

6. ПОДСТАВИТЬ

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

7. MINIFS/MAXIFS

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

8. НАКЛАДКА

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

9. СЧЁТЕСЛИМН

Формула СЧЁТЕСЛИМН подсчитывает, сколько раз значение появляется на основе одного критерия. В приведенном выше примере «Красный» и «Англия» появляются вместе дважды, и это число возвращается в ячейке СЧЁТЕСЛИМН. Здесь вы найдете пошаговое руководство по COUNTIFS.

10. ВЛЕВО/ВПРАВО

Формулы =ЛЕВО и =ПРАВО — это простые способы получения данных из статических ячеек. =ВЛЕВО возвращает желаемое количество символов от начала ячейки, а =ВПРАВО делает обратное. Нужно извлечь коды городов из списка телефонных номеров? Это формула для вас.

Заключительные мысли

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

Просмотр по теме: Том, старший научный сотрудник CareerFoundry, выполняет некоторые из наиболее важных функций. Прочитав эту статью, возможно, вы даже узнаете некоторых из них!

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