Влияющие ячейки excel не активна

Обновлено: 07.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ячейки- ячейки, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5,ячейка B5 является влияемой на ячейку D10.

Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на другие ячейки. Например, если ячейка D10 содержит формулу =B5, ячейка D10 является зависимой от ячейки B5.

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

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

Щелкните Файл > параметры > Дополнительные параметры.

Примечание: Если вы используете Excel 2007; нажмите кнопку Microsoft Office , Excel параметры, а затем выберите категорию Дополнительные параметры.

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

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

Выполните одно из указанных ниже действий.

Трассировка ячеек, обеспечивающих формулу данными (влияющих ячеек)

Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.

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

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

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

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

Трассировка формул, ссылающихся на конкретную ячейку (зависимых ячеек)

Укажите ячейку, для которой следует найти зависимые ячейки.

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

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

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

Чтобы удалить стрелки зависимости по одному уровню, начиная с зависимой ячейки, наиболее далекой от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить зависимые стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.

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

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

Чтобы удалить все стрелки трассировки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Удалить стрелки .

Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.

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

Ссылки на текстовые поля, внедренные диаграммы или рисунки на таблицах.

Отчеты для отчетов в отчетах.

Ссылки на именуемые константы.

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

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

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

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

Доброго дня!
При выделении ячейки мы делаем какое либо действие (например закрашиваем ее) и после этого делаем эту ячейку не активной.

Дальше планирую делать следующее:
1. как можно закрасить несколько ячеек во круг выделенной ячейки
2. сделать условие: если нажали на какую либо ячейку, то повторное нажатие нужно сделать только на одну из закрашенных ячеек, иначе из всех окрашенных ячеек(макросом) убирается заливка, и закраска идет как при первом нажатии.(например, нажали на ячейку C5, закрасился диапазон B4:D6. если следующее нажатие будет по ячейке D6, то закрашиваются дополнительно все крайние ячейки: C5:E7. если мы бы нажали на ячейку F9, тогда убираем заливку с ячеек, которые были закрашены перед этим нажатием, и закрашиваем ячейки во круг F9. это E8:G10)

Если это подскажите, огромное спасибо!

Если это подскажите, огромное спасибо! lFJl

Если это подскажите, огромное спасибо! Автор - lFJl
Дата добавления - 02.09.2015 в 09:13

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

Ключом ко многим типам специальных выборов является диалоговое окно Выделение группы ячеек. Выберите Главная → Найти и выделить → Выделение группы ячеек для отображения диалогового окна Выделение группы ячеек (рис. 5.1). Другой способ открытия этого диалогового окна: нажмите клавишу F5, а затем в появившемся диалоговом окне Переход — кнопку Выделить.

Рис. 5.1. Диалоговое окно Выделение группы ячеек используется для выбора определенных типов ячеек

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

В табл. 5.1 приведено описание вариантов, доступных в диалоговом окне Выделение группы ячеек.

Таблица 5.1. Параметры диалогового окна Выделение группы ячеек

Параметр	Что он выбирает
Примечания	Только ячейки, содержащие примечания
Константы	Все непустые ячейки, которые не содержат формул. Этот параметр полезен, если у вас настроен шаблон и вы хотите очистить все ячейки для ввода (так, чтобы вы могли ввести новые значения) и при этом оставить формулы нетронутыми. Используйте флажки под положением переключателя формулы, чтобы выбрать, какие ячейки необходимо включить в выборку
Формулы	Ячейки, содержащие формулы. Уточните свой выбор с помощью флажков для типа результата: числа, текст, логические (логические значения TRUE или FALSE) или ошибки
Пустые ячейки	Все пустые ячейки
Текущая область	Прямоугольный диапазон ячеек, окружающих активную ячейку. Этот диапазон определяется окружающими пустыми строками и столбцами. Вы также можете использовать сочетание клавиш Ctrl+А
Текущий массив	Весь массив (используется для формул массива с множеством ячеек)
Объекты	Все графические объекты на листе. Параметр удобно использовать для удаления всех объектов
Отличия по строкам	Ячейки, которые отличаются от активной ячейки, если выбрана одна строка. Если выбрано несколько строк, производится то же самое сравнение, но для каждой строки ячейкой, с которой выполняется сравнение, является ячейка из того же столбца, что и активная ячейка
Отличия по столбцам	Ячейки, которые отличаются от активной ячейки, если выбран один столбец. Если выбрано несколько столбцов, производится то же самое сравнение, но для каждого столбца ячейкой, с которой выполняется сравнение, является ячейка из той же строки, что и активная ячейка
Влияющие ячейки	Ячейки, на которые есть ссылки в формулах в активной ячейке или выборке (в пределах активного листа). Вы можете выбрать либо напрямую влияющие ячейки, либо влияющие ячейки любого уровня
Зависимые ячейки	Ячейки с формулами, которые ссылаются на активную ячейку или выборку (в пределах активного листа). Вы можете выбрать либо напрямую зависимые ячейки, либо зависимые ячески любого уровня
Последняя ячейка	Нижняя правая ячейка на листе, которая содержит данные или имеет форматирование
Только видимые ячейки	Только видимые ячейки в выделенном диапазоне. Этот параметр полезен при работе со схемами или фильтрованным списком
Условные форматы	Ячейки, для которых применялось условное форматирование (с помощью команды Главная → Стили → Условное форматирование)
Проверка данных	Ячейки, которые настроены на проверку ввода данных (с помощью команды Данные → Работа с данными → Проверка данных). Установкой в положение всех вы выбираете все ячейки этого типа. С помощью положения этих же ячеек можно выбрать только те, которые имеют такие же правила проверки, что и активная ячейка

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

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