Содержание

Финансы в Excel

Главная Статьи Теория Проблемы Excel

Проблемы Excel

Ошибки

Потеря данных

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

При работе со сложными структурами расчетов Excel может критически закрыть программу без сохранения данных. Этот сбой особенно часто встречается в ранних версиях Excel. Так, например, легко «убить» файл Excel 2000-2003, попытавшись перемножить несколько миллионов ячеек на одном листе.

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

Слишком много форматов

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

Неоконченные вычисления

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

Ошибки отображения

В Excel 2007 разработчиками признана ошибка при умножении чисел, образующих результат в диапазоне 65534,99999999995 и 65535. Например, 850*77.1 показывает результат 100000. Причем, это именно ошибка отображения результата на экране – по факту в ячейке правильное значение 65535. Можно производить дальнейшие вычисления, используя именно это правильное значение.

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

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

Мы выделили главу с описанием «багов» в вычислениях в отдельную статью.

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

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

Псевдоошибки

В 99% случаев ситуации, преподносимые пользователями как «ошибки Excel», по факту таковыми не являются. Чаще всего проблемы связаны с непониманием формул или логики вычислений электронной таблицы.
Другая типичная причина проблем – это некорректное использование форматов чисел. Так, например, легко добиться визуальной ситуации, когда «2 * 2 = 5»:

В первом примере в ячейках A1:C1 на экране отображаются только целая часть чисел, в расчетах же участвуют вещественные числа: «2,3 * 2,3 = 5,29».

Во втором примере в ячейкe C2 установлен пользовательский формат отображения чисел. Для всех чисел отображается символ «5», вне зависимости от того, какое значение на самом деле хранится в ячейке. Если производить дальнейшие вычисления с ячейкой C1, то в расчетах будет участвовать правильное значение «4».

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

Внутренний тип значения может изменяться только при обновлении ячейки (вводе данных) при этом действуют такие правила:

Если текущий формат ячейки не «Текст», а вводимое значение является числом, датой или логической константой (TRUE, FALSE), то типу значения присваивается признак «число».
Если текущий формат ячейки «Текст», либо вводимое значение начинается с символа «’» (апостроф), то типу значения присваивается признак «строка».

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

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

Локализация

Интерфейс

В качестве примера не совсем корректного перевода в интерфейсе откройте диалог параметры Excel 2007 и выберите раздел «Формулы»:

Здесь почему-то использовано слово «узор», хотя кнопка служит для копирования, как формата, так и содержимого ячейки из соседней.

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

Формулы

Русская локализация формул Excel – это отдельная широко-обсуждаемая (в узких кругах специалистов) тема. Формулы рабочего листа имеют оригинальные и локализованные (переведенные) заголовки. Например, формула СУММ, используемая в русском Excel, в немецкой версии будет выглядеть как SUMME, а в английской (оригинальной) как SUM.

Excel хранит формулу в как в оригинальной, так и в локализованной версии – это можно увидеть, проверив свойства ячейки в VBA:

Решать проблемы Microsoft рекомендует с помощью специальных пакетов языковой поддержки:

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

Английское наименование функции	Русское наименование функции	Комментарий
COLUMNS ROWS	ЧИСЛСТОЛБ ЧСТРОК	разные префиксы «ЧИСЛ» и «Ч»
COUNT COUNTBLANK	СЧЁТ СЧИТАТЬПУСТОТЫ	существительное и глагол в русской версии
LEFT LEFTB	ЛЕВСИМВ ЛЕВБ	почему-то решили сэкономить 4 символа
DSUM DGET	БДСУММ БИЗВЛЕЧЬ	разные префиксы «БД» и «Б» только в русской версии, в английской всегда «D»

2. Использования буквы «Ё», которая практически не используется при компьютерном наборе текстов:

Английское наименование функции	Русское наименование функции	Комментарий
COUNT, COUNTIF	СЧЁТ, СЧЁТЕСЛИ
ISEVEN	ЕЧЁТН

3. Перевод на русский устоявшихся английских терминов:

Английское наименование функции	Русское наименование функции	Комментарий
NPV	ЧПС	экономическое выражение
IRR	ВСД	экономическое выражение
BIN2HEX	ДВ.В.ШЕСТН	также и остальные функции конвертации

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

Английское наименование функции	Русское наименование функции	Комментарий
ABS	ABS	широко применяемая функция не только в математических задачах

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

Английское наименование функции	Русское наименование функции	Комментарий
IMABS	МНИМ.ABS	и другие функции работы с комплексными числами

6. Слишком короткие наименования. Нелогичные аббревиатуры не способствуют запоминанию формулы:

Английское наименование функции	Русское наименование функции	Комментарий
IPMT	ПРПЛТ	лучше ПЛАТЕЖИПРОЦЕНТОВ
MID	ПСТР	лучше ПОДСТРОКА
TRIM	СЖПРОБЕЛЫ	лучше назвать целиком СЖАТЬПРОБЕЛЫ

7. Слишком длинные наименования. Совершенно непонятна причина, почему нельзя было для этих формул использовать сокращения:

Английское наименование функции	Русское наименование функции	Комментарий
GETPIVOTDATA	ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ	похоже на издевательство над пользователем
SUBTOTAL	ПРОМЕЖУТОЧНЫЕ.ИТОГИ	очень полезная функция, почему не ПОДИТОГИ

8. Неверный по смыслу перевод. Нет никакой интуитивной связи с действиями функций:

Английское наименование функции	Русское наименование функции	Комментарий
TRUNC	ОТБР	по смыслу должно быть от слова «ОКРУГЛЯТЬ»
INDIRECT	ДВССЫЛ	что здесь может означать «ДВ»?
EFFECT	ЭФФЕКТ	перевод дословный, но не по смыслу функции
QUOTIENT	ЧАСТНОЕ	в русском языке понятие «частное» не подразумевает только целую часть числа

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

Английское наименование функции	Русское наименование функции	Комментарий
INFO(«param»)	ИНФОРМ(«параметр»)	параметр указанный на английском языке (например, «osversion») работает в любой локальной версии, а на русском («версияос») только в русской версии, причем этот параметр не переводится автоматически
CELL(«param». )	ЯЧЕЙКА(«параметр». )	то же самое

10. Просто смешные аббревиатуры (попробуйте произнести вслух):

Английское наименование функции	Русское наименование функции	Комментарий
FVSCHEDULE	БЗРАСПИС	.
RANDBETWEEN	СЛУЧМЕЖДУ	.
TBILLPRICE	ЦЕНАКЧЕК
MMULT	МУМНОЖ

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

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

Термины

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

Самую большую путаницу вызывает, пожалуй, термин «Table» («Таблица»). Он употребляется в следующих случаях:

Связанный диапазон ячеек, имеющих общую формулу. Такие таблицы вводятся через сочетание клавиш Ctrl+Shift+Enter. Сейчас больше употребляется термин «массив».
Таблицы подстановки, использующие функцию ТАБЛИЦА().
Устаревший редко используемый вариант таблиц для поиска информации через специальные функции типа DGET,DSUM и пр. В этом случае таблица представляет собой прямоугольный диапазон с заголовками столбцов, а также диапазоны для формирования выборок.
Новый объект данных в Excel 2007. Такая таблица имеет общий стиль для строк и/или столбцов. Кроме того, есть возможность применять запись формул в специальной нотации со ссылками по имени таблицы. Изначально такая таблица была названа «List», затем все-таки переименована в «Table».
В русском языке вообще любой файл Excel зачастую также называют «таблицей», что вносит дополнительную путаницу.

Интерфейс Excel 2007

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

3. Найти некоторые диалоги старого меню без дополнительной справки чрезвычайно трудно. Так для примера попробуйте найти форму параметров группировки, вызываемую в старой версии через Данные \ Группы и структура \ Настройка. Честно признаюсь, сам не смог, пришлось «гуглить».

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

[expert_bq id=»1570″]Чтобы не вводить новые обозначения, стандартную ошибку средней обычно записывают в виде соотношения стандартного отклонения выборки и корня объема выборки. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Судя по формуле, разброс стандартной ошибки средней должен быть в 4 раза (корень из 16) меньше, чем разброс исходных данных, что и видно на рисунке выше. Чем больше наблюдений, тем меньше разброс средней.

Ошибки в Формулах ms Excel Ошибка Ссылка Означает • Ошибка знач

Что означает ошибка имя в excel

Если текущий формат ячейки не «Текст», а вводимое значение является числом, датой или логической константой (TRUE, FALSE), то типу значения присваивается признак «число».
Если текущий формат ячейки «Текст», либо вводимое значение начинается с символа «’» (апостроф), то типу значения присваивается признак «строка».

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

Английское наименование функции	Русское наименование функции	Комментарий
TRUNC	ОТБР	по смыслу должно быть от слова «ОКРУГЛЯТЬ»
INDIRECT	ДВССЫЛ	что здесь может означать «ДВ»?
EFFECT	ЭФФЕКТ	перевод дословный, но не по смыслу функции
QUOTIENT	ЧАСТНОЕ	в русском языке понятие «частное» не подразумевает только целую часть числа

Диагностика ошибок в формулах Excel

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

Сообщения об ошибках в Excel могут принадлежать к одному из 8 типов:

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

Ошибка #ЗНАЧ ! возникает, когда используется недопустимый тип аргумента, например, пользователь пытается сложить текстовое и числовое значение.

Ошибка #ДЕЛ/0 появляется, когда в формуле делается попытка деления на ноль.

Сообщение об ошибке типа #ИМЯ? появляется, когда Excel не может найти имя, используемое в формуле. Например, такая ситуация возникнет, если:

o текст ошибочно не был заключен в двойные кавычки;

o в ссылке на диапазон ячеек пропущен знак двоеточия (:). Ошибка #Н/Д является сокращением термина «Неопределенные Данные».

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

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

Сообщение об ошибке типа #ПУСТО! появляется, когда используется ошибочная ссылка на ячейку или диапазон, например, задано пересечение двух областей, которые не имеют общих ячеек.

Ввод и обработка данных в Excel

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

Рабочий лист в Excel 2007 состоит из 13384 столбцов и 1048576 строк.

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

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

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

Форматирование и защита рабочих листов

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

Форматирование в Excel включает в себя решение следующих вопросов:

• изменение шрифта, размеров, начертания и цвета символов;

• выравнивание и изменение ориентации текста и чисел в ячейках;

• создание и использование пользовательских форматов;

Работа с электронными таблицами

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

Рабочий лист электронной книги состоит из ячеек, каждая из которых имеет свой адрес: сочетание имени столбца и строки. Столбцы идентифицируются буквами латинского алфавита (А, B, C, D…), а строки

– арабскими цифрами (1,2,3…). Ячейка, в которой находится курсор, считается активной, то есть предназначенной для ввода данных. Например, адрес F10 говорит о том, что вводимая информация при активной ячейке F10 попадет именно в эту ячейку, а содержимое этой ячейки отразится в строке формул.

Ячейкам и блокам для удобства работы можно давать имена.

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

[expert_bq id=»1570″]В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие например, деление на 0. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Сегодня столкнулся с ситуацией когда в excel при протягивании не работали формулы, т.е. было два столбца и надо было в третьем столбце вычислить разницу первых двух столбцов. В итоге делается формула и протягивается вниз до конца, но результат из первой ячейки копируется в остальные ячейки.

Стандартная ошибка в Microsoft Excel

Чтобы исправить ошибку #ИМЯ?, проверьте правильность написания формулы. Если написана правильно, а ваша электронная таблица все еще возвращает ошибку, Excel, вероятно, запутался из-за одной из ваших записей в этой формуле. Простой способ исправить это — попросить Эксель вставить формулу.

Функция IFERROR (ЕСЛИОШИБКА) в Excel. Как использовать?

Функция IFERROR (ЕСЛИОШИБКА) в Excel лучше всего подходит для обработки случаев, когда формулы возвращают ошибку. Используя эту функцию, вы можете указать, какое значение функция должна возвращать вместо ошибки. Если функция в ячейке не возвращает ошибку, то возвращается её собственный результат.

Видеоурок

Что возвращает функция

Указанное вами значение, в случае если в ячейке есть ошибка.

Синтаксис

Аргументы функции

Дополнительная информация

Если вы используете кавычки («») в качестве аргумента value_if_error (значение_если_ошибка), ячейка ничего не отображает в случае ошибки.
Если аргумент value (значение) или value_if_error (значение_если_ошибка) ссылается на пустую ячейку, она рассматривается как пустая.

Примеры использования функции IFERROR (ЕСЛИОШИБКА) в Excel

Пример 1. Заменяем ошибки в ячейке на пустые значения

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

В примере, показанном ниже, результатом ячейки D4 является # DIV/0!.

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

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

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

Если вы пользуетесь версией Excel 2003 или ниже, вы не найдете функцию IFERROR (ЕСЛИОШИБКА) . Вместо нее вы можете использовать обычную функцию IF или ISERROR.

Пример 2. Заменяем значения без данных при использовании функции VLOOKUP (ВПР) на “Не найдено”

Когда мы используем функцию VLOOKUP (ВПР) , часто сталкиваемся с тем, что при отсутствии данных по каким либо значениям, формула выдает ошибку “#N/A”.

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

На примере выше, в списке студентов с результатами экзамена нет данных по имени Иван, в результате, при использовании функции VLOOKUP (ВПР) , формула нам выдает ошибку.

Как раз в этом случае мы можем воспользоваться функцией IFERROR (ЕСЛИОШИБКА) , для того, чтобы результат вычислений выглядел корректно, без ошибок. Добиться этого мы можем с помощью формулы:

Пример 3. Возвращаем значение “0” вместо ошибок формулы

Если у вас нет конкретного значения, которое вы бы хотели использовать для замены ошибок — оставляйте аргумент функции value_if_error (значение_если_ошибка) пустым, как показано на примере ниже и в случае наличия ошибки, функция будет выдавать “0”:

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

[expert_bq id=»1570″]Хорошей новостью является то, что это просто означает, что столбец недостаточно широк для отображения введенного вами значения. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Должно запуститься окно аргументов функции СЧЕТ. Синтаксис функции будет таким – =СЧЁТ(значение1;значение2;…). Ставите курсор в строку «Значение1» и зажимаете левую кнопку мыши, чтобы выделить весь диапазон выборки. Когда координаты отобразятся, жмите ОК.

Функция IFERROR (ЕСЛИОШИБКА) в Excel. Как использовать? ЭКСЕЛЬ ХАК

Если вы используете кавычки («») в качестве аргумента value_if_error (значение_если_ошибка), ячейка ничего не отображает в случае ошибки.
Если аргумент value (значение) или value_if_error (значение_если_ошибка) ссылается на пустую ячейку, она рассматривается как пустая.

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

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции