Google Docs – это веб-версия приложений MS Office.

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

Особенно полезны для работы с данными Google Spreadsheets, или Google Таблицы – аналог приложения Excel.

Они могут пригодиться вам в следующих целях:

• для совместного планирования отпуска с друзьями и расчета бюджета;

• ведения реестров с вашими клиентами/заказами/поставщиками и т. д., которые заполняются несколькими людьми параллельно;

• онлайн-координации любых совместных действий.

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

Все данные в примерах вымышлены.

Как сделать документ Google Таблиц быстрее и «легче»

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

• Оптимизировать количество вкладок (если есть несколько вкладок с маленькими таблицами или списками – попробуйте объединить их в одну).

• Если есть формулы поиска данных (ВПР/VLOOKUP, ИНДЕКС/INDEX, ПОИСКПОЗ/MATCH и другие), сохранить часть формул как значения (если не нужно будет эти значения обновлять). Например, если у вас подтягиваются данные за много месяцев с помощью VLOOKUP – оставляйте текущий месяц формулами, а остальные данные сохраняйте как значения.

• Не заливать строки/столбцы цветом целиком (и вообще стараться избегать излишнего форматирования).

• Проверить, нет ли условного форматирования на (излишне) большом диапазоне ячеек.

• Не ставить фильтр на все столбцы.

• Очистить примечания, если их много и они не нужны.

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

Наглядное представление для длинных списков и таблиц (чередующиеся строки)

Если в вашей таблице десятки и сотни строк («Операции», «Сотрудники», «Клиенты», «Продукты», «Сделки» и т. д.) и несколько столбцов – вам будет непросто сравнивать соседние строки, они станут сливаться. Выход – чередующаяся заливка строк, которую можно сделать как в Excel с помощью встроенного инструмента (Главная → Форматировать как таблицу), так и в Google Таблицах (Формат → Чередование цветов):

Как выделить уникальные элементы из списка в Google Таблицах?

Дано: список с текстовыми значениями в Google Таблице.

Задача – получить список, содержащий только уникальные значения из исходного.

Для этого нужна функция UNIQUE, единственный аргумент которой – исходный список.

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

Но что если исходный список будет со временем меняться (то есть к нему станут добавляться новые строки)? Не менять же формулу каждый раз. Чтобы функция UNIQUE автоматически обновляла список уникальных элементов при обновлении исходного списка (а COUNTUNIQUE, соответственно, обновляла количество), в качестве аргумента укажите не диапазон A2:A14, а диапазон A2:A.

Ставим ссылки автоматически: Функция Hyperlink (гиперссылка)

Функция HYPERLINK (ГИПЕРССЫЛКА) возвращает ссылку на страницу в сети. Ее первый аргумент – собственно ссылка (записывается в кавычках), второй – текст, который будет отображаться в ячейке (тоже в кавычках):

Результатом работы этой формулы будет ссылка. При выделении ячейки с ссылкой сам адрес появится во всплывающей ячейке:

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

Для начала зайдем на сайт, введем название любой книги (или текст «Название книги», как в примере) в поиск и заберем ссылку из адресной строки:

Уберем все, что после знака «равно» (после него мы будем добавлять в формулу название книги из ячеек первого столбца):

=HYPERLINK("http://www.mann-ivanov-ferber.ru/book/search?query="&A2;A2).

Первый аргумент в этой формуле – это ссылка на поиск на сайте с прикрепленным названием книги из ячейки A2. Второй – само название книги из той же ячейки (чтобы в ней отображалось название, а не текст ссылки). Протягиваем формулу на весь столбец и получаем ссылки на все книги из списка:

Функцию HYPERLINK можно использовать в связи с другими функциями. Например, я использую ее с IF и DETECTLANGUAGE (подробнее о последней вы сможете прочитать в разделе «Переводим текст прямо в Google Таблице»):

=HYPERLINK(IF(DETECTLANGUAGE(A2)="EN";"https://www.amazon.com/s/url=search-alias%3Daps&field-keywords="&A2;"http://www.ozon.ru/?context=search&text="&A2)).

В общем виде:

=HYPERLINK(IF(DETECTLANGUAGE(A2)="EN";ссылка на англ. сайт"&A2;"ссылка на рус. сайт"&A2)).

Эта формула проверяет, на каком языке указано название книги в ячейке A2. И если язык английский, то выдает ссылку на поиск этого названия в Амазоне, а иначе (формально – если текст не на английском, по факту это означает русский в моем файле) на поиск его же в Озоне.

Функция Importrange (перенос данных из файла в файл)

Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.

Для чего она может, например, пригодиться?

• Вам нужны актуальные данные из файла ваших коллег.

• Вы хотите обрабатывать данные из файла, к которому у вас есть доступ «Только для просмотра».

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

Эта формула позволяет получить копию диапазона из другой Google Таблицы. Форматирование при этом не переносится – только данные.

Синтаксис формулы следующий:

IMPORTRANGE(spreadsheet_key; range_string)

spreadsheet_key (ключ_таблицы) последовательность символов в атрибуте «key=» (ключ) в ссылке на таблицу. В новых Google Таблицах необходимо вставить ссылку полностью. Иначе говоря, ключ таблицы – это длинная последовательность символов в конце ссылки на таблицу после «spreadsheets/…/».

Пример формулы:

=IMPORTRANGE("abcd123abcd123"; "sheet1!A1:C10")

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

=ImportRange("https://docs.google.com/a/company_site.ru/spreadsheet/ccc?key=0A601pBdE1zIzHRxcGZFVT3hyVyWc";"Лист1!A1:CM500")

В файле, в котором вы введете эту формулу, будет отображаться диапазон A1:CM500 с Листа 1 из файла, который находится по соответствующей ссылке.

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

Так, если в ячейку A2 вы введете ссылку на документ, из которого нужно загрузить данные, а в ячейку B2 – ссылку на лист и диапазон, то загружать данные можно будет с помощью следующей формулы:

=IMPORTRANGE(A2;B2)

Функция Vlookup (перенос данных из таблицы в таблицу)

Эта функция – аналог функции ВПР в Excel. Она необходима для переноса данных из одной таблицы в другую.

У нее следующие аргументы:

VLOOKUP (искомое значение; таблица; номер столбца; интервальный_просмотр)

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

Например, в отдельном файле у вас есть стандартная ставка перевода книг с английского – по трем категориям сложности.

А в таблице с текущими проектами вы просто указываете категорию сложности (это и будет искомое значение), по которому функцией ВПР (VLOOKUP) подтягиваете ставку.

Таблица – это диапазон данных, из которого вы подтягиваете информацию.

Аргумент «Номер столбца» определяет, из какого столбца ТАБЛИЦЫ (а не листа – это важно!) вы будете брать данные.

Интервальный_просмотр обычно равен нулю – в таком случае будет вестись точный, а не приблизительный поиск.

Пример:

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

Примечание. Если исходная таблица в будущем расширится, указывайте диапазон не как в примере – $A$2:$C$13, а без строк – $A:$C. Иначе уже из 14-й строки, когда она добавится, формула не будет подтягивать данные.

ФУНКЦИЯ MATCH (СРАВНЕНИЕ ДВУХ СПИСКОВ)

Функция ПОИСКПОЗ (в английской версии Excel и в Google Таблицах она называется MATCH) позволяет определить порядковый номер элемента (обычно текста, записанного в ячейке) в определенном списке.

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

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

Синтаксис функции следующий:

• MATCH (искомое_значение; список; точный поиск);

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

Пример:

В примере ошибка #N/A возникает только в тех случаях, когда соответствующего элемента нет во втором списке.

Сочетание функций INDEX + MATCH – когда обычный VLOOKUP не работает

К сожалению, функция ВПР (VLOOKUP) не работает, когда искомые значения в исходной таблице находятся не в первом столбце. Но вы можете воспользоваться сочетанием функций ПОИСКПОЗ (MATCH – обсуждалась ранее) и ИНДЕКС (INDEX).

Функция ИНДЕКС возвращает элемент из списка по его порядковому номеру. А порядковый номер вы определяете с помощью MATCH.

Пример:

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

Переводим текст прямо в Google Таблице: Функция Googletranslate

В Google Таблицах есть функция, позволяющая переводить текст прямо в ячейках:

Синтаксис функции следующий:

GOOGLETRANSLATE(text,[source_language], [target_language])

text – это текст, который нужно переводить; можно, конечно, взять текст в кавычки и записать прямо в формулу, а можно сослаться на ячейку, в которой он содержится;

[source_language] – язык, с которого мы переводим;

[target_language] – соответственно, язык, на который мы переводим.

Второй и третий аргументы задаются двухзначным кодом: es, fr, en, ru. Их тоже можно указать в самой функции, но можно брать из ячейки, а язык исходного текста и вовсе можно автоматически определять.

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

А как быть, если мы все-таки хотим переводить не только на английский, но и на другие языки? И при этом не хотим каждый раз указывать язык исходника вручную?

Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент – текст, язык которого нужно определить:

Осталось ее «внедрить» в функцию TRANSLATE. Укажем справа от текста, на какие языки хотим переводить исходный текст (в столбце B). В столбец C введем формулу GOOGLETRANSLATE. Первым аргументом будет текст в столбце A, вторым – функция DETECTLANGUAGE, которая определит, с какого языка переводить, а третья – код языка из столбца B.

Как и с любой другой функцией, прелесть здесь в автоматизации. Можно быстро поменять текст или язык; быстро перевести одну фразу на десять языков и т. д. Конечно, мы понимаем, что это текст онлайн-переводчика – качество будет соответствующим.

Quel merveilleux tableau!

Вставляем в ячейки Google Таблиц изображения: Функция Image

Функция IMAGE позволяет добавлять в ячейки Google Таблиц изображения. У функции следующий синтаксис:

IMAGE(URL, [mode], [height], [width])

URL – единственный обязательный аргумент. Это ссылка на изображение. Ссылку можно указать напрямую в формуле, взяв в кавычки:

=IMAGE(http://howtoexcel.ru/wp-content/uploads/2015/12/Run-or-Die.jpg)

Или же поставить ссылку на ячейку, в которой ссылка хранится:

= IMAGE(A2)

Аргумент mode может принимать четыре значения (если его пропустить, по умолчанию будет первое):

1:-изображение растягивается до размеров ячейки с сохранением соотношения сторон;

2:-изображение растягивается без сохранения соотношения сторон, целиком заполняя ячейку;

3:-изображение вставляется с оригинальным размером;

4:-вы указываете размеры изображения в третьем и четвертом аргументах функции [height] и [width].

[height], [width] соответственно нужны только при значении аргумента mode = 4. Они задаются в пикселях.

Итак. Допустим, у нас есть список книг и мы хотим добавить обложки:

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

Хорошо. А если бы мы указали mode = 1?

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

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

Большинство менеджеров, офисных работников, руководителей, аналитиков, специалистов работают с таблицами и массивами данных.

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

Большинство рутинных и повторяющихся операций можно как минимум упростить, как максимум – полностью автоматизировать!

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

Ренат Шагабутдинов:

В моей преподавательской практике нередки случаи, когда ученики начинают заниматься индивидуально и сразу рассказывают про какой-нибудь огромный отчет, подготовка которого требует нескольких часов (а чаще дней) и много нервов. В подавляющем большинстве случаев мы находим решение, которое позволяет сделать отчет за 15–20 минут или за час.

Если есть сомнения, оптимально ли вы работаете с конкретным файлом, списком, таблицей, воспользуйтесь следующим критерием: представьте, что объем работы увеличился в пять раз, в десять, в двадцать…

А операции остались теми же самыми.

Сможете ли вы при существующей методике или наборе формул обработать в 20 раз больший объем строк/столбцов/таблиц? Или это займет слишком много времени, так как вы многое делаете вручную? Если последнее верно, то вам наверняка стоит пересмотреть свои методики и приемы в данном случае.

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

Все данные в примерах вымышлены.

Как сделать файл Excel быстрее и «легче»

• Поменять формат на. XLSX (формат версий 2007 и более поздних),XLSM (с макросами) или. XLSB (самый быстрый и сжатый, с макросами). Удивительно, но до сих пор многие пользуются по инерции файлами версии 2003 (расширение. XLS), хотя они медленнее и могут занимать в разы больше дискового пространства.

• Не заливать строки/столбцы цветом целиком (и вообще стараться избегать излишнего форматирования).

• Не ставить фильтр на все столбцы (их в файле новых версий 16 384. В вашей таблице обычно намного меньше).

• Проверить, нет ли условного форматирования на (излишне) большом диапазоне ячеек.

• Очистить примечания, если их много и они не нужны.

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

• Не сохранять кэш сводных таблиц (Параметры сводной таблицы → Сохранять исходные данные вместе с файлом).

• Удалить неиспользуемые именованные диапазоны (диспетчер имен вызывается сочетанием клавиш Ctrl + F3).

• Удалить ненужные макросы, если они есть.

Создавайте имена

Диапазонам Excel можно присваивать имена, благодаря которым формулы выглядят более осмысленными:

=Продажи*Ставка_налога

Вместо

=A7*$B$5

Для присвоения имени его нужно ввести в небольшое поле слева от строки формул или же, если у вас есть заголовки, выделить данные и нажать на кнопку «Присвоить из выделенного» на ленте инструментов в разделе «Формулы» и выбрать подходящий пункт:

Теперь в любой формуле вы сможете использовать диапазон «Продажи», не ссылаясь на конкретные ячейки:

Как автоматически установить ширину столбцов/строк?

Выделяем столбцы (строки) (для этого нужно навести курсор именно на названия столбцов (строк)).

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

Несопоставимое сопоставимо: Диаграмма с двумя осями

У нас есть несовместимые по объему данные – количество сотрудников в компании и выручка (план-факт). Но мы хотим сравнить их в динамике на одном графике. Строим обычный график по всем данным:

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

Меняем тип на «График» и указываем, что количество сотрудников отображается на вспомогательной оси:

Результат:

Как выделить значения лучше (хуже) среднего?

Выделяем данные, нажимаем на «Условное форматирование» → «Правила отбора первых и последних значений» → Выбираем нужный вариант (например, выше среднего):

В появившемся окне выбираем вариант оформления:

Как отсортировать список не по алфавиту?

В Excel можно быстро отсортировать данные в алфавитном порядке (или в обратном алфавитном порядке).

Но как быть, если элементы списка должны сортироваться в произвольном порядке?

Например, у вас есть отчет по продажам в разных городах, для каждого из которых указан федеральный округ, и его нужно сортировать именно по последнему. Причем ЦФО должен идти на первом месте, СЗФО – на втором, а ПФО – на третьем. По алфавиту их отсортировать не получится.

Как быть? Заходить в Сортировку (раздел «Данные» на ленте инструментов), выбирать сортировку по региону и в списке «Порядок» выбрать «Настраиваемый список».

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

Теперь вы сможете сортировать список в нужном вам порядке.

Как создать выпадающий список в ячейке?

Очень просто. Заходите в раздел «Данные» на ленте инструментов, нажимайте на кнопку «Проверка данных», и появляется такое окно:

В поле «Тип данных» выберите список, а в поле «Источник» поставьте ссылку на ячейки с элементами, которые должны отображаться в выпадающем списке (можно и не ставить ссылку на ячейки, а перечислить элементы через точку с запятой). Нажимайте ОК, и в ячейке появится выпадающий список:

Как быстро скопировать рабочий лист?

Конечно, вы можете кликнуть правой кнопкой на ярлыке рабочего листа и нажать в появившемся меню на «Переместить или скопировать». Но есть более быстрый способ: держите зажатой клавишу Ctrl и перемещайте ярлык листа вправо.

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

Выделите ячейки с формулами, наведите курсор мыши на границу выделения и, удерживая правую кнопку мыши, отведите диапазон вправо – и верните на место. В появившемся меню нажмите «Копировать только значения». Проверено – процедура занимает всего три-четыре секунды.

Второй путь – воспользоваться специальной функцией в надстройке PLEX (о ней – позже).

Как повернуть таблицу на 90°?

Выделяем таблицу и копируем ее (Ctrl + C): кликаем правой кнопкой мыши на ту ячейку, в которую хотим вставить перевернутую таблицу, в появившемся контекстном меню нажимаем на «Транспонировать».

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

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

Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак =), ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны.

Как построить сводную из нескольких источников данных

Если вам нужно построить сводную сразу из нескольких источников данных, придется добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.

Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить».

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

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

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

Как просуммировать ячейки с нескольких листов?

Если у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе, в ячейку, где вы хотите увидеть результат, введите стандартную формулу (например, СУММ (SUM)), но укажите в аргументе через двоеточие название первого и последнего листов из списка тех, что вам нужно обработать:

Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий:

=ФУНКЦИЯ(первый_лист: последний_лист!ссылка на диапазон).

Как разделить фразу, записанную в одну ячейку, на отдельные слова в отдельных ячейках?

Выделяем ячейки и кликаем на кнопку «Текст по столбцам» (на ленте в разделе «Данные»).

В появившемся окне выбираем «С разделителями» (если нужно разбить текст на блоки – например, номера кредитной карты по четыре цифры, – то выберите «фиксированной ширины») и нажимаем далее:

В следующем окне выбираем разделитель – в данном случае пробел – и нажимаем «Далее».

В последнем окне выбираем, куда хотим разместить данные, и нажимаем «Готово».

Как быстро выявить кириллицу и латиницу?

Если нам нужно определить, где латинские символы, а где кириллические (и, например, быстро выявить, где по ошибке введена латинская «c» вместо кириллической), поменяйте шрифт в ячейке на какой-то, не поддерживающий кириллицу, например на Bauhaus 93.

Как убрать ненужные текстовые элементы и примечания из целого массива ячеек?

Пример – список книг. В названиях некоторых книг в конце присутствует пометка «(т)», ее нужно убрать:

Для этого выделяем диапазон, нажимаем Ctrl + H и заполняем диалоговое окно (в разделе «Найти» – что нужно удалить, раздел «Заменить» оставляем пустым), нажимаем «Заменить».

Если же мы хотим убрать любые фразы в скобках, используем символ «звездочка» (*), который эквивалентен любому тексту. Удалятся все тексты в скобках, например: (т), (б), (переплет).

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

Маленькие графики в ячейках: Спарклайны

У вас есть много рядов данных: допустим, данных по продажам книг (цифры в примере случайные, но это не столь важно), и вы хотите посмотреть динамику по каждому ряду, но при этом не создавать отдельных диаграмм. Для этого подойдут спарклайны – мини-графики в ячейках, которые появились в версии Excel 2010.

Спарклайн – очень простой инструмент для быстрого визуального сравнения рядов данных.

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

Выбираем диапазон с данными. На вкладке «Конструктор», которая появляется на ленте инструментов Excel при выделении ячеек со спарклайнами, можно изменить их внешний вид:

• поменять цвет спарклайна и маркеров;

• выделить первую, последнюю, максимальную и минимальную точки;

• добавить ось;

• выделить отрицательные точки.

Кроме того, в ячейках со спарклайнами можно вводить текст.

Пример с типом спарклайна «Выигрыш/Проигрыш». Спарклайн показывает, были ли по соответствующему ряду возвраты (отрицательные «продажи») и нулевые продажи; и если были – то когда:

А что делать, если у вас Excel 2003 или 2007?

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

Ее первый аргумент – сам текст, а второй – количество повторов.

Выберите подходящий символ – можно какую-нибудь красивую иконку из шрифта Wingdings. Чтобы посмотреть, какие символы есть в шрифте и какие у них коды, напишите в столбец числа от 1 до 255, а правее введите функцию СИМВОЛ (шрифт в правом столбце нужно, соответственно, поменять на Wingdings).

Для продаж книг подойдет символ с номером 38 ☺.

Хорош и обычный квадратик – у него номер 110 в шрифте Wingdings. Обратите также внимание на шрифты Webdings, Wingdings 2 и Wingdings 3.

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

Как вычислить часть формулы?

Допустим, вы столкнулись с длинной и сложной формулой вроде этой:

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ВПР($A3;Отчет2015!$A$2:$Z$1616;СТОЛБЕЦ()-14;0);ВПР(ВПР($A3; мэппинг!$A$2:$B$999;2;0);Отчет2015!$A$2:$Z$1616;СТОЛБЕЦ()-14;0));0).

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

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

Второй способ – инструмент «Вычислить формулу» на ленте инструментов в разделе «Формулы». Он поможет проанализировать пошаговое вычисление сложной формулы.

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

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

Появятся стрелки, указывающие, от чего зависит результат вычислений.

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

Щелкнув на иконку, вы увидите, где именно находятся влияющие ячейки или диапазоны.

Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от нее.

Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу.

Как рассчитать количество вхождений текста A в текст B. Например, в тексте «Автоматическая система парковки у новых автомобилей» – два вхождения текста «Авто».

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

ДЛСТР (LEN) вычисляет длину текста, единственный аргумент – текст. Пример: ДЛСТР ("машина") = 6.

ПОДСТАВИТЬ (SUBSTITUTE) заменяет в текстовой строке определенный текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст).

Пример: ПОДСТАВИТЬ ("автомобиль";"авто";«»)= «мобиль».

ПРОПИСН (UPPER) заменяет все символы в строке на прописные. Единственный аргумент – текст. Пример: ПРОПИСН ("машина") = «МАШИНА». Эта функция понадобится нам, чтобы делать поиск без учета регистра. Ведь ПРОПИСН("машина")=ПРОПИСН("Машина").

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

ДЛСТР("Автоматическая система парковки у новых автомобилей") ДЛСТР("матическая система парковки у новых мобилей") = 8

А затем разделить эту разницу на длину той строки, которую мы искали:

8 / ДЛСТР ("авто") = 2

Именно два раза строка «авто» входит в исходную.

Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» – тот, число вхождений которого нас интересует):

=(ДЛСТР(текст) ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст);ПРОПИСН(искомый); ")))/ДЛСТР(искомый)

Как найти дубликаты в списке?

Выделяем список, выбираем на ленте в разделе «Главная»: Условное форматирование → Правила выделения ячеек → Повторяющиеся значения.

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

Если же дубликаты нужно удалить, а не выделить, то выделите таблицу (в данном примере дубликаты предварительно выделены) и нажмите на кнопку «Удалить дубликаты» в разделе «Данные». В появившемся окне укажите, в каком именно столбце ищем дубликаты:

Обновляемые дата и время в Excel

Вводим формулы

=СЕГОДНЯ() или =ТДАТА().

Первая – текущая дата, вторая – дата и время. ТДАТА можно отформатировать как время, и будет отображаться только оно:

Значения обновляются при любом действии (вводе данных в любую ячейку).

Для Google Таблиц:

=NOW() текущие дата и время (отображение будет зависеть от форматирования):

Как определить номер недели по дате?

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

=НОМНЕДЕЛИ(A1;2)

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

Если у вас Excel 2003, воспользуйтесь следующей формулой:

=1+ЦЕЛОЕ((A1-ДАТА(ГОД(A1+4-ДЕНЬНЕД(A1+6));1;5)+ДЕНЬНЕД(ДАТА(ГОД(A1+4-ДЕНЬНЕД(A1+6));1;3)))/7)

В Google Таблицах тоже есть функция WEEKNUM. Просто укажите в качестве аргумента этой функции ячейку с датой, и получите номер недели:

= WEEKNUM(A1)

Как выделить даты, которые уже прошли?

Выделяем ячейки с датами, выбираем «Условное форматирование» → «Правила выделения ячеек» → «Дата»:

Выбираем подходящий вариант, нажимаем ОК и получаем результат:

Как быстро добавить новые данные в диаграмму?

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

Как сделать это максимально быстро?

Очень просто: выделите те данные, которые нужно добавить (в данном случае диапазон A12:B13), скопируйте их (Ctrl + C), выделите диаграмму мышкой и вставьте (Ctrl + V) данные. Несколько секунд – и готово!

Используйте «Умные таблицы» Excel

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

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

Что изменится?

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

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

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

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

Сценарный анализ в excel – 1. Таблицы данных

В старых версиях Excel этот инструмент назывался не «Таблицы данных», а «Таблицы подстановки». Суть же не изменилась.

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

Рассмотрим этот инструмент на примере. Допустим, у нас есть простая модель расчета прибыли от продаж (серым обозначены входящие параметры, а в белых ячейках – расчетные показатели):

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

В этом примере в строке стоят ссылки на ячейки с выручкой, себестоимостью и прибылью от продаж. В столбце – разные варианты по объему производства. После того как данные готовы, необходимо выделить всю таблицу (в данном случае с ячейки «Количество товаров» и до правого нижнего угла), на ленте инструментов выбрать:

Данные → Анализ «Что если» → Таблица данных

и в появившемся диалоговом окне в пункте «Подставлять данные по строкам» (то есть наши варианты по количеству производимых товаров) поставить ссылку на ячейку с количеством товаров в нашей модели – в примере это ячейка B3.

После этого в таблице будут отображены разные сценарии изменения выручки, себестоимости и прибыли от продаж при шести вариантах объема производства:

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

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

Выделяем таблицу и снова вызываем инструмент «Таблица данных». Но теперь в диалоговом окне мы ставим ссылки на две ячейки исходной модели – с удельной себестоимостью и объемом производства:

И получаем результат:

Сценарный анализ – 2. Подбор параметра

Еще один полезный встроенный инструмент Excel для проведения анализа «Что если» – «Подбор параметра». Его можно найти там же, где и таблицы данных:

Данные → Анализ «Что если» → Подбор параметра.

Подбор параметра позволяет получить ответ на вопрос:

Каким должен быть входящий параметр, чтобы получить заданный результат?

Или, если рассматривать пример – модель из предыдущего раздела:

Какой должна быть себестоимость единицы товара (при прочих равных), чтобы получить прибыль, равную 58 000 рублей?

Вызовем инструмент «Подбор параметра», чтобы получить ответ:

В первом пункте мы указываем ссылку на ячейку с целевым показателем – в данном случае она была активна и подставилась автоматически (B9). Во втором пункте диалогового окна нужно указать желаемое целевое значение – мы хотим прибыль от продаж на уровне 58 тысяч. Изменять мы будем параметр в ячейке B5 – себестоимость единицы.

Нажимаем ОК и получаем результат. Можно сохранить его в таблице или вернуть исходные значения.

Как построить простой прогноз в Excel

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

Самый простой способ – добавить линию тренда на график с показателем.

Допустим, у вас есть график с динамикой продаж. Щелкните правой кнопкой мыши на ряд данных и нажмите «Добавить линию тренда …»:

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

Результат:

Для прогнозирования не на графике, а в диапазоне ячеек можно воспользоваться функцией ПРЕДСКАЗ (FORECAST). У нее следующий синтаксис:

=ПРЕДСКАЗ(x; известные_значения_y; известные_значения_x).

• Известные_значения_y – старые значения показателя, значения которого вы будете прогнозировать;

• известные_значения_x – значения фактора, влияющего на зависимый показатель y (в качестве x могут выступать и периоды – как в нашем примере);

• x – новое значение фактора.

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

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

Разделите продажи за каждый месяц (или другой период) на средние продажи за год или на показатели тренда (значения линейного тренда можно рассчитать с помощью функции ТЕНДЕНЦИЯ (TREND) ее единственным аргументом будут известные значения прогнозируемого показателя).

Это пример с делением на средние продажи:

А это – с делением на тренд:

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

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

Например, для декабря показатель может составить 1,29, то есть в этот месяц высокого сезона показатель (допустим, выручка) на 129 % выше «обычного». Для января, наоборот, коэффициент может быть равен 0,86, к примеру.

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

Если вы хотите построить многопараметрическую регрессию для прогнозирования показателя, зависящего от нескольких факторов, вам понадобится функция ЛИНЕЙН (LINEST). Ее описание, к сожалению, выходит за рамки этой книги – см. в конце раздела. Кроме того, вы можете обратиться к сайту , другим порталам или справке Excel.

Горячие клавиши Excel

Чем меньше вы в Excel пользуетесь с мышью, тем быстрее вы работаете.

В Excel очень много горячих клавиш, позволяющих производить привычные рутинные операции мгновенно.

Почти все пользователи знают про стандартные сочетания вроде Ctrl + C и Ctrl + V, но ведь этим возможности программы не ограничиваются.

Очень удобны сочетания клавиш для быстрого выделения и перемещения по таблицам.

• Выделить всю таблицу – Ctrl + A.

• Переместиться в конец таблицы – Ctrl + стрелки.

• Выделить таблицу до конца – Ctrl + Shift + стрелки.

• Конец рабочего листа – Ctrl + End.

• Начало рабочего листа – Ctrl + Home.

Еще несколько полезных сочетаний:

• Установить или убрать фильтр – Ctrl + Shift + L.

• Создать новый лист – Shift + F11.

• Сохранить файл – Shift + F12.

• Переход на предыдущий/следующий лист – Ctrl + PgDown/PgUp.

• Процентный формат ячеек – Ctrl + Shift + 5.

• Денежный формат ячеек – Ctrl + Shift + 4.

• Формат «дата» – Ctrl + Shift + 3.

• Полужирный шрифт – Ctrl + B.

• Курсив – Ctrl + I.

• Подчеркнутый – Ctrl + U.

• Вставить текущую дату – Ctrl + Ж.

• Вставить текущее время – Ctrl + Shift + Ж.

• Повторение последнего действия – F4 (работает далеко не для всех действий. Очень удобно для быстрого многократного повторения вставки строки или столбца).

• Вернуться в активную ячейку – Ctrl + Backspace.

• Режим выделения (в котором можно выбирать несвязные диапазоны, не удерживая Ctrl) Shift + F8.

• Выделить всю строку Ctrl + пробел.

• Выделить весь столбец Shift + пробел.

• Создание диаграммы из выделенных данных Alt + F1.

• Создание сводной таблицы Alt + D + P.

• Удерживайте Alt при добавлении любой фигуры – и ее границы будут растягиваться строго в соответствии с границами ячеек.

Источники данных:

В книге MrExcel XL – 40 Greatest Excel Tips of All Time есть «периодическая таблица горячих клавиш Excel». Она пригодится вам, если вы используете английскую версию Excel.

Полезную памятку с горячими клавишами русской версии можно скачать на сайте Николая Павлова – .

Надстройка Plex

Автор сайта  – пожалуй, лучшего ресурса по Excel в российском интернете – создал надстройку PLEX, которая содержит 50 инструментов, отсутствующих в самом Excel. Например, таких:

• функция FindSame, находящая наиболее похожий с искомым текст;

• сравнение двух списков;

• автоматический ввод суммы прописью;

• номер квартала по месяцу и многие другие.

Надстройка на момент написания книги стоит всего 795 рублей, а протестировать (500 запусков) ее можно бесплатно. Могу сказать, что одна функция FindSame окупила стоимость надстройки, сэкономила мне многие часы работы и очень облегчила работу с отчетностью. Она решает важную задачу: позволяет сопоставлять списки элементов, названия в которых совпадают не полностью (пример из моей практики: в одном списке название книги может быть с подзаголовком, в другом – без. Или в одном списке – с точкой, в другом – с двоеточием. Этого достаточно, чтобы для Excel такие названия были разными. Функция FindSame решает эту типичную проблему).

Купить надстройку (или попробовать тестовую версию) можно здесь: .