Ренат уже не в первый раз выступает гостевым автором на Лайфхакере. Ранее мы публиковали отличный материал от него о том, как составить план тренировок: основные книги и онлайн-ресурсы, а также пошаговый алгоритм создания тренировочного плана.
В этой статье собраны несложные приёмы, позволяющие упростить работу в Excel.
Особенно они пригодятся тем, кто занимается управленческой отчётностью, готовит разнообразные аналитические отчёты, основанные на выгрузках из 1С и других отчётах, формирует из них презентации и диаграммы для руководства. Не претендую на абсолютную новизну — в том или ином виде эти приёмы наверняка обсуждались на форумах или упоминались в статьях.
Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ
Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.
В остальных случаях есть два варианта:
- Использовать функцию ПРОСМОТР (LOOKUP).
У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию:
- Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).
Синтаксис функций:
• ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
• ИНДЕКС (массив; номер_строки; [номер_столбца]). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.
Как быстро заполнить пустые ячейки в списке
Задача — заполнить ячейки в столбце со значениями сверху (чтобы тематика стояла в каждой строке таблицы, а не только в первой строке блока книг по тематике):
Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:
Как найти ошибки в формуле
Вычисление отдельной части формулы
Чтобы разобраться в сложной формуле (в которой в качестве аргументов функции используются другие функции, то есть одни функции вложены в другие) или найти в ней источник ошибок, часто нужно вычислить её часть. Есть два простых способа:
- Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:
В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом. - Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:
В появившемся окне можно вычислять формулу по шагам и определить, на каком этапе и в какой функции возникает ошибка (если она есть):
Как определить, от чего зависит или на что ссылается формула
- Чтобы определить, от каких ячеек зависит формула, в группе «Формулы» на ленте нажмите на кнопку «Влияющие ячейки»:
- Появляются стрелки, указывающие, от чего зависит результат вычислений.
- Если отображается символ, выделенный на картинке красным цветом, то формула зависит от ячеек, находящихся на других листах или в других книгах:
- Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:
- Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.
- Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:
Как найти сумму (количество, среднее) значений ячеек с нескольких листов
- Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:
- Для этого в ячейку, в которой вы хотите видеть результат, введите стандартную формулу, например СУММ (SUM), и укажите в аргументе через двоеточие название первого и последнего листов из списка тех листов, что вам нужно обработать:
- Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:
Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).
Как автоматически строить шаблонные фразы
Используя базовые принципы работы с текстом в Excel и несколько простых функций, можно готовить шаблонные фразы для отчётов. Несколько принципов работы с текстом:
- Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
- Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
- Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.
- Пример создания шаблонной фразы с помощью формул:
- Результат:
- В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:
- ТЕКСТ (значение; формат)
- Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек».
Автоматизировать можно и более сложные тексты.
В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.
Как сохранить данные в каждой ячейке после объединения
- При объединении ячеек сохраняется только одно значение.
Excel предупреждает об этом при попытке объединить ячейки:
- Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):
- Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:
Как построить сводную из нескольких источников данных
- Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.
- Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:
- После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:
- При щелчке на неё появляется диалоговое окно:
В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных — выберите второй пункт:
- В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:
- После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы — на существующем или новом листе:
- Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:
Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)
- В данном примере в столбце A есть несколько текстовых строк, и наша задача — выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:
- Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:
- ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
- ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
- ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)
- Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:
- ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6
- А затем разделить эту разницу на длину той строки, которую мы искали:
- 6 / ДЛСТР (“МТС”) = 2
- Именно два раза строка «МТС» входит в исходную.
- Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):
- =(ДЛСТР(текст)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст);ПРОПИСН(искомый);“”)))/ДЛСТР(искомый)
- В нашем примере формула выглядит следующим образом:
- =(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1)
Источник: https://Lifehacker.ru/8-priyomov-raboty-v-excel/
Поиск и исправление ошибок в формулах MS Excel
Иногда мы все ошибаемся, в том числе и при использовании формул в MS Excel.
Однако не всегда наши ошибки являются именно ошибками, то есть приводят к выводу MS Excel соответствующего предупреждения и рекомендаций о том, как справится с возникшей проблемой.
Гораздо хуже, когда никакого предупреждения об ошибках не выводится, а мы явно видим, что результат совершенно не соответствует реальности.
К счастью, в наших руках несколько отличных инструментов для поиска «хитрых» ошибок в формулах MS Excel.
Влияющие и зависимые ячейки в MS Excel
Обычно в итоговых строках таблицы выводится некая сводка данных расположенных в других местах таблицы, а то и результат каких-то промежуточных вычислений. Если таких вычислений довольно много, то ошибиться в одном из них становится проще простого. Впрочем, исправить такую ошибку тоже очень просто… но только при условии, что вы знаете где искать.
Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые. Различить и запомнить их просто:
- Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б — это данные влияющие на результат вычисления формулы).
- Зависимые — содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).
Зная какие ячейки влияют на результат и от чего он зависит, мы без труда найдем причину проблемы.
Для иллюстрации я подготовил простейшую табличку с данными. В ней есть два условных показателя и коэффициент, а итоговый расчет осуществляется простой плюсовкой обоих показателей с последующим умножение на результат: (Показатель 1 + Показатель 2) х Коэффициент.
Дополнительно я создал ещё одну простую формулу: она умножает наш «Итог» на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6.
Давайте перейдем на вкладку «Формулы» и в группе «Зависимости формул» посмотрим на два крайне полезных в работе инструмента: «Влияющие ячейки» и «Зависимые ячейки».
Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке
Выделяю результирующую ячейку «Итог» и нажимаю кнопку «Влияющие ячейки». Оп, и на листе MS Excel появляются синие стрелки ведущие от трех используемых в вычислениях ячеек к итоговой формуле. Согласитесь, нагляднее представить себе понятие «влияющая ячейка» невозможно.
А теперь зависимые ячейки. Весь лист теперь как на ладони
Теперь нажимаю (не убирая курсор с ячейки «итоги») кнопку «Зависимые ячейки» и на экране появляется ещё одна стрелка. Она ведет к ячейке «результат с поправкой», то есть той, результат вычислений в которой зависит от текущей.
Теперь я намеренно «порчу» таблицу, внося в исходные данные ошибку — подставляя букву вместо цифры. Мгновение, и я уже точно знаю откуда эта ошибка взялась. Мне даже искать ничего не пришлось — все вполне наглядно и графически красиво.
Ошибка возникшая из-за замены цифры на букву. Excel подсветил «ошибочное» вычисление красной стрелкой
Отключить графику можно в любой момент нажав на кнопку «Убрать стрелки».
Чтобы убрать стрелки с листа MS Excel воспользуйтесь соответствующей кнопкой
Исправление ошибок возникающих в MS Excel
Впрочем, не всегда ошибка представляется нам так явно, иногда нужно ещё понять какое из промежуточных вычислений приводит к сбою. Есть в MS Excel отличный помощник и для этого.
Ищем ошибку в формуле Excel
Снова выделим наш пресловутый «итог» и щелкнем мышью по кнопке «Вычислить формулу». Открывшееся окно содержит в себе поле в котором записана «проблемная» формула.
Пока ничего не обычного, верно? Нажимаем на кнопку «Вычислить» внизу и обращаем внимание как формула в окне начнет меняться — вместо идентификаторов ячеек в ней появятся цифры из соответствующих ячеек.
А вот и ошибка — как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей
Одно нажатие на кнопку — один шаг в вычислениях. Уже на третьем шаге мы наглядно увидим в какой именно части формулы происходят вычисления порождающие ошибку, и теперь без труда сможем их исправить.
Вот и всё. Пользуйтесь этими несложными методами, и без труда «расщелкаете» любую возникшую при вычисления в MS Excel ошибку.
Источник: http://bussoft.ru/tablichnyiy-redaktor-excel/poisk-i-ispravlenie-oshibok-v-formulah-ms-excel.html
Зависимости ячейки
MulTEx » 15 Сентябрь 2014 Дмитрий 14385 просмотров
Вызов команды:
MulTEx -группа Ячейки/Диапазоны —Ячейки —Зависимости ячейки
С помощью данной команды можно легко просмотреть все ячейки, которые «завязаны» на выделенную. Иначе говоря, можно увидеть на какие ячейки влияет выделенная и от каких зависит(какие ячейки влияют на неё). Команда древовидно отображает все эти зависимости наглядно и удобно. Присутствует возможность не только просмотра значения и формул всех завязанных на текущую ячеек, но и переход на эти ячейки в один клик и возврат к анализируемой ячейке так же в один клик.
Для чего вообще нужна подобная команда? К примеру, выделенная ячейка содержит формулу:
=СУММ(C6*E19;СУММ(СУММ('Статьи затрат.xls'!C2;'Статьи затрат.xls'!C4;'Статьи затрат.xls'!C6;'Статьи затрат.xls'!C7;'Статьи затрат.
xls'!C8);СУММ('Производственная себестоимость'!B4;'Производственная себестоимость'!B5)))
при этом от значения этой ячейки зависят значения еще нескольких ячеек(т.к. изменение значения этой ячейки изменит значение других, т.к. в них формулы ссылаются на эту ячейку), в данном случае это несколько ячеек листа Расчет.
Команда Зависимости ячейки построит наглядную карту зависимостей такой ячейки:
Как можно видеть — все наглядно и просто. Сразу показаны и адреса ячеек, и их значения, и их формулы. И это позволит достаточно быстро и точно произвести анализ зависимости ячеек и даст понять насколько безболезненным будет изменение значения той или иной ячейки для общего функционала файла или таблицы, а также выявить ссылки на другие книги. При этом команда отдельно выделяет зависимости ячейки на закрытые книги, т.к. эти значения не могут быть вычислены или могут быть неверными и так же могут повлиять на результат формулы. Такие ссылки выделяются в отдельный блок Недоступные ссылки:
Так же есть возможность отобразить ссылки в формуле исключительно на ячейки других листов и книг.
Примечание: если среди влияющих и зависимых ячеек присутствую диапазоны, содержащие более 20-ти ячеек, то для таких диапазонов будут отражены значения и формулы первых 20-ти ячеек.
Это сделано для более быстрой работы функции, к тому же в большинстве случаев видеть большее количество значений совершенно нет необходимости, да и просматривать в этом окне их будет неудобно.
Проще в этом случае перейти прямо из окна к нужному диапазону и посмотреть всю необходимую информацию «на месте».
Показать зависимости — принудительно обновляет информацию по зависимостям активной ячейки. Карта зависимостей отражается исключительно для активной ячейки. Если выделено несколько ячеек — карта зависимостей будет показана только для верхней левой ячейки.
В полях карт зависимостей отображается следующая информация:
- имя книги, в которой расположена ячейка;
- имя листа;
- адрес влияющих/зависимых ячеек;
- значение влияющей/зависимой ячейки(если ячеек несколько — отображаются все значения ячеек через точку-с-запятой);
- формула влияющей/зависимой ячейки(если ячеек несколько — отображаются все формулы ячеек через точку-с-запятой).
Если для ячейки нет влияющих или зависимых ячеек — поле карты зависимостей остается пустым.
Выделить проверяемую ячейку — по нажатии будет активирована книга и лист с ячейкой, для которой была отображена текущая карта зависимостей. Может пригодиться, когда производится изучение зависимостей путем перехода по ячейкам (см. Переход к ячейкам по клику).
Обновлять зависимости при выделении ячейки — если включен, то обновление карты зависимостей будет происходить каждый раз, когда будет выделяться ячейка на листе. Т.е. можно просто при открытой форме щелкать по ячейкам и просматривать зависимости каждой из них.
Примечание: Обновление не срабатывает, если выделение ячеек произошло через опцию Переход к ячейкам по клику.
Показывать только связи с другими книгами — если включен, то в окнах зависимости ячейки будут показаны только те ссылки, которые ссылаются на другие книги, в том числе закрытые.
Например, на картинке выше это будет книга Статьи затрат.xls.
Показывать только связи с другими листами — если включен, то в окнах зависимости ячейки будут показаны только те ссылки, которые ссылаются на другие листы книги с проверяемой ячейкой, а так же ссылки на листы других книг, в том числе закрытых.
Для каждого окна карты зависимостей так же доступна функция выделения цветом зависимых или влияющих ячеек:
- — при нажатии на данную иконку все доступные влияющие/зависимые ячейки будут закрашены бледно-красным цветом. При этом если ранее для этих ячеек была применена другая заливка — она будет заменена.
- — при нажатии на данную иконку со всех влияющих/зависимых ячеек будет убрана заливка ячеек. Это означает, что цвет заливки будет просто убран и ячейка будет без заливки.
Применяя эти опции следует учитывать, что программа не запоминает состояние заливки ячеек до выделения и вернуть прежнюю заливку, если она была, уже не получится. Связано это с тем, что ссылки могут содержать слишком большое количество ячеек и если сохранять их заливку для отмены, то программа может либо спровоцировать ошибку, либо выделение и отмена будут работать очень долго.
Можно, конечно, для отслеживания зависимостей воспользоваться и стандартным средством — вкладка Формулы-группа Зависимости формул—Влияющие ячейки/Зависимые ячейки.
Но в данном случае результат будет выглядеть следующим образом:
Из этого несомненно можно понять большую часть, но если присмотреться к изображению выше, то видно, что стрелка к той же ячейке С8, на которую влияет выделенная С3 перекрывается стрелками влияющих ячеек C13, C14:C18 и практически не заметна.
А сразу увидеть ячейки с других листов и книг вообще не получится — для этого надо будет сначала дважды щелкнуть мышью на стрелке(которая ведет к значку в виде таблицы) и в появившемся окне выбрать одну(!) ссылку.
А если ссылок больше 10? Сколько раз надо будет щелкать туда-сюда? Пока будем щелкать уже забудем, что хотели узнать…Так же в этом окне никак не помечены ссылки на закрытые книги — они просто ничем не отличаются от ссылок на доступные источники(открытые книги).
О том, что ссылка недоступна узнать можно будет только после того, как попробуем на неё перейти.
И еще нюанс: при отображенных зависимостях одной ячейки отобразить зависимости другой нельзя, пока не уберем стрелки от первой ячейки(нажав Убрать стрелки). Т.е.
для просмотра зависимостей второй ячейки, необходимо сначала убрать отображение зависимостей текущей. После чего заново отобразить стрелки через меню. Несколько затратно по времени, особенно если ячеек куча.
Источник: https://www.excel-vba.ru/multex/zavisimosti-yachejki/
Что такое в Excel зависимые и влияющие ячейки — Трюки и приемы в Microsoft Excel
На многих листах ячейки могут содержать сложные взаимосвязи. Попытка отладить формулы может оказаться проще, если вы понимаете два ключевых понятия: влияющие и зависимые ячейки.
- Влияющие ячейки — приводят к вычислению результата формулы. Влияющую напрямую ячейку указывают непосредственно в формуле, а косвенно влияющие ячейки не используются непосредственно в формуле, но применяются ячейкой, на которую ссылается формула.
- Зависимые ячейки — эти ячейки с формулами зависят от конкретной ячейки (влияющей). От влияющей ячейки зависят все ячейки с формулами, которые используют данную ячейку. Ячейка с формулой может зависеть напрямую или косвенно.
- Нажмите клавишу F2. Ячейки, которые используются непосредственно формулой, будут обрисованы, а цвет будет соответствовать ссылке на ячейку в формуле.
- Откройте диалоговое окно Выделение группы ячеек (выберите Главная ► Редактирование ► Найти и выделить ► Выделение группы ячеек). Установите переключатель в положение влияющие ячейки, а затем в положение только непосредственно или на всех уровнях. Нажмите кнопку ОК, и Excel выберет влияющие ячейки для формулы.
- Нажмите Ctrl+[ для выбора всех влияющих напрямую ячеек на текущем листе.
- Нажмите Ctrl+Shift+[ для выбора всех влияющих ячеек (прямых и косвенных) на текущем листе.
- Выберите Формулы ► Зависимости формул ► Влияющие ячейки, и Excel нарисует стрелки, указывающие на влияющие ячейки. Нажмите эту кнопку несколько раз, чтобы увидеть дополнительные уровни влияния. Выберите Формулы ► Зависимости формул ► Убрать стрелки, чтобы скрыть стрелки.
- Откройте диалоговое окно Выделение группы ячеек. Установите переключатель в положение зависимые ячейки, а затем в положение только непосредственно (для нахождения напрямую зависимых ячеек) или на всех уровнях (для нахождения напрямую и косвенно зависимых ячеек). Нажмите кнопку ОК. Excel выберет ячейки, которые зависят от активной ячейки.
- Нажмите Ctrl+] для выбора всех напрямую зависимых ячеек на текущем листе.
- Нажмите Ctrl+Shift+] для выбора всех зависимых ячеек (прямых и косвенных) на текущем листе.
- Выберите Формулы ► Зависимости формул ► Зависимые ячейки, и Excel нарисует стрелки, указывающие на зависимые ячейки. Нажмите кнопку несколько раз, чтобы у видеть дополнительные уровни влияния. Выберите Формулы ► Зависимости формул ► Убрать стрелки, чтобы скрыть стрелки.
Выявление влияющих ячеек для ячейки, содержащей формулу, часто проливает свет на то, почему формула некорректно работает. И наоборот, также полезно знать, какие ячейки формул зависят от конкретной ячейки. Например, если вы собираетесь удалить формулу, то можете захотеть проверить, не зависит ли что-нибудь от нее.
Идентификация влияющих ячеек
Вы можете идентифицировать ячейки, используемые формулой в активной ячейке, несколькими способами.
Заметьте, что все эти методы ограничены выявлением влияющих ячеек только на том листе, который содержит формулу.
Идентификация зависимых ячеек
Следующими способами вы можете определять ячейки с формулами, которые используют конкретную ячейку.
Опять же, эти методы ограничены выявлением зависимых ячеек только на текущем листе.
Источник: http://excelexpert.ru/chto-takoe-v-excel-zavisimye-i-vliyayushhie-yachejki
Excel, работа с листами | Женский журнал онлайн — EVA.RU | Цифровой мир и программное обеспечение
Открыть тему в окнах
- Помогите, кто разбирается, пожалуйстаПервый раз столкнулась с необходимостью в Excel работать с большим кол-вом листов, нужно сделать следующую операцию:упрощенно, задача такая: в файле более 100 листов, нужно из каждого листа взять ячейку А1.формула =Лист1!$А$1=Лист2!$А$1=Лист3!$А$1и далее, не могу понять, какой формулой задать, чтобы номер листа менялся автоматически при расятгивании столбца?И еще вопрос, если названиям листа присвоены разные имена без нумерации, как задать, чтобы изменялся на +1.
- ***не могу понять, какой формулой задать, чтобы номер листа менялся автоматически при расятгивании столбца? — что значит “ расятгивании столбца ”?***И еще вопрос, если названиям листа присвоены разные имена без нумерации, как задать, чтобы изменялся на +1. — название листа значение не имеетP.S. Если я вас правильно понял, у вас имеется 100 листов табличных данных, информацию с которых вам надо переработать и вывести сводку на один лист ?
- — что значит “ расятгивании столбца ”? Это, я так «местечково» выразилась)) Ищу функцию, которая позволит написать одну формулу, а остальные (по аналогии с формулами на одном листе с изменением порядкого номера), будут изменяться с шагом +1, как порядковый номер ячейки. А потом увидела, что в некоторых файлах, листы имеют названия без нумерации)- *Если я вас правильно понял, у вас имеется 100 листов табличных данных, информацию с которых вам надо переработать и вывести сводку на один лист ?*Угу. Не пойму, как задать обращение ко всем этим листам из одной и той же ячейки((
- ***Угу. Не пойму, как задать обращение ко всем этим листам из одной и той же ячейки(( — из ячейки вы можете задать обращение не к листу, а к «ячейки на другом листе» или к нескольким ячейкам на разных листах, и произвести с ними разные действия, результат которых отобразиться в запрограммированной ячейки сейчас подготовлю пример и дам ссылку
- Да, Вы правы — «обращение к ячейке на другом листе» Спасибо, буду ждать
- Спасибо за пример!То что Вы написали, я понимаю, по Вашему примеру, мне придется вручную суммировать все 100 листов.в фотоальбоме моя таблица, я хочу задать диапазон листов, чтобы из каждого листа ячейка А1 отображалась в сводной таблице. http://eva.ru/albumpage/177812/759295.htm
- Сорри! Отлучался! сейчас посмотрю
- Наташ! Еще раз извини! В более ранней версии Offise Excel была функция “Прогрессия”, в 2007 найти не могуРучной ввод можно немного автоматизировать:выбираешь ячейку, ставишь в ней знак ( = ),затем переходишь на требуемый лист, выделяешь нужную ячейку и жмешь “Enter” и т.д.но если понадобится в сводную таблицу вводить столбец с данными из ячейки “В1”, то это уже можно сделать копированием уже введенных ячеек
- Я, конечно, извратилась сейчас)) в word заменив массив одинаковых чисел (целым блоком на сквозную нумерацию листов, скопированную из excel (через функцию – показать формулу и переименовав все листы), потом перебросив их в excel, но сейчас ищу, чтобы в excel найти эту опцию)) Нужно что-то типа консолидации данных, только не могу применить к моему примеру.
- Для импорта данных из Word можно применить функцию из закладкиДанные/ Получить внешние данные / Из текстаНо это тоже ручная работа
- Если еще актуально. Сделать стандартными средствами Excel это нельзя. Можно использовать макрос.Sub EnterNum() Dim x, y As Integer n = 0 x = ActiveCell.Row y = ActiveCell.Column CurrentSheet = ActiveSheet.Name For Each Sheet In Sheets() Sheet.Activate Worksheets(CurrentSheet).Cells(x, y).Formula = «=» & ActiveSheet.Name & «!A1» x = x + 1 NextEnd Sub
- Да, конечно, актуально)) Спасибо, я сегодня попробую ночью.Если это возможно, напишите мне, пожалуйста, в личку или на почту …, у меня есть несколько задач по оптимизированию работы с файлами, готова обсудить возможности сотрудничества.
- Макрос работает — гениально)) СПАСИБО!Появились два нюанса:- у меня некоторые листы названы через тире, к примеру «01-04″И вот если в названии листа стоит тире, макрос его воспринимает как минус — и эту строчку не считает.Если любой другой символ ставить в названии листа (01_04), то работает. Можно как-то это избежать или нужно все листы переимнеовать «тире» на «подчеркивание»?- если макрос запускать на листе, то он и этот лист считает, т.е. к примеру 100 лист — сводный.и он в последней ячейке столбца ставит то, что стоит в этом листе в обозначенной ячейке.В любом случае, я принцип поняла, еще раз спасибо)) Но мое предложение в силе!
Источник: https://eva.ru/technics/messages-2256867.htm
Как Связать Ваши Данные из Разных Книг в Excel
По мере того как вы используете и создаете больше рабочих документов Excel, у вас может возникнуть необходимость связать их между собой. Может быть вы захотите написать формулу, которая использует данные с разных Листов. Или может даже вы напишете формулу, которая будет использовать данные из разных Книг (стандартное название рабочего документа Excel — workbook (анг.))
Я понял, что если я хочу, чтобы мои файлы были понятными и аккуратными, лучше разделять большие Листы с данными от формул, которые их обрабатывают. Я часто использую одну Книгу или Лист, который обрабатывает все что мне нужно.
В этом уроке, вы узнаете как добавлять ссылки на данные в Excel. Сначала, мы научимся связывать данные из разных Листов в пределах одной Книги. Затем, мы перейдем к связыванию нескольких Книг Excel, чтобы импортировать и синхронизировать данные между файлами.
Как Можно Быстро Организовать Ссылку Между Данными в Книгах Excel (Смотри и Учись)
Я покажу вам два примера, как можно связать данные из ваших электронных таблиц. Вы узнаете как извлекать данные из другой таблицы в Excel и как организовать связь между двумя Книгами. Мы также рассмотрим основные примеры написания формул, использующих данные из различных Листов в пределах одного рабочего документа.
Давайте посмотрим иллюстрированное руководство по связыванию Листов и Книг в Excel.
Основы: Как Добавить Ссылку между Листами в Excel
Давайте начнем с того, что научимся создавать формулы, которые используют данные с других Листов. Вы возможно уже знаете, что рабочий документ (Книга) Excel, может содержать несколько рабочих страниц (Листов). Каждый лист имеет свою вкладку, и вы можете переключаться между ними щелкая по вкладкам в нижней части документа Excel.
Сложные документы могут содержать большое количество листов. И в какое-то время, вам придется написать формулу которая будет использовать данные из разных листов.
Может вы будете использовать один лист в документе для всех ваших формул, которые обрабатывают данные, а данные будете распределять по отдельным листам.
В моем документе три вкладки с Листами (Sheet). Я собираюсь написать формулу, которая будет работать с данными из каждого листа.
Давайте разберемся как написать многостраничную формулу, которая работает с данными из разных Листов в пределах одного рабочего документа.
1. Создайте в Excel новую Формулу
Многие формулы в Excel начинаются со знака равно (=). Дважды щелкните или начните печатать в ячейке, и вы начнете создавать формулу, в которую вы хотите вставить ссылку. Например, я собираюсь написать формулу, которая будет суммировать значения из разных ячеек.
Я открываю запись знаком =, и затем щелкаю по первой ячейке на текущем листе, чтобы создать первую часть формулы. Затем, я напечатаю знак +, чтобы добавить значение из второй ячейки в этой формуле.
Начните записывать формулу в ячейке и щелкните по первой ячейке, чтобы бы включить ее в формулу, но при этом, пока не завершайте ввод формулы.
Пока не завершайте ввод формулы и не жмите Enter! Вам нужно оставить формулу открытой перед тем как вы переключитесь на другой Лист.
2. Переключитесь между Листами в Excel
Пока ваша формула не закончена, щелкните на вкладке другого листа в нижней части программы. Очень важно, чтобы вы не закрывали формулу, перед тем как вы щелкните по другой ячейке, которую вы хотите вставить в формулу.
Перейдите на другой Лист Excel
После того, как вы переключитесь на другой лист, щелкните по ячейке которую вы хотите добавить в формулу. Как вы можете видеть на картинке ниже, Excel автоматически запишет часть формулы, которая будет обращаться к ячейке на другом Листе.
Заметьте на картинке ниже, что для ссылки на ячейку на другом Листе, Excel добавил надпись «Sheet2!B3», которая ссылается на ячейку В3 на листе с названием Sheet2. Вы можете написать это вручную, но когда в щелкаете по ячейке, Excel делает эту надпись автоматически за вас.
Excel автоматически записывает за вас часть формул, чтобы добавить ссылку на ячейку с другого Листа.
3. Закройте Excel формулу
Теперь, вы можете нажать enter, чтобы завершить создание мульти-страничной формулы. Когда вы это делаете, Excel вернется туда, где вы начали создавать формулу и покажет вам результат.
Вы можете продолжать набирать формулу, включая в нее ячейки с других листов и другие ячейки на этом же листе. Продолжайте комбинировать ссылки со всего рабочего документа, на все данные, которые вам нужны.
Повышаем Уровень Сложности: Как Создать Связь Между Несколькими Книгами Excel
Давайте узнаем, как извлечь данные из другого рабочего документа. С помощью этого навыка, вы сможете писать формул, которые связывают данные из разных рабочих документов Excel.
Для этого раздела урока, вам понадобится два рабочих документа, которые вы можете скачать в виде бесплатного приложения к этому уроку. Откройте их оба в Excel, и следуйте дальнейшим указаниям.
1. Откройте Две Рабочие Книги
Давайте начнем создавать формулу, которая будет использовать данные из двух различных документов.
Самый простой способ сделать это, это открыть два документа Excel, одновременно и расположить их рядом друг с другом. Я использую для этого функцию Windows Snap, чтобы каждое окно занимало пол экрана. Вам нужно иметь две книги перед собой, чтобы писать формулу, которая будет взаимодействовать с двумя Книгами.
На рисунке ниже, я открыл два рабочих документа один рядом с другим, для которых я буду создавать формулы. В моем примере, я веду бизнес, и покупаю различные продукты, и продаю их в разные страны. Итак, я использую разные рабочие документы, чтобы отслеживать покупки/продажи и данные по стоимости.
В этом примере, я использую разные рабочие документы, чтобы отслеживать покупки/продажи и данные по стоимости.
2. Начните Создавать Формулу в Excel
Цена за которую я покупаю товары, может измениться, а так же может измениться соотношение курсов к валюте в которой я получаю выплаты. Поэтому мне нужно иметь список в котором отслеживаются курсы валют и умножать их на стоимость моих закупок. Это подходящая возможность, чтобы использовать информацию из двух файлов и записать формулу, которая будет использовать данные обоих файлов.
Давайте рассмотрим число баррелей нефти которую я покупаю каждый месяц и цену за баррель.
Сначала в ячейке из колонки Cost (ячейка C3), я начинаю создавать формулу, щелкнув в ней и нажав знак равно (=), а затем щелкаю по ячейке В3, чтобы взять значение для количества. Теперь, я жму знак *, чтобы уможножить количество на курс.
Итак, ваша формула, пока должна выглядеть так:
=B3*
Не закрывайте пока вашу формулу. Убедитесь, что она осталась открытой перед тем как перейти к следующему шагу; нам нужно указать Excel данные по цене, на которую будет умножаться количество.
3. Переключитесь на Другой Рабочий Документ Excel
Пришло время переключиться на другой рабочий документ, вот почему так важно иметь данные из двух документов между собой, когда вы работаете с двумя Книгами.
Пока ваша формула открыта, щелкните по вкладке другой книги. Затем, щелкните по ячейке в вашей второй Книге, чтоб связать данные из двух файлов Excel.
Excel автоматически добавит ссылку на другой рабочий документа, как часть формулы:
=B3*[Prices.xlsx]Sheet1!$B$2
- После того как вы нажмете Enter, Excel автоматически рассчитает окончательную стоимость умножив количество в первой книге на цену из второй книги.
- Потренируйте свои навыки работы в Excel, умножая количество товара или объем на соответствующую ему цену из документа «Prices«.
- Главное, расположить книги одну рядом с другой, и создавать формулу переключаясь между ними.
При этом, ничего не мешает вам использовать больше, чем два рабочих документа. Вы можете открыть много рабочих документов, на которые вы хотите ссылаться, и создавать формулы, связывающие данные между множеством листов.
Как Обновлять Данные При Работе с Несколькими Книгами
Когда вы используете формулы, которые ссылаются на разные книги Excel, вам нужно позаботится о том, как будут обновляться данные.
Итак, что же произойдет, если данные в Книге, на которую вы ссылаетесь, поменяются? Будут ли выполнены автоматические изменения в вашей Книге, или вам нужно обновлять ваши файлы, чтобы извлечь последние изменения и импортировать их?
Ответ такой, «зависит», и в основном зависит от того, открыты ли два документа одновременно.
Пример 1: Открыты Обе Книги Excel
Давайте проверим пример используя те же книги, что мы использовали раньше. Обе книги открыты. Давайте посмотрим, что произойдет, если мы изменим цену нефти за баррель с 45$ на 75$:
На сриншоте ниже, вы можете видеть, что когда мы изменили цену на нефть, результаты в другом документе автоматически поменялись.
Важно помнить: если оба рабочих документа открыты одновременно, изменения будут внесены автоматически в реальном времени. Когда вы меняете переменную, то информация в другом документе будет автоматически изменена или пересчитана, на основании новых данных.
Пример 2: Если Один Рабочий Документ Закрыт
А что если вы открыли только один рабочий документ? Например, каждое утро мы обновляем цены на наши товары и валюты, а вечером мы рассматриваем влияние изменений на наши покупки и продажи.
В следующий раз, когда вы откроете документ, который ссылается на другие таблицы, вы должны увидить сообщение подобное тому, что приведено ниже. Вы можете щелкнуть по кнопке Обновить (Update), чтобы извлечь полседние данные из документа, на который ссылается формула.
Щелкните по кнопке Обновить в выпадающем окне, когда откроете документ, чтобы извлечь полседние данные из друго файла.
Вы так же можете видеть меню, где можно нажать на копку Включить содержимое (Enable Content), чтобы автоматически обновлять данные между файлами Excel.
Подводим итоги и продолжаем обучаться работе в Excell
Написание формул которые берут данные из разных Листов и Книг, важный навык, при работе в Excel. Тем более, что это совсем не сложно, если вы знаете как это делать.
Посмотрите эти дополнительные уроки, чтобы узнать больше о том как работать с программой Excel и как обрабатывать в ней данные. Эти уроки, хорошо подойдут для того, чтобы продолжить обучение.
Дайте мне знать в х, если у вас остались какие-то вопросы, по поводу того как организовать связь между различными рабочими документами Excel.
Источник: https://business.tutsplus.com/ru/tutorials/how-to-link-data-in-excel-workbooks—cms-28950
Загрузка...
