Лучшее
Рубрики
Рецепты
Промокоды
Подкасты
Сервисы
Колонки
Лучшее
Рубрики
Рецепты
Промокоды
Подкасты
Сервисы
Колонки
Теории большого мира
Новости
Здоровье
Спорт и фитнес
Покупки
Технологии
Научпоп
Отношения
Реклама
Советы Технологии 27 ноября 2014

12 наиболее распространённых проблем с Excel и способы их решения

Представляем вам гостевой пост, из которого вы узнаете, как избежать самых распространённых проблем с Excel, которые мы создаём себе сами.
Фото автора Лиля Леднёва
Лиля Леднёва

правильно работать в Excel
Избранное

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

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

Вы не даёте заголовки столбцам таблиц

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

Пустые столбцы и строки внутри ваших таблиц

Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.

На одном листе располагается несколько таблиц

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

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

Данные одного типа искусственно располагаются в разных столбцах

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

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

Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.

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

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Рекомендуемый формат таблицы выглядит так:

Разнесение информации по разным листам книги «для удобства»

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

Информация в комментариях

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

Бардак с форматированием

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

  1. Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
  2. Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
  3. Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.

Объединение ячеек

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

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

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

Если ваша таблица будет презентоваться через LCD проектор

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

Страничный режим листа в Excel

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

Ещё больше полезной информации про Excel можно узнать на сайте Дениса.

Если нашли ошибку, выделите текст и нажмите Ctrl + Enter
Избранное
Комментарии
Азат Есенов
27.11.14 11:18
"Данные одного типа искусственно располагаются в разных столбцах" Очевидно, при публикации иллюстарции поменялись местами. Исправьте, пожалуйста.
Мария Верховцева
27.11.14 13:26
Спасибо. Исправили!
Zabolotny Alexey
27.11.14 13:42
у меня все так же ошибочно отображается.
dskuznetsov1980
27.11.14 11:20|изменено
Здравствуйте, согласен с вашими наблюдениями, но только кроме таблиц. Попробуйте по своей "рекомендуемой" форме сделать сводную таблицу, у вас ничего не выйдет. А уж если очень тяжело искать по столбцу датами, то можно рекомендовать воспользоваться автофильтрами (Фильтр по дате) встроенными в Excel. Там есть разбивки и на годы, месяцы и дни.
Zabolotny Alexey
27.11.14 13:45
очевидно таблицы-иллюстрации перепутали местами
Денис Батьянов
28.11.14 00:04
Алексей, спасибо вам за внимательность. ЛХ меня не предупредил о публикации и я вот сейчас с удивлением вижу, что статья вышла. Знал бы - проверил.
Алексей Гребенюк
27.11.14 11:38
А где проблемы?? Вот решите мне с помощью Excel задачу. Есть три даты: 01.01.2014 10.01.2014 20.01.2014 Сколько в среднем дней между датами? Решите пожалуйста формулами? Это проблема!
Азат Есенов
27.11.14 11:43
Используйте формулу =DAYS(конечная_дата;первая_дата) для каждой последовательной пары дат. Затем формулу =AVERAGE().
Денис Батьянов
28.11.14 11:23
Алексей, отчасти вы правы, просто редакция ЛФ переименовала мою статью: у меня она называлась "Хит-парад проблем с Excel, которые мы создаём себе сами", а у них "12 наиболее распространенных проблем с Excel и способы их решения". Акценты сильно смещены.
Слава Баранский
28.11.14 11:34
Не ЛФ, а ЛХ. Не смещайте акценты.
Костя Жи
04.12.14 14:26
В среднем дней между датами? Хорошо, давайте разберемся: у Вас три даты, верно? между датами, соответственно, разница в днях, будет между второй датой и первой и третьей датой и второй, так? Если так, то решение, в принципе, может быть таким (например): =СРЗНАЧ(B3:B4-B2:B3) 1) эта формула вводится как формула массива 2) данные находятся в диапазоне B2:B4 3) даты должны быть отсортированы по возрастанию
Denis Osipov
27.11.14 12:16
пример с цветовым оформлением - белым на зеленом, против черным по красному. Контраст и читаемость выше у второго, который "нет".
Денис Батьянов
28.11.14 00:01
Денис, посмотрите, как это будет выглядеть на проекторе.
Larry Woll
27.11.14 12:21
В бан уйти не хотите?
Aleksandr Vladimirovich
28.11.14 00:19
Ребят, подскажите в чем может быть проблема? Эксель перестал считать формулы - то есть вводишь значения и пока два раза по формуле не кликнешь, он ничего не посчитает и тем более не пересчитает автоматически? Может где просто галочку надо поставить?
Юлия Полякова
28.11.14 09:50
Excel 2010: Формулы-Параметры вычислений-Автоматически. Была аналогичная проблема)
Aleksandr Vladimirovich
28.11.14 16:56
большое спасибо!
Денис Батьянов
28.11.14 15:26
Если по F9 формулы успешно пересчитываются, то - да, стоит ручной пересчёт формул в файле. Юля правильно написала.
Артур Дёмин
28.11.14 22:04
"Данные одного типа искусственно располагаются в разных столбцах" Подскажите пожалуйста, как при таком построении таблицы правильно добавлять показатели по новому периоду? Добавлять новые строки снизу или внутри поле непосредственно "ответственного"?
Elena Beltyukova
04.12.14 17:15
Добавляйте данные снизу, а затем просто фильтруйте по периодам (месяц/год).
Татьяна Фарносова (Леоненко)
06.03.15 06:00
Здравствуйте, возникла такая проблема: при смене компьютера, документы (в т.ч. формата excel), находившиеся в нем сохранили в сервисе cloud. После скачки в новый комп, экселевские доки сохраняются не просто save, а выходит диалоговое окно save as. Получается, что при любом редактировании документа происходит создание копии. Как отключить это функцию?
Яр- Рандус
20.01.19 14:04
ИДИОТ Я ТАБЛИЦАМИ НЕ РАБОТАЮ ДА И ГЛУПО ИХ РИСОВАТЬ ПОЛУЧИТСЯ ИХ СОТНЯ ДЛЯ ОДНОГО ВЫЧИСЛЕНИЯ ПРОСТО В НИХ УТОНИТЕ
Alex Ponomar
20.01.19 14:11
Выздоравливайте.
Елена Козлова
31.03.20 08:00
почему у меня данные в эксель перепутались между собой
Владимир Силич
05.05.21 12:25
Здравствуйте, возникла такая проблема: после сохранения таблицы все столбики сливаются в один. Как эту проблему решить?
Наталия Горбачёва
24.07.23 21:56
что делать, если правильно выставлен числовой формат после запятой в десятичных цифрах, автосумма правильно всё считает, но как только набираю эти цифры от руки в следующей ячейке (такие условия отчета в работе), всё сбивается, и вместо 4,642 у меня показывает 4642,000. И такая цифра в другом бланке данного отчета. Такого у меня никогда не было до поломки компа. Мне старый комп опять дали и там был виндовс 2003, а отчеты набирала 2007, мне загрузили опять 2007, и после загрузки начались проблемы в ячейках
Информация
О проектеРубрикиРекламаРедакцияВакансии
Подписка
TelegramВКонтактеTwitterViberYouTubeИнициалRSS
Правила
Пользовательское соглашениеПолитика обработки персональных данныхПравила сообщества
18+Копирование материалов запрещено. Издание может получать комиссию от покупки товаров, представленных в публикациях