Рубрики
Ноу-хау

Как поменять все ссылки на отдельный файл

Ваш файл ссылается на другой файл в сети. Кто-то перенес этот файл, и вам не хочется переделывать все формулы, которые ссылаются на него? Это можно сделать в два нажатия.

Это можно сделать следующим образом: на ленте выбрать вкладку Data, в группе Connections выбрать Edit links… В появившемся окне выберите файл, ссылку на который вы хотите изменить и нажмите на кнопку Change source.

P.S. Если ссылки на файл больше не понадобятся, нажмите на кнопку Break links (имейте в виду, что вся формула будет удалена, в ячейках будет только значение).

Рубрики
Ноу-хау

Расчетные поля в сводной таблице (calculated fields)

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

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

Освежить свои знания об основах сводных таблиц можно здесь.

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

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

Третий способ. Встроить вычисляемые поля в саму сводную таблицу. А вот это хороший способ. Делается это следующим образом. Когда сводная таблица уже построена, ставим курсор на сводную таблицу (при этом на ленте появляются две закладки, относящиеся к данной сводной таблице) и нажимаем на появившуюся закладку Options, затем Fields, Items, & Sets из блока Calculations, в выпавшем меню выбираем Calculated field. В 2007 офисе эта кнопка была на той же вкладке Options, только кнопка была отдельная.

В появившемся окне, в поле Name введите желаемое название поля. В поле Formula введите необходимую вам формулу, вставляя поля из списка внизу и нажимая кнопку Insert field.

Получится таблица с новым расчетным полем. Таблица готова!

Экселевский файл можно скачать отсюда.

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

Рубрики
Ноу-хау

Как вставить комментарий к формуле

Часто случается, что написанная нами формула получается излишне сложной для чтения и понимания. Доходит до смешного: например, этим расчетом мы пользуемся раз в год — наверняка в следующем году мы будем с большим трудом вспоминать, что было заложено в эту формулу. А каково будет другим людям! В частности нашим преемникам или (и их мне особенно жалко) аудиторам, особенно тем, которые делают level 3 assurance — то есть проверку корректности вычислений в электронных таблицах. Возможно они даже не читали статью о том, как читать сложные формулы.

Так давайте делать себе и им подарок — прокомментируем вводимую формулу. Я знаю два простых способа, как можно сделать комментарий к формуле: пользуясь встроенных функционалом комментирования ячейки и с помощью функции N (по-русски, Ч).

Первый способ — достаточно топорный. Мы просто вставляем комментарий к ячейке (на ленте Review нажать на New comment в блоке Comments). После своего имени (имени пользователя, под которым вы работаете) написать комментарий. Тут я хотел бы обратить особое внимание — пожалуйста, не удаляйте имя комментатора, порой бывает очень нужно спросить у него, что он имел в виду.

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

Второй способ — довольно изящный, но через неправильное место. Я увидел его впервые несколько лет назад, когда разгребал файлы одного из моих предшественников. Спасибо ему большое за эти пояснения! Этот способ работает для формул с вычислениями. В нужном месте формулы вставляется блок +N(«Ваш комментарий»). Функция N (по-русски, Ч) преобразует текст в число (если ваш комментарий не содержит чисел, то он вернет 0). Таким образом, вы получите некоторое вычисление, к которому в нужном вам месте будет прибавлен 0. Только аккуратней вставляйте такие комментарии в местах, где есть умножение деление или возведение в степень, может потребоваться добавить скобки.

Если вы знаете другой способ, пожалуйста, поделитесь со мной в комментариях или по электронной почте edward@youcanexcel.ru — я обязательно добавлю его в статью.

Рубрики
Ноу-хау

Горячие клавиши — навигация

Полный список горячих клавиш Microsoft Excel можно скачать на разных сайтах, например здесь. Их очень много, но надо ли знать все? По мне так — нет. Но определенное их количество определенно знать стоит. Использование горячих клавиш очень сильно ускоряет вашу работу. Особенно это актуально, если вы работаете с ноутбука без внешней мышки: как бы хорошо вы не владели точпадом, работа с ним все равно занимает много времени.

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

Всем хорошо известны следующие клавиши:

стрелки — двигают курсор на одну ячейку в выбранную сторону

Page Up/Down — двигают курсор на одну страницу вверх или вниз

Home — вернуться на начало строки (или до закрепления областей, если они стоят)

End, затем стрелка — перейти до конца диапазона данных в выбраном направлении

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

Ctrl + стрелка — работает так же, как и End, затем стрелка

Ctrl + Home — перейти на начало листа (сразу после закрепленных областей, если они закреплены)

Ctrl + End — перейти на конец диапазона данных на данном листе. Вот здесь все не так просто, как хотелось бы: если вы заполняли какие-то данные в конце диапазона данных, а потом из удалили, то диапазон данных все равно будет их захватывать до сохранения. Поэтому, если при нажатии на Ctrl + End курсор переходит куда-то далеко за пределы диапазона, попробуйте удалить все пустые строки/колонки и сохраните файл — после этого переход должен работать корректно

Удерживайте Shift — для выделения диапазона от текущей ячейки до выбраной (выбирать можно как мышкой, так и клавиатурой). Напимер, нажатие клавиш Shift + стрелка вниз выделит текущую ячейку и следующую вниз; аналогично, нажав на Shift + Home вы выделите все ячейки от текущей до начала строки. Соответственно, Shift + Ctrl + End выделит диапазон от текущей ячейки и до конца листа

Shift + пробел — выделить строчку, в которой стоит выделение. Если выделено несколько ячеек по вертикали, то выберутся соответствующие им строки. Не работает для разорванных диапазонов (выделится только последний)

Ctrl + пробел — выделить колонку. Если выделено несколько ячеек, то выделятся соответствующие им строки. Не работает для разорванных диапазонов (выделится только последний)

Ctrl + A — выделить весь лист (начиная с 2007 версии Excel, выделяет при первом нажатии весь диапазон данных, при втором — данные на всем листе)

Ctrl + Page Up/Down — переход на предыдущий/следующий лист книги

Ctrl + F6 — переход между открытыми книгами (это, на мой взгляд, гораздо эффективнее реализуется виндосовскими Alt + Tab, но на вкус и цвет…)

Отдельного внимания заслуживает клавиша Scroll Lock! Не многие ей пользуются, а зря. При нажатии на нее включается одноименный режим. Когда он включен, при нажатии на стрелки и Page Up/Page Down двигается не курсор, а экран. Это потрясающе удобно, когда вы проверяете глазами большой массив данных или вам надо вычитать большой документ оформленный в Excel или Word.

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

Рубрики
Ноу-хау

Умножить или разделить данные на определенное число

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

Итак, дано:

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

Что сделать, чтобы умножить каждое число в массиве на 1000.

(1) выделить ячейку со значением 1000 (в данном примере D10),
(2) нажать Копировать (или Ctrl + C, или Ctrl + Insert, как вам больше нравится) ,
(3) выделить диапазон с числами, которые необходимо умножить на 1000 (скопированное число) — в данном примере B5:D7,
(4) На ленте нажать «Вставить…»(«Paste…») => «Специальная вставка…» («Paste special…»)

(5) В появившемся окне выберите в секции Operations (вычисления) необходимое действие (в данном случае — умножить/multiply) и нажмите Ok.

Вуаля! Все числа массива перемножены!

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

Рубрики
Ноу-хау

Как вставить файл в экселевский и зачем

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

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

Вариант первый — создать лист в электронной таблице, вставить в него текстовое поле (Text box) и написать инструкцию в нем. Такой способ отлично работает, если мы не собираемся делать ничего замысловатого в этой инструкции в смысле форматирования. Еще я бы не советовал этот способ, если инструкция не помещается на одну страницу — иначе ее будет некомфортно читать.

Второй вариант — написать инструкцию отдельно, например, в ворде или любом другом редакторе (хоть запэдээфить). И вставить ссылку на этот файл в экселевскую таблицу. Это очень удобный и легкий для файла вариант. Особенно, если у компании, в которой вы работаете есть своя система хранения данных, такая как live-link (это не реклама), ну или на худой конец общий диск с контролируемым доступом.

В противном случае, можно вставить файл с инструкцией непосредственно в экселевский файл. Как это делается:

(1) на вкладке Insert ленты в блоке Text выбрать Object,
(2) в появившемся диалоговом окне на вкладке Create from File выбрать необходимый файл, при желании нажать на кнопку Change icon… и выбрать иконку и заголовок,
(3) поставить галочку Display as icon, убедиться что галочка Link to file НЕ стоит,
(4) нажать OK.

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

Смотрите приложенный пример такого файла.

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