Или формула в excel: ИЛИ (функция ИЛИ) — Служба поддержки Майкрософт

Анализ данных в Excel. Часть 1

Для работы с данными, которые представлены в табличном формате, можно использовать программы, установленные на компьютере – Microsoft Excel или Numbers (для Mac OS). Или воспользоваться практически аналогичной по функционалу онлайн-версией Google Sheets. Панели инструментов разных версий Excel отличаются, поэтому мы покажем возможности работы с этим инструментом в Google Sheets, которые на любом компьютере выглядят одинаково. 

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

Как устроен Excel

Пространство внутри программы похоже на лист бумаги с клетками. Каждая колонка здесь имеет свое название – по букве алфавита, а каждая строка – свой номер. У каждой ячейки есть свой адрес, который состоит из сочетания буквы столбца и номера строки – например, ячейка А1 или B2 – это чем-то похоже на игру в Морской бой. Сам файл похож на книгу со множеством листов. Нажимая на знак плюса в левом нижнем углу страницы, можно создавать новые листы, и например, помещать каждый набор данных на отдельный лист. 

Импорт данных

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

Еще одно распространенное расширение – csv. Это текстовый файл, значения в котором разделены специальными символами – например, запятыми (отсюда и название – comma-separated values) или другими. Его можно открыть в обычном Блокноте. Там можно посмотреть содержимое файла, но чтобы обрабатывать такие данные, пригодится Excel. Чтобы открыть csv, необходимо нажать «Файл» – «Импортировать» – и указать путь к файлу. 

Импорт csv-файла

После загрузки появится меню с разделом «Тип разделителя». Обычно Google Sheets сами определяют верный тип разделителя, поэтому галочку можно оставить на опции «Определять автоматически». Если же тип разделителя определен неверно, и вместо табличного представления вы получили данные в нечитаемом виде, можно указать тип разделителя самостоятельно. Выбрать из предложенных опций или вставить свой символ в окно «Другой». Затем нажать «Импортировать данные» и «Открыть сейчас». 

Предварительная работа с данными

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

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

Форматы данных

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

Формулы 

В программе можно использовать простые математические формулы – например, сложение. Нужно активировать ячейку, которую необходимо заполнить (в нашем примере D2), и ввести =. Именно со знака равенства начинается любая формула в Excel. Затем нужно указать адрес первой ячейки, поставить +, и указать адрес второй ячейки, которую мы хотим прибавить к первой, и нажать клавишу ввода (Enter или Return). Программа сама посчитает сумму двух ячеек. В формуле можно указывать столько ячеек, сколько понадобится сложить. Сложим значения из столбцов «маск.рф» и «mask.ru». Формула будет такой: =B2+C2.

Формула сложения

Автозаполнение ячеек

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

Растягивание формулы

Функции

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

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

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

Функция суммы

Сортировка

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

Для этого необходимо выделить курсором всю таблицу – нажать в меню «Данные» – «Сортировать диапазон». Затем отметить галочкой «Данные со строкой заголовка» – это необходимо, чтобы заголовки остались на месте и не участвовали в сортировке. Затем выбрать, по какому показателю (или заголовку столбца) нужно отсортировать данные. В нашем случае – это «Дата». Выбрать вид сортировки: «от А до Я» (от меньшего к большему) или «от Я до А» (от большего к меньшему). В нашем примере, чтобы даты отобразились в хронологическом порядке, надо отметить «от А до Я». 

Сортировка по возрастанию

Функцию сортировки можно использовать и для анализа данных. Например, нам интересно, в какой день было зарегистрировано самое большое количество сайтов по продаже медицинских масок. Для этого надо проделать те же действия, но в качестве параметра сортировки выбрать «Все сайты» и «от Я до А». Тогда максимальное количество сайтов отобразится в самом начале таблицы, и мы увидим, что пик регистрации таких сайтов пришелся на 4 апреля.

Общая информация о данных

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

Основные показатели

Фильтрация

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

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

Фильтрация по значению

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

Есть и другие примеры условий. Например, мы хотим отобразить данные только за апрель. Нажимаем в меню фильтрации столбца «Дата» «Фильтровать по условию» – «Дата после» – «Точная дата» – и вводим «31. 03.2020». Отобразились данные только за апрель, и теперь можно приступать к их анализу. Например, выделить этот столбец и в правом нижнем углу листа посмотреть общую информацию о данных. Мы увидим, например, что каждый день апреля, в среднем, регистрировали 34 таких сайта. 

Фильтрация по условию

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

Сохранение

Google Sheets при наличии подключения к интернету самостоятельно сохраняют каждое действие и результаты работы. Если же вам понадобится сохранить обработанные данные на компьютер, это можно сделать так: выбрать в меню «Файл», нажать «Скачать» и выбрать из выпадающего меню необходимый формат – там есть как xlsx, так и csv, а также другие форматы.

Решите ошибку формулы Excel

Главная » Excel-Ошибки » Excel-Формула-Ошибка

Если Excel обнаружит, что ваша формула или функция Excel содержит ошибку, она вернет сообщение об ошибке (например, #ЗНАЧ!, #Н/Д ).

В появившемся сообщении об ошибке содержится информация о типе и причине ошибки формулы Excel. Поэтому он может помочь вам в выявлении и устранении проблемы.

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

#НОЛЬ! — Возникает при обращении к пересечению двух диапазонов, которые не пересекаются.
#ДЕЛ/0! — Происходит, когда формула пытается разделить на ноль.
#ЗНАЧ! — Происходит, если одна из переменных в формуле имеет неверный тип (например, текстовое значение вместо числового).
#REF! — Возникает, когда формула содержит недопустимую ссылку на ячейку.
#ИМЯ? — Происходит, если Excel не распознает имя формулы или не распознает текст в формуле.
#ЧИСЛО! — Происходит, когда Excel обнаруживает недопустимое число.
#Н/Д — Указывает, что значение недоступно для формулы.

Excel #NULL! Ошибка

Excel выдает ошибку #NULL! Ошибка при попытке пересечь два диапазона, которые не пересекаются. Например, формула =СУММ(B1:B10 A5:D7) вернет сумму значений в диапазоне B5:B7 (пересечение диапазонов B1:B10 и A5:D7).

Однако, если вы введете формулу =СУММ(B1:B10 C5:D7) , вы получите #NULL! Ошибка , так как диапазоны B1:B10 и C5:D7 не пересекаются.

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

=ЕСЛИОШИБКА(СУММ(B1:B10 C5:D7), 0 )

Excel #ДЕЛ/0! Ошибка

Excel #DIV/0! возникает, когда формула пытается разделить на ноль. Ясно, что деление на ноль равно бесконечности, которая не может быть представлена ​​значением электронной таблицы, поэтому Excel возвращает #DIV/0! Ошибка .

Например, если ячейка C1 содержит значение 0, то формула:

=B1/C1

вернет #DIV/0! Ошибка .

Эту проблему можно решить, используя функцию ЕСЛИ в Excel, чтобы идентифицировать деление на 0 и, в этом случае, получить альтернативный результат. Например:

=ЕСЛИ(C1=0, «н/д», B1/C1)

Excel #ЗНАЧ! Ошибка

#ЗНАЧ! Ошибка формулы Excel генерируется, когда одна из переменных в формуле имеет неправильный тип. Например, простая формула =B1+C1 зависит от ячеек B1 и C1, содержащих числовые значения. Таким образом, если B1 или C1 содержат текстовое значение, это приводит к ошибке #ЗНАЧ! Ошибка .

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

Excel #ССЫЛКА! Ошибка

Если в ячейке Excel отображается #ССЫЛКА! Ошибка формулы , это указывает на недопустимую ссылку на ячейку. Существует две распространенные ситуации, которые вызывают эту ошибку формулы Excel:

  1. Формула ранее ссылалась на ячейку, которая теперь была удалена.

    Ячейка A1 содержит формулу:

    =B1+C1

    Если вы сейчас удалите столбец C электронной таблицы, ссылка формулы на C1 больше недействительна, и вы получите #РЕФ! Ошибка .

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

    Показанная ниже формула, введенная в ячейку A1, подсчитывает количество ячеек в «Листе2», содержащих значение 1: 9.0003

    =СЧЁТЕСЛИ(Лист2!1:1048576, 1)

    Если вы сейчас скопируете приведенную выше формулу в ячейку A2, Excel попытается изменить диапазон на Лист2!2:1048577. — Однако строки с номером 1048577 не существует (поскольку в текущих версиях Excel имеется только 1048576 строк). Таким образом, вы получите #REF! Ошибка .

В обоих приведенных выше примерах, если щелкнуть ячейку, содержащую #ССЫЛКА! , вы увидите, что ссылка на ячейку в формуле была заменена на #РЕФ! . Поэтому, чтобы исправить эту ошибку, вам нужно повторно ввести в формулу правильные ссылки на ячейки.

(Обратите внимание, что во втором примере выше вы можете избежать ошибки, сделав ссылки на ячейки абсолютными, т.е. изменив ссылку Лист2!1:1048576 на Лист2!$1:$1048576).

Excel #ИМЯ? Ошибка

Когда Excel обнаруживает текст в формуле, он пытается интерпретировать текст как ссылку, именованный диапазон или имя функции. Если текст не распознается ни одним из них, #ИМЯ? Будет выдана ошибка .

Например, если вы намеревались ввести функцию =СУММ(B1:C2) , но случайно набрали =SM(B1:C2) , Excel не распознает имя функции «SM» и поэтому будет генерировать #ИМЯ? Ошибка .

Следовательно, как подступиться к #ИМЯ? Ошибка функции Excel заключается в проверке правильности написания имен функций, ссылок и именованных диапазонов, а также в том, что любые переменные, предназначенные для текстовых значений, вводятся в двойных кавычках. Если ваша формула содержит вложенные функции, проверяйте результаты каждой из них по отдельности, пока не определите источник ошибки.

Excel #ЧИСЛО! Ошибка

#ЧИСЛО! Ошибка формулы Excel генерируется, когда Excel обнаруживает недопустимое число в формуле. Например, все квадратные числа положительны, поэтому не существует такой вещи, как квадратный корень из отрицательного числа (за исключением мнимых чисел). Таким образом, функция Excel КОРЕНЬ(-2) сгенерирует #ЧИСЛО! , чтобы указать недопустимый аргумент отрицательного числа для функции квадратного корня.

Таким образом, способ исправить #ЧИСЛО! Ошибка заключается в проверке каждого из числовых аргументов в вашей формуле. Как и в случае с другими типами аргументов, если ваша формула вложена или состоит из нескольких частей, рекомендуется разбить формулу и оценить каждый аргумент отдельно.

Ошибка Excel #N/A

Ошибка формулы Excel #N/A возникает, когда значение недоступно для вашей формулы. Например, в приведенном ниже примере показана попытка использовать функцию Vlookup для поиска значения «Капуста» в столбце C электронной таблицы и возврата соответствующей стоимости из столбца D. Однако, поскольку «Капуста» не отображается в столбце C, функция ВПР не может найти это значение и поэтому возвращает #Н/Д ошибка.

Таким образом, чтобы понять причину ошибки #N/A , необходимо просмотреть значения, к которым обращается формула, и определить, почему требуемое значение недоступно.

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

Вернуться на страницу ошибок Excel
Вернуться на страницу ExcelFunctions.net Домашняя страница

Отображение или скрытие формул в Microsoft Excel (3+ простых способа)

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

by Avantix Learning Team | Обновлено 7 марта 2022 г.

Применяется к: Microsoft ® Excel ® 2010, 2013, 2016, 2019, 2021 и 365 (Windows)

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

Рекомендуемая статья: Как удалить пустые строки в Excel (5 быстрых способов удалить пустые строки)

Хотите узнать больше об Excel?  Посетите наш виртуальный класс или живой класс Курсы Excel >

Отображение формул с помощью сочетания клавиш

Вы можете показать или скрыть формулы с помощью сочетания клавиш. Нажмите Ctrl + тильда (~) или Ctrl + ударение (`), чтобы отобразить или скрыть формулы. Клавиша тильды/акцента появляется в верхнем левом углу большинства клавиатур под клавишей Esc. Этот ярлык работает во всех версиях Excel.

Отображение формул с помощью кнопки

Простой способ отобразить или скрыть формулы в Excel — использовать кнопку «Показать формулы».

Чтобы отобразить формулы с помощью кнопки:

  1. Щелкните вкладку «Формулы» на ленте.
  2. В группе Аудит формул щелкните Показать формулы. Рабочий лист теперь будет отображаться с формулами вместо значений.
  3. Нажмите «Показать формулы» еще раз, чтобы скрыть формулы.

Ниже находится вкладка «Формулы» на ленте:

Отображение формул с помощью функции ФОРМУЛАТЕКСТ

Вы также можете использовать функцию ФОРМУЛАТЕКСТ в ячейке для отображения формулы из другой ячейки в виде текстовой строки. Это очень полезно, если вы хотите проверить рабочий лист и просмотреть как значения, так и формулы. Функция ФОРМУЛАТЕКСТ доступна в Excel 2013 и более поздних версиях.

Синтаксис функции FORMULATEXT: =FORMULATEXT(ссылка), где ссылка — это ячейка или диапазон ячеек.

Функция ФОРМУЛАТЕКСТ вернет ошибку #Н/Д, если:

  • Формула относится к ячейке, не содержащей формулы.
  • Формула ссылается на другую книгу, но книга не открыта.

В следующем примере у нас есть обычные формулы в столбце C и в столбце D, мы использовали функцию FORMULATEXT:

Итак, в D2 формула будет = FORMULATEXT(C2).

Бонус: скрытие формул и блокировка ячеек

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

Первый шаг — скрыть формулы:

  1. Выберите ячейки с формулами, которые вы хотите скрыть.
  2. Щелкните правой кнопкой мыши выбранные ячейки и выберите «Формат ячеек» или нажмите Ctrl + 1. Появится диалоговое окно «Формат ячеек».
  3. Перейдите на вкладку Защита.
  4. Проверка скрыта. Если вы хотите также защитить ячейки, убедитесь, что установлен флажок «Заблокировано».
  5. Нажмите кнопку ОК. Ничего не произойдет, пока вы не защитите лист.

Ниже приведено диалоговое окно «Формат ячеек»:

Второй шаг — защита рабочего листа:

  1. Отобразите рабочий лист с ячейками, которые были отформатированы как скрытые, в диалоговом окне «Формат ячеек».
  2. Перейдите на вкладку «Обзор» на ленте.
  3. В группе «Изменения» нажмите «Защитить лист». Появится диалоговое окно.
  4. Установите или снимите флажки с нужных опций (обычно вы оставляете отмеченными первые два).
  5. Введите пароль (вам потребуется установить пароль, иначе любой сможет снять защиту с листа). Пароли чувствительны к регистру, и вы должны хранить копию своих паролей в другом месте.
  6. Введите пароль еще раз.
  7. Нажмите кнопку ОК. Все формулы, которые вы пометили как скрытые, больше не будут отображаться на панели формул.

Ниже находится диалоговое окно «Защитить лист»:

Чтобы отобразить формулы и снять защиту с рабочего листа:

  1. Отобразите нужный рабочий лист.
  2. Перейдите на вкладку «Обзор» на ленте и нажмите «Снять защиту с листа».
  3. Введите соответствующий пароль.
  4. Нажмите кнопку ОК.

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

Подпишитесь, чтобы получать больше статей, подобных этой

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

Дополнительные ресурсы

Как объединить ячейки в Excel (4 способа с помощью ярлыков)

Как объединить ячейки в Excel с помощью конкатенации (3 способа)

Как заполнить пустые ячейки в Excel значением из ячейки выше

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

Как использовать мгновенное заполнение в Excel для очистки или извлечения данных (Руководство для начинающих)

Связанные курсы

Microsoft Excel: средний/продвинутый уровень

Microsoft Excel: анализ данных с функциями, информационными панелями и инструментами анализа «что, если»

Microsoft Excel: введение в Visual Basic для приложений (VBA)

ПРОСМОТРЕТЬ ДРУГИЕ КУРСЫ >

Наши курсы под руководством инструктора проводятся в формате виртуального класса или в нашем офисе в центре Торонто по адресу 18 King Street East, Suite 1400, Toronto, Ontario, Canada (некоторые очные курсы также могут проводиться в другом месте в центре Торонто) . Свяжитесь с нами по адресу [email protected], если вы хотите организовать индивидуальный виртуальный класс под руководством инструктора или обучение на месте в удобное для вас время.

Copyright 2023 Avantix ® Обучение

Функция XLOOKUP заменяет традиционную функцию Excel VLOOKUP (а также функции HLOOKUP и INDEX/MATCH). Он имеет новый набор аргументов и доступен в Excel 2021 и 365. Он позволяет искать значение в массиве в диапазоне или таблице и возвращать один или несколько результатов. Одно из основных преимуществ XLOOKUP заключается в том, что он может искать в столбцах слева в наборе данных и возвращать диапазон.

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

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