Трюк №79. Трюки с мегаформулами, которые ссылаются на другие рабочие книги
Формулы Excel становятся достаточно сложными, когда мегаформулы ссылаются на другую рабочую книгу. Нужно включать не только ссылки на ячейки, но и названия рабочих книг и листов, и, кроме того, полный путь, если книга, на которую ссылается формула, закрыта. Есть несколько способов упростить этот сложный процесс.
Написание таких формул с нуля может оказаться неожиданно быстрой решаемой задачей. В этом трюке мы покажем быстрый и простой способ, позволяющий конструировать сложные формулы без необходимости указывать названия рабочих книг и пути к файлам. Этот способ настолько прост, что о нем часто даже не догадываются.
Сначала убедимся, что вы правильно создаете ссылки на ячейки и рабочие листы. При написании формул лучше всегда избегать ввода ссылок на ячейки, имен листов и рабочих книг вручную, так как при этом можно случайно использовать неверный синтаксис или сделать опечатку. Большинству пользователей среднего уровня следует создавать ссылки на ячейки, листы и рабочие книги только при помощи мыши. Это определенно помогает предотвратить синтаксические ошибки и опечатки, однако если вы когда-либо пробовали делать это с вложенной функцией, вы знаете, что она быстро становится громоздкой и непонятной.
Например, взгляните на такую формулу: =INT(SUM('C:DocumentsFinanceSoftwareSalesLRegnow.xls]ProductSales'!C2:C2924)), в русской версии Excel =ЦЕЛОЕ(СУММ('С:DocumentsFinanceSoftwareSales[Regnow.xls]ProductSales'!С2:С2924)). Это достаточно простая функция СУММ (SUM), вложенная в функцию ЦЕЛОЕ (INT). Она ссылается на ячейки в закрытой рабочей книге, поэтому указан весь путь вместе с ссылками на ячейки и названиями рабочего листа и рабочей книги. Однако если понадобится вложить в эту функцию еще несколько дополнительных функций, это скоро станет весьма сложной задачей.
Мы покажем быстрый способ написания мегафункций, которые ссылаются на внешние рабочие книги. Трюк состоит в том, чтобы написать функцию в той рабочей книге, на которую хотите сослаться, в любой пустой ячейке. Если ссылаться только на один рабочий лист в этой рабочей книге, лучше использовать ячейку на этом листе.
Сначала, используя метод простого написания вложенных функций, описанный в разделе «Трюк №77. Определение количества указанных дней в любом месяце», сконструируйте формулу в любой пустой ячейке в той рабочей книге, на которую она будет ссылаться. Получив желаемый результат, вырежьте формулу из ячейки, перейдите в ту рабочую книгу, где должен появиться результат, выберите подходящую ячейку и вставьте.
Excel выполнит всю черную работу за вас, вставив названия рабочих книг и листов. Если понадобится добавить или модифицировать формулу, просто откройте рабочую книгу, на которую она ссылается, вырежьте формулу из исходной рабочей книги и вставьте в эту. Затем внесите необходимые изменения и вставьте модифицированную формулу в книгу, где хотите видеть конечный результат.