Таблицы excel формулы excel: Функции Excel (по категориям) — Служба поддержки Майкрософт

Содержание

Обработка данных. Word и Excel

На этой странице вы узнаете

  • Как быстро прибраться в большом количестве данных?
  • Как объяснить поиску, что “ухо” и “КуХоНнЫй” — не одно и то же?

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

Знакомство с интерфейсом Excel

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

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

Интерфейс открытой таблицы состоит из следующих элементов:

  1. Главная панель, в которой все рабочие инструменты разбиты на основные группы.  

    2. Операции можно проводить как над конкретными числами, так и над значениями ячеек, записывая их номер в виде буква-цифра, например, А1 или С16. Кроме ручного указания номера ячейки, в любой момент составления формулы можно щелкнуть по нужной ячейке — ее номер автоматически будет добавлен в формулу. Завершение записи формулы и ее запуск производятся нажатием клавиши Enter.

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

    Можно заметить, что формула подстроилась под ее растягивание — если во второй строке находилась формула “=А2+В2”, то в шестой строке формула оказалась ”=А6+В6”. $C$2” — значение с относительной адресацией подстроилось, а с абсолютной — нет.

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

    Например, в данном случае:

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

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

    Функции Excel

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

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

    Для записи более сложных функций, например, функции ЕСЛИ, которая помогает производить определенные операции при определенных условиях, удобно воспользоваться помощником построения функций слева сверху от таблицы. Записав в ячейке “=”, он автоматически предложит несколько функций, и если нужной здесь не окажется, ее можно будет найти по названию в расширенном поиске. 

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

    Какие еще есть функции в Excel?

    • МАКС(диапазон) — возвращает максимальное из всех переданных значений;
    • МИН(диапазон) — возвращает минимальное из всех переданных значений;
    • ЕСЛИ(условие; значение если истина; значение если ложь) — проверяет “условие”, и если оно выполняется, заносит в ячейку “значение если истина”, если же не выполняется — “значение если ложь”;
    • СЧЁТ(диапазон) — подсчитывает количество чисел в переданном диапазоне;
    • СЧЁТЕСЛИ(диапазон; критерий)  — подсчитывает количество значений в диапазоне, удовлетворяющих критерию;
    • СУММ(диапазон) — возвращает сумму переданных значений;
    • СРЗНАЧ(диапазон) — возвращает среднее арифметическое переданных значений;
    • ОСТАТ(делимое; делитель) — возвращает остаток от деления делимого на делитель;
    • И(условие 1; условие 2) — возвращает истину, если оба переданных условия истинны;
    • ИЛИ(условие 1; условие 2) — возвращает истину, если хотя бы одно из условий истинно.

    Фильтрация и сортировка таблиц

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

    Например, класс поручил вам неимоверно важную миссию — найти учительский журнал и исправить все двойки на хорошие оценки.

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

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

    И что делать?

    Как быстро прибраться в большом количестве данных?

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

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

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

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

    Теперь мы будем видеть только наших одноклассников:

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

    Снова выделяем все данные и нажимаем кнопку “Сортировка”.

    Выплывет окно, в котором мы сможем настроить сортировку — по нужному нам параметру и в нужном нам порядке.

    Наконец мы имеем список только наших одноклассников, который выведен в порядке возрастания оценок.

    Поиск в тексте. Файл Word

    Microsoft Word — программа для создания, редактирования и хранения данных в текстовом виде. Формат файла Word — docx или doc.

    Как и электронная таблица, текст, набранный в данной программе, может быть сколь угодно большим. В огромном массиве текстовой информации важно найти конкретные данные, для чего в Word есть функция поиска по файлу. Ее можно включить сочетанием клавиш ctrl + F или нажатием соответствующей кнопки в разделе “Главное” основной панели.

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

    Если выбрать в выпавшем меню “Параметры”, откроется окно настройки поиска текстовой информации.

    Как объяснить поиску, что “ухо” и “КуХоНнЫй” — не одно и то же?

    Самыми часто используемыми параметрами являются первые два:
    — “Учитывать регистр” — если выбрать этот параметр, то поиск выдаст только слова, в которых заглавные и строчные буквы написаны именно так, как задали мы. Без этого параметра слова “регистр” и “РеГиСтР” будут считаться одним и тем же словом.
    — “Только слово целиком” будет искать только дописанные слова. Без этого параметра при поиске по слову “ухо” как вариант нам будет выдано и слово “кухонный” —  в нем содержится искомая комбинация букв.

    Например, нам надо в произведении И. С. Тургенева найти слово “окно”, причем именно с маленькой буквы. Тогда применяем настройки “Учитывать регистр”, а также “Только слово целиком”. 

    Выставив все настройки и введя в строку поиска слово “окно”, мы получим:

    • информацию о количестве подходящих значений сразу под строкой поиска;
    • раздел “Заголовки” — он  выделит по заголовкам фрагменты текста, в которых содержится интересующая нас информация;
    • раздел “Страницы” — покажет, на каких страницах находится информация;
    • раздел “Результаты” — продемонстрирует небольшой фрагмент текста, где находится информация.

    В самом тексте искомая информация будет выделена цветом.

    Фактчек

    • Для обработки данных в электронных таблицах Microsoft Excel используются формулы. Помимо стандартных математических операций, также используются функции для более сложных вычислений и поиска информации.
    • Фильтрация электронной таблицы позволяет вывести не все данные, а только необходимые.
    • Сортировка электронной таблицы позволяет вывести все данные в определенном порядке.
    • Для поиска информации по текстовому документу Microsoft Word используется навигационная панель, открывающаяся сочетанием клавиш ctrl + F или из раздела “Главное” главной панели. Расширенные настройки поиска находятся в разделе “Параметры” всплывающего меню, находящегося в строке поиска навигационной панели.

    Проверь себя

    Задание 1.
    Какова логика работы функции =ЕСЛИ(А1+В1>C1; A1+B1; C1)?

    1. Если А1+В1 больше С1, то в ячейку будет записано А1+В1, иначе — С1
    2. Если А1+В1 больше С1, то в ячейку будет записано С1, иначе — А1+В1
    3. А1+В1 точно будет записано в ячейку, но если А1+В1 больше С1, то в ячейке будет и второе значение — С1
    4. А1+В1 точно будет записано в ячейку, но если А1+В1 больше С1, то к этому значению будет еще и прибавлено С1

    Задание 2.
    За подсчет значений в электронной таблице, удовлетворяющих определенному критерию, отвечает функция…

    1. СЧЁТ
    2. СЧЁТЕСЛИ
    3. ОСТАТ
    4. СУММ

    Задание 3.
    Для чего в Excel используется фильтрация?

    1. Для вывода информации в определенном порядке
    2. Для изменения большого объема информации
    3. Для удаления информации.
    4. Для вывода только определенной информации.

    Задание 4.
    Выберите все слова, которые выдаст навигатор Word при поиске слова “часть” с указанием настройки “Только слово целиком” и без указания настройки “Учитывать регистр”.

    1. “часть”
    2. “Часть”
    3. “чАсТь”
    4. “матчасть”
    5. “МаТчАсТь”

    Ответы: 1. — 1; 2. — 2; 3. — 4; 4. — 123

    Таблицы в Excel

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

    Рис. 1. Диапазон данных: (а) в обычном представлении; (б) в виде таблицы

    Скачать заметку в формате Word или pdf, примеры в формате Excel

    Вероятно, вам доводилось создавать диапазоны, соответствующие такому описанию (рис. 1а). Самое интересное начинается, когда вы хотите, чтобы Excel преобразовал диапазон данных в «настоящую» таблицу. Для этого нужно выделить любую ячейку в диапазоне и выполнить команду Вставка –> Таблицы –> Таблица, или нажать Ctrl+T (английское) (рис. 1б).

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

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

    Разница между обычным диапазоном ячеек и диапазоном, преобразованным в таблицу:

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

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

    • Не работают Представления (Вид –> Режимы просмотра книги –> Представления).
    • Книгу нельзя предоставить Для совместного использования (Рецензирование –> Изменения –> Доступ к книге).
    • Невозможно автоматически вставлять промежуточные итоги (Данные –> Структура –> Промежуточный итог).
    • Нельзя использовать в таблице формулы массива.

    Подведение итогов в таблице

    Рассмотрим таблицу со статистикой продаж (рис. 2). Сначала были введены данные, а потом диапазон преобразован в таблицу с помощью команды Вставка –> Таблицы –> Таблица. Не предпринимая никаких действий, таблица получила название. В нашем случае Таблица3.

    Рис. 2. Простая таблица с тремя столбцами

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

    1. Активизируйте любую ячейку в таблице.
    2. Выполните команду Работа с таблицами –> Конструктор –> Параметры стилей таблиц –> Строка итогов и установите флажок Строка итогов.
    3. Активизируйте ячейку в строке итогов и выберите формулу подведения итогов из раскрывающегося списка (рис. 3).

    Рис. 3. Из раскрывающегося списка можно выбрать формулу для подведения итогов

    Например, чтобы вычислить сумму показателей из столбца Факт, выберите вариант Сумма из раскрывающегося списка в ячейке D14. Excel создает формулу: =ПРОМЕЖУТОЧНЫЕ. ИТОГИ(109;[Факт]). Аргумент 109 соответствует функции СУММ. Второй аргумент функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ – название столбца, указанное в квадратных скобках. Использование имени столбца в квадратных скобках позволяет создавать в таблице структурированные ссылки. Функции итогов в разных столбцах таблицы могут быть различными. Опция Другие функции позволяет сконструировать довольно сложную формулу.

    Можно включать или отключать отображение строки итогов с помощью команды Работа с таблицами –> Конструктор –> Параметры стилей таблиц –> Строка итогов.

    Использование формул в таблице

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

    1. Активизируйте ячейку D1 и введите в качестве заголовка столбца слово Разница — Excel автоматически расширит таблицу.
    2. Перейдите в ячейку D2, введите знак равенства (начало написание формулы), кликните на ячейке С2, на знак минус, и на ячейку В2.
    3. Нажмите Enter для завершения формулы. Формула =[@Факт]-[@План] введется во все ячейки столбца.

    Вместо традиционного введения формулы с помощью мыши (как описано в п. 2), в таблице доступен очень удобный способ введения формул с помощью клавиатуры. Начните со знака равенства, далее нажмите клавишу <– (влево) чтобы указать на соответствующее значение в столбце Факт, введите знак минуса, а потом дважды нажмите <–, чтобы указать на соответствующее значение в столбце План, нажмите Enter для завершения формулы.

    Рис. 4. В столбце Разница содержится формула

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

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

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

    При выполнении вышеописанных действий мы пользовались заголовками столбцов при создании формулы. Например, можно ввести в ячейку D2 прямым набором в строке формул: =C2-D2. Если вы вводите ссылки на ячейки, Excel по-прежнему будет автоматически копировать формулу в другие ячейки столбца.

    Как ссылаться на данные в таблице

    Формулы, находящиеся вне таблицы, могут ссылаться на данные этой таблицы по ее имени и заголовку столбца. Самостоятельно создавать названия этих элементов не нужно. Как обычно, кликайте на нужные ячейки, записывая формулу. Применение табличных ссылок дает значительное преимущество: при изменении размера таблицы (удалении или добавлении строк) названия в ней корректируются автоматически. Например, чтобы вычислить сумму всех данных в таблице на рис. 2, достаточно записать следующую формулу: =СУММ(Таблица3). Эта формула всегда возвращает сумму всех данных, даже если строки или столбцы таблицы добавляются или удаляются. Если вы измените название Таблица3, Excel автоматически откорректирует формулы, ссылающиеся на нее.

    Как правило, нужно сослаться на конкретный столбец таблицы. Следующая формула возвращает сумму данных из столбца Факт (но игнорирует строку итогов): =СУММ(Таблица3[Факт]). Обратите внимание: название столбца заключено в квадратные скобки. Опять же формула корректируется автоматически, если вы измените текст в заголовке столбца. Более того, Excel предоставляет подсказку, когда вы создаете формулу, ссылающуюся на данные в таблице (рис. 6). Функция автоматического завершения формул помогает написать формулу, отображая список элементов, присутствующих в таблице. Как только вы введете открывающую квадратную скобку, Excel предложит доступные аргументы.

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

    Автоматическая нумерация строк таблицы

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

    Рис. 7. Номера в столбце В сгенерированы с помощью формулы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

    Как обычно, формулу вычисляемых столбцов можно ввести в любую ячейку столбца =СТРОКА()-СТРОКА(Таблица4)+1. Когда вы вводите формулу, она автоматически распространяется на все остальные ячейки столбца Номер. Если функция СТРОКА используется без аргумента, она возвращает строку, в которой содержится формула. Если данная функция имеет аргумент, состоящий из многострочного диапазона, она возвращает первую строку данного диапазона.

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

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

    Рис. 8. После фильтрации номера строк таблицы уже не последовательны

    Если вы хотите, чтобы номера строк таблицы оставались последовательными и после фильтрации, нужна другая формула. В ячейку В3 введите: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;C$3:C5). Первый аргумент – 3 – соответствует функции СЧЁТЗ. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует скрытые строки, поэтому подсчитываются только видимые строки. Формула ссылается уже на столбец С. Это необходимо, чтобы избежать ошибки «циклическая ссылка» (рис. 9).

    Рис. 9. После фильтрации номера строк таблицы остались последовательными

    [1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 211–220.

    Создание и обслуживание электронных таблиц // Рекомендации по работе с Excel // PerfectXL

    Введение в создание и обслуживание качественных электронных таблиц

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

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

    21 Рекомендации и советы для экспертов по Excel

    1. Подготовка хорошей электронной таблицы

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

    2. Создавайте рабочие листы с расчетом на будущее

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

    3. Подумайте о порядке рабочих листов

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

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

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

    4. Выбирайте ясность, а не внешний вид

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

    5. Поддерживайте согласованность временной шкалы

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

    6. Организуйте информационный поток

    Постарайтесь организовать рабочие листы таким образом, чтобы информация всегда текла сверху слева вниз справа. Это значительно облегчает пользователю понимание того, как работает электронная таблица. Исключениями из правила являются «Вводная таблица» и/или «Рабочая таблица результатов». Размещение их в начале повышает ясность для пользователей, что особенно полезно для электронной таблицы, ориентированной на пользователя. При управлении потоком информации избегайте перекрестных зависимостей, поскольку они сильно снижают понятность. Всегда избегайте циклических ссылок, если необходима ссылка на какие-либо предыдущие данные.

    7. Пометьте столбцы и строки

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

    8. Сохраняйте формулы читаемыми

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

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

    9. Избегайте повторяющихся формул

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

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

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

    11. Не объединяйте ячейки

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

    12. Избегайте сокрытия данных

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

    Скрытая информация увеличивает риск ошибок.

    13. Встроенная проверка данных

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

    14. Сохранить стили на конец

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

    15. Сохраняйте единый стиль

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

    16. Сделайте условное форматирование простым

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

    17. Используйте положительные числа

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

    18. Четко определите единицы измерения и форматы чисел

    Убедитесь, что зрители всегда могут отследить единицы измерения и форматы чисел, используемые в электронной таблице. Вы можете сделать это, либо задав единицы измерения в свойствах ячейки в Excel, либо указав их в метке столбца. Но будьте осторожны: никогда не вводите символ валюты (€, $, £ и т. д.) сразу после значения! Это вызывает проблемы при использовании значения в формулах.

    19. Уточните свои источники

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

    20. Избегайте использования макросов

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

    21. Используйте простые диапазоны

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

    Подробнее о бизнес-курсе

    Excel и Передовой опыт работы с Excel

    Бизнес-курс Excel для бизнеса

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

    Узнать больше

    Индивидуальный бизнес-курс Excel

    Пройдите наш 4-недельный курс по основам моделирования в Excel. Учитесь у лучших пользователей Excel в мире и сразу же наблюдайте, как ваше использование Excel улучшается!

    Подробнее

    Программа бизнес-курса Excel

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

    Узнать больше

    Преподаватели бизнес-курса Excel

    Этот курс был разработан компанией PerfectXL (Infotron) в сотрудничестве с 11 признанными Microsoft «Самыми ценными профессионалами» и 3 самыми престижными университетами Нидерландов.

    Узнать больше

    Принципы правильного использования Excel

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

    Подробнее

    Создание и поддержка качественных электронных таблиц

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

    Подробнее

    Зачем использовать Excel?

    Сегодня люди начинают задавать этот вопрос все чаще. В 1990-е годы, на пике популярности Excel, это даже не вопрос, но времена меняются. Теперь рынок заполнен альтернативами…

    Узнать больше

    Никогда больше не бороться с этими ошибками VBA

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

    Узнать больше

    Передовой бизнес-курс Excel

    Обучение работе с Excel невероятно важно! Есть много курсов для быстрых советов и приемов, продуктивности, ярлыков и основ, но чего не хватало? Тренинг по Best Practice и дизайну.

    Узнать больше

    Хорошее форматирование в Excel

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

    Узнать больше

    5 лучших онлайн-блогов Excel

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

    Узнать больше

    Чем PerfectXL может вам помочь?

    Поделитесь своими вопросами о нашей продукции. Мы свяжемся с вами по номеру
    в течение следующих 48 часов.

    Как исправить формулы Excel, которые не вычисляют (обновить формулы)

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

    Формулы — это жизнь и кровь Microsoft Excel.

    Мы используем их для сложения чисел, вычитания дат и даже извлечения текстов.

    При вводе формулы результат приходит практически сразу!

    Но что произойдет, если это не так?

    Очевидно, 2+2 = 4! Не 5!

    Можем ли мы быть лучше в базовой математике, чем в Excel? Наверное, нет 🤣

    Итак, как исправить формулу, которая не вычисляется автоматически?

    В этом уроке вы узнаете, почему ваши формулы не обновляются и как это исправить!

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

    Table of Contents