Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как скрыть или отобразить конкретный лист на основе значения ячейки на другом листе?

Как найти ячейки, связанные с внешними источниками в Excel

Как найти ячейки, связанные с внешними источниками в Excel

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

В ячейке B2 видно, что значение связано с рабочим листом с именем External File .xlsx (Лист 1, ячейка B2). Аналогичные ссылки также можно найти в ячейках B5, B7 и B8. Теперь изучите значение в ячейке B2 файла.

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

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

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

Поиск внешних ссылок с помощью функции поиска и замены

1. перейдите в меню Пуск > Найти и выберите > Заменить на ленте.

2. Во всплывающем окне (1) введите «* .xl * *» для команды Найти что, (2) нажмите Найти все и (3) нажмите CTRL + A на клавиатуре, чтобы выделить все найденные ячейки.

Связанные файлы должны быть в формате Excel (.xlsx, .xlsm, .xls), поэтому формула (ссылка) должна найти ячейки, содержащие «.xl». Звездочки (*) до и после «.xl» означают любой символ, поэтому поиск найдет все расширения файлов Excel.

3. Выбираются все ячейки, содержащие внешнюю ссылку (B2, B5, B7 и B8). Чтобы заменить их определенным значением, введите это значение в поле Заменить и нажмите кнопку Заменить все. При отсутствии значения все связанные ячейки очищаются. Вы можете ввести 55 в поле Заменить на, чтобы получить столбец Значение, как на рисунке ниже. В любом случае, замененные ячейки больше не связаны с другой книгой.

Найдите внешние ссылки с помощью ссылок редактирования

Еще один вариант — использовать функцию редактирования ссылок в Excel.

1. Перейдите на ленту > Данные > Изменить ссылки.

Читайте так же:
Как скопировать лист и автоматически переименовать в Excel?

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

Как сделать данные в Excel невидимыми

Бывают случаи, когда необходимо сделать некоторые данные невидимыми, не удаляя их. Другими словами, промежуточные результаты вычислений, нулевые значения, значение параметра TRUE или FALSE и т.д.

Существует, по крайней мере, два способа сделать это:

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

Во-вторых, вы можете изменить тип данных, сделав их невидимыми. Для этого выделите диапазон ячеек, содержащих данные, которые вы хотите скрыть, щелкните правой кнопкой мыши и выберите Формат ячеек → вкладка Число → выберите (все форматы) в поле Формат числа. Справа от Type: поставьте три точки с запятой «;; ;». (без перевернутых запятых) и нажмите OK.

В приведенном ниже примере показаны два типа данных: «Основной» — видимый, и «;;;» — невидимый, а также сумма чисел, одинаковая в обоих диапазонах.

Нажмите на картинку, чтобы увеличить ее

Чтобы сделать данные в ячейках видимыми, выполните те же действия. Снова выделите нужные ячейки и установите Тип: Основной, или просто удалите «;;;;» из этого поля.

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

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

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

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

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

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

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

Нажмите на фотографию, чтобы увеличить ее

Очевидно, что второй вариант гораздо проще для понимания.

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

Читайте так же:
Как создать выпадающий список с гиперссылками в Excel?

Во-первых, мы можем использовать формулу, основанную на функции ЕСЛИ

Например, если сумма выручки в ячейке B40 изначально является произведением количества проданного товара в ячейке B5 и цены товара в ячейке B25 (B40=B5*B25), то формула, скрывающая нулевое значение выручки в ячейке B40, выглядит следующим образом

Когда B5=0 и в периоде нет продаж, две кавычки скрывают нулевое значение дохода.

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

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

В Excel 2010 это можно сделать так:

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

На вкладке Главная → в группе Стили → выбираем команду Условное форматирование → в раскрывающемся списке выбираем Правила выделения ячеекРавно

Для увеличения нажмите на рисунке

Далее выполните следующую последовательность действий:

Установите в поле Формат ячеек, которые равны: значение 1 (1). Выберите в раскрывающемся списке Пользовательский формат (2). и в открывшемся диалоговом окне Формат ячеек на вкладке Шрифт в раскрывающемся списке Цвет: (3) измените Цвет темы «Авто» на «Белый, фон 1» (4). Нажмите OK (5) и OK (6).

Нажмите на изображение для увеличения

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

Для восстановления исходного форматирования правильным способом является удаление созданного правила из выделенных ячеек: Вкладка Главная * Группа Стили * Команда Условное форматирование → Удалить правила → Удалить правила из выделенных ячеек.

Создать невидимые нулевые значения очень просто.

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

Ссылки на ячейки текущего рабочего листа

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

Ссылки на рабочие листы к другим книгам

Есть два возможных сценария:

A) если открыты две книги и при выделении ячейки создается ссылка

=[Название книги.xls]Sheet3!$A$3 т.е. по умолчанию создается абсолютная связь.

Б) если книга, на которую указывает ссылка, закрыта, то в ссылке необходимо указать полный путь доступа к папке, где хранится книга

Пример: =’C:Имя папки[Название книги.xls]Лист5′!$A$3.

Обширные рекомендации

Читайте так же:
Как скопировать данные из / на несколько листов в Excel?

Объемные (трехмерные) ссылки — это ссылки на ячейки в нескольких рабочих листах книги.

В книге 100 листов. Чтобы вычислить сумму значений в диапазоне (B1:B15), вам понадобятся все 100 листов.

= СУММ(Лист1:Лист100!В1:В15)или =СУММ(’29 эи:32 эи’!В1:В15).

При работе с большими ссылками можно использовать до 11 функций, например: СУММА, ЗНАЧЕНИЕ CC, MAX, MIN.

Циклические обращения

Циклическая ссылка — это ссылка, зависящая от собственного значения.

В ячейке A1 есть формула =B5+C3-A1.

M S Excel отобразит сообщение об ошибке, которую необходимо устранить.

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

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

Таблица 10 — Референтный анализатор

Тип ссылкиОписаниеПример
АбсолютнаяИспользует для указания на ячейку ее фиксированное положение на листе. В абсолютной ссылке перед именем столбца и номером строки ставится знак $.=$A$5
ОтносительнаяУказывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например, на две строки выше.=А1
СмешаннаяСодержит относительную и абсолютную ссылки. В смешанной ссылке знак $ ставится перед абсолютной частью. Смешанные ссылки используются, чтобы при копировании зафиксировать только часть ссылки на ячейку.=$A1

На рисунке 16 показан результат копирования формулы, содержащей абсолютную (a), относительную (b) или смешанную (c, d) ссылку. Исходная формула находится в ячейке C2 и ссылается на ячейку A1. Затем формула из ячейки C2 копируется в ячейку D5. Ячейки, на которые ссылается исходная формула (в ячейке C2) и формула, которая была скопирована (в ячейке D5), выделены серым цветом.

Рисунок 16: Результат копирования деталей

Задания

Документы со следующими названиями должны быть сохранены в папке диска D.

Задание 1

1. Сохраните рабочую книгу с именем References_1.

2. На Листе 1 создайте таблицу товаров (Таблица 11).

3 Заполните столбцы стоимости: ячейка D2 должна иметь относительные ссылки, а ячейка E2 — смешанные ссылки. Скопируйте содержимое ячейки D2 в область D2:D4 и ячейки E2 в область E2:E4.

Таблица 11 — Товары

Сохранение документа.

Задание 2

1 Создайте рабочую книгу и сохраните ее под именем Ссылки_2.

2. На Листе 1 создайте таблицу, пример которой приведен в Таблице 12. Для этого

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

Читайте так же:
Как скрыть столбцы на нескольких листах в книге?

Для копирования содержимого ячейки B4 скопируйте диапазон B4:E9.

Таблица 12 — Вычисление функции

1.7.
2.8.
3.9.
4.10.
5.11.
6.12.

Сохранить документ.

Задание 3

Создайте папку проекта и сохраните ее под именем Directory. Переименуйте рабочий лист 1 в «Обменные курсы» и создайте таблицу 13.

Таблица 13 — Обменные курсы

2 Переименуйте Лист2 в Товары и введите таблицу Товары (Таблица 14). Рассчитайте столбцы E, F и G, используя формулы: Цена продажи=Цена покупки*2, переведите цену в белорусские рубли в колонках E, G.

3. Сохраните документ.

Таблица 14 — Товары

4. Создайте рабочую книгу и сохраните ее с именем Merchandise. Назовите Sheet1 Purchase и создайте таблицу Purchase (Таблица 15). В столбцы D-J можно ввести количество купленных товаров, а в столбце K — общий итог за неделю.

Таблица 15 — Закупы

5. Переименуйте лист 2 книги товаров в продажи и создайте аналогичную таблицу продаж.

6. Сохраните документ.

7. Создайте новую рабочую книгу и назовите ее «Баланс». На Листе1 создайте таблицу Баланс (Таблица 16). Рассчитайте столбцы C:G по формулам, используя прямые и косвенные ссылки на книги «Каталог» и «Товары».

Таблица 16 — Остаток

Остаток, кг = Покупка, кг – Продажа, кг;

Покупки, ? = Сумма покупок по каждому товару, пересчитанная в ?

Продажи, ? = сумма продаж по каждому товару, пересчитанная в ?

Прибыль, ? = ΣПродажи, ? — ΣЗакупки, ?;

Прибыль, BR = конверсия из ? к белорусскому рублю

Итого — сумма столбцов D, E, F и G

8. Сохраните документ и закройте книги Каталог, Товар и Баланс.

Вопросы контроля

1. ввод ссылок в MS Excel.

2 Обратитесь к ячейкам текущего рабочего листа. Приведите примеры.

3. Рассмотрите клетки других листов в книге. Приведите примеры.

4 См. ячейки в другой книге. Приведите примеры.

5. объемные и циклические ссылки. Приведите примеры.

6. Существуют абсолютные, относительные и смешанные адреса. Приведите примеры.

ЛАБОРАТОРНАЯ РАБОТА №5

Создание таблиц. Условное форматирование

Методологическая консультация

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

Рисунок 17: Структура таблицы

Эта таблица может состоять из следующих элементов:

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

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

Читайте так же:
Как создать и отфильтровать сводную таблицу в Excel 2007/2010/2013?

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

Измените размер маркера. Маркер изменения размера в правом нижнем углу таблицы позволяет изменять размер таблицы путем ее перетаскивания.

Управление данными таблиц

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

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

Форматирование табличных данных. Применение встроенного или пользовательского стиля таблицы позволяет быстро отформатировать данные таблицы. Можно также использовать экспресс-стили.

Вставка и удаление строк и столбцов в таблице. Можно добавить пустую строку в конец таблицы, соединить соседние строки или столбцы в таблице или вставить строки или столбцы в любое место таблицы.

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

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

Создание таблиц

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

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

Форматирование с помощью стилей. Выделен диапазон ячеек, которые будут включены в таблицу. Нажмите кнопку Формат как таблица в группе Стили на вкладке Главная и выберите нужный стиль. В программе Работа с таблицами/Конструктор можно редактировать контекст таблицы.

Чтобы добавить итоговую строку в таблицу, установите курсор в любую ячейку таблицы и выберите команду Итоговая строка (Работает с таблицами/Конструктор ® Параметры стиля таблиц ® Итоговая строка).

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

Чтобы удалить стили форматирования таблицы, используйте команду Преобразовать в диапазон (Работа с таблицами/Инструменты Конструктора® Преобразование в диапазон).

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector