Форматирование отдельных знаков

Формат

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

Если вам нужно, например, выделить фрагмент текста полужирным курсивом или же воспользоваться верхним и нижним индексом, нет ничего проще.
Нужно всего лишь выделить необходимый символ или слово в строке формул, а не саму ячейку. Пожалуй, самый удобный и простой способ перейти в строку формул - клавиша F2. Хотя можете воспользоваться любым другим, на свое усмотрение.

Дальнейшие действия уже ничем не отличаются от применения обычного формата ячейки. Правда, доступна вам будет только вкладка "Шрифт", что логично. То есть, выделив фрагмент текста, достаточно открыть диалоговое окно "Формат ячеек" на ленте (Ctrl+1 или Ctrl+Shift+F) и сделать все, что требуется.

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

Unknown 0 Comments

Автообновление сводной таблицы

Хитрости

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

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

Инструкция:

1. Создаем на новом листе сводную таблицы на основе своих данных.
2. Правой клавишей щелкаем на пиктограмме листа со сводной таблицей и выбираем пункт "Просмотреть код" из контекстного меню.
   
3. Сверху в открывшемся окне Microsoft VBA выбираем Worksheet (Лист) и Activate (Активировать). Таким образом, мы устанавливаем событие, которое запустит наш код. В данном случае это активация листа сводной таблицы. Можете поэкспериментировать и с другими вариантами.
   
4. Копируем туда указанную строку кода:
   Private Sub Worksheet_Activate()
   ThisWorkbook.RefreshAll
   End Sub
   
5. Сохраняем свой файл (лучше в формате .xlsm с поддержкой макросов) и готово.

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

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

Скачать пример

Unknown 0 Comments

10 горячих клавиш (ч.1)

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

10 полезных сочетаний клавиш в MS Excel (часть 1)

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

В MS Excel их точно не меньше двух сотен, но с первой подборкой топ-10 полезных горячих клавиш сегодня я вас познакомлю. Итак, приступим!

1. ФОРМАТ ЯЧЕЕК: Ctrl+1
   Это сочетание клавиш позволяет быстро открыть окно "Формат ячеек" для выделенной ячейки или диапазона.
2. ФИКСАЦИЯ АДРЕСА ЯЧЕЙКИ: F4 (при редактировании формул)
   Если вам приходится часто работать с формулами, которые содержат как абсолютные, так и относительные адреса ячеек, то без F4 не обойтись. При редактировании формул (курсор должен быть активен в строке формул) нажатие на эту кнопку позволяет "зафиксировать" адрес или часть адреса ячейки. То есть, адрес A1 превратится в $A$1, A$1 или $A1.
3. ОТОБРАЖЕНИЕ ФОРМУЛ: Ctrl+' (или Ctrl+ё)
   Отобразит все формулы на листе. Довольно удобно использовать для быстрой проверки правильности формул в небольших таблицах.
4. ГРУППИРОВКА СТРОК/СТОЛБЦОВ: Ctrl+Alt+стрелки вправо-влево
   Полезно, если вы готовитесь к расчету "Промежуточных итогов" или пользуетесь группировкой строк/столбцов для их дальнейшего скрытия.
5. ТЕКУЩАЯ ДАТА: Ctrl+; (или Ctrl+ж)
   Быстрая вставка сегодняшней даты всегда удобней ручного ввода.
6. НОВАЯ СТРОКА В ЯЧЕЙКЕ: Alt+Enter
   Хотите начать ввод текста с новой строки? Это можно сделать и в той же ячейке без никаких проблем.
7. ОТМЕНА И ЗАКРЫТИЕ МЕНЮ: Esc
   Закрытие всяческих окон, меню и отмена редактирования формулы на одной кнопке.
8. НАВИГАЦИЯ МЕЖДУ ЛИСТАМИ: Ctrl+PageUp/PageDown
   Один из простых способов передвижения между листами книги. Максимально удобен, если их не больше 4-5.
9. СПЕЦИАЛЬНАЯ ВСТАВКА: Ctrl+Alt+V
   Пригодится, если вставлять фрагмент из буфера нужно не "как есть", а например, только его формат или же его необходимо предварительно транспонировать.
10. ПОСЛЕДНЯЯ ЯЧЕЙКА: Ctrl+End
    Это сочетание поможет быстро найти крайнюю правую нижнюю ячейку на рабочем листе и переместиться к ней.

Unknown 0 Comments

Объединенные ячейки

Формат

Объединенные ячейки: добро или зло?

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

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

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

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

Основные проблемы:

• Объединенные ячейки нельзя использовать в паре с "умными таблицами" (Вставка-Таблицы-Таблица). 
• Частично нарушается согласованность между строками и столбцами электронной таблицы, что может привести к нарушению правильности работы некоторых формул.
• При работе с макросами VBA возможны аналогичные сбои (особенно, что касается выбора и выделения диапазонов)
• О нормальной сортировке и фильтрации данных, содержащих объединенные ячейки, тоже можете забыть сразу.

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

Альтернативные способы:

1. Формат ячеек: Здесь нам поможет довольно малоизвестный параметр, но крайне полезный. Итак, выделяем диапазон, который мы бы хотели "объединить" и жмем "Формат ячеек"-"Выравнивание"-"По горизонтали"-"По центру выделения".
   
2. Вставка-Надпись: этот способ требует немного больше времени. При добавлении Надписи не забудьте выбрать параметр "Привязать к сетке" для удобства настройки ее размеров.
   

Наглядный пример использования этих способов Вы можете СКАЧАТЬ ЗДЕСЬ.

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

Unknown 0 Comments

Преобразование ссылок GoogleDrive

Хитрости

Пример использования текстовых формул для трансформации ссылок

Сегодня покажу вам простой пример работы текстовых формул в Excel для преображения веб-ссылок GoogleDrive.

Раз уж свой блог я решил завести на платформе blogger.com, то и использование штатного GoogleDrive для хостинга xls-файлов мне показалось логичным.
Правда, почти сразу же я столкнулся с первым неудобством. Суть его заключалась в том, что предлагаемый формат ссылок на файлы по умолчанию открывают файл, а не предлагают выбор открыть или скачать.
Возможно, кому-то так и удобней, но для части файлов, которые содержат макросы такой способ отображения может вызвать некорректную работу.

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

Например:
1) Ссылка по умолчанию (Google-Таблицы):
https://drive.google.com/file/d/0B3eOGZuGXnyDTnVLWkRFeDY4OFU/view?usp=sharing
2) Прямая ссылка:
https://drive.google.com/uc?export=download&id=0B3eOGZuGXnyDTnVLWkRFeDY4OFU

Как вы видите, обе содержат одинаковый 28-значный идентификатор (уникальный код файла), а отличаются они самой формой ссылок (или т.н. маской адреса).

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

Инструкция:

1. Первое, что нам понадобится, это сама ссылка на файл из GoogleDrive
   https://drive.google.com/file/d/0B3eOGZuGXnyDTnVLWkRFeDY4OFU/view?usp=sharing
2. Копируем ее в любую свободную ячейку на листе (в примере C1).
3. Теперь нам нужно из нее как-то вытащить сам код файла. Самый простой способ определить, где он начинается, это ввести в соседнюю ячейку формулу:
   =НАЙТИ("/d/";C1)
4. С ее помощью Excel найдет в нашей ссылке номер позиции искомого текста /d/, а именно число 30. Проще говоря, теперь мы знаем, что наш код файла начинается сразу после /d/, а значит с 33 (30+3) знака в ячейке.
5. Ранее я уже упомянул, что код файла в GoogleDrive 28-значный. Убедиться в этом можете сами. Для этого вручную скопируйте сам код файла в любую свободную ячейку. Длину текстовой строки можно легко определить с помощью формулы:
   =ДЛСТР(ссылка на ячейку)
6. Итак, теперь мы знаем, где начинается наш искомый текст и его длину. А значит, можно приступить к его вытаскиванию из нашей ссылки. Здесь нам на помощь приходит еще одна текстовая формула:
   =ПСТР(C1;D1+3;28)
7. Формула ПСТР имеет 3 аргумента: текст, начальная_позиция и количество_знаков.
   В нашем случае, текст - адрес ячейки со ссылкой на открытие файла (С1),
   начальная_позиция - ссылка на ячейку из шага 4, увеличенная на 3 знака (D1+3),
   количество символов - 28 (или можно указать и ссылку из шага 5).
   В результате данной формулы мы получим полный код файла без ничего лишнего.
8. Остается только как-то объединить нужную нам маску прямой ссылки и полученный 28-значный код. Здесь можно, например, воспользоваться формулой:
   =СЦЕПИТЬ("https://drive.google.com/uc?export=download&id=";G1)
   Или просто обойтись знаком конкатенации &:
   ="https://drive.google.com/uc?export=download&id="&G1
9. Вуаля! Теперь у нас есть универсальный преобразователь ссылок GoogleDrive для вставки файлов в блог. Надеюсь, кому-то он тоже пригодится
   Естественно, все вышеперечисленные формулы не обязательно писать в разных ячейках. Да и для удобства наши ссылки и преобразования лучше засунуть в таблицу. Более-менее облагороженный вариант можете посмотреть по ссылке ниже.

Пример:

Скачать

Unknown 0 Comments

Пропали ярлыки листов?

Грабли

Описание проблемы

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

Где же переключатель листов?

Распространенные причины:

• Самая частая: кто-то из Ваших коллег все еще использует 1С 7.7. При экспорте данных из 1С 7.7 в Эксель по умолчанию книги сохраняются в старом формате .xls (Excel 5.0/95). В таких файлах ярлыки листов по умолчанию скрыты всегда.
• Ярлыки листов могут не отображаться после добавления в книгу листа макросов.
• Человек, который скинул Вам этот файл, хотел скрыть что-то важное. Что же, в этот раз ему не повезло ;)

Решение

1. Сначала проверьте, действительно ли этот параметр отключен. Для этого нажмите кнопку Microsoft Office (крайний левый верхний угол окна) и выберите пункт Параметры Excel. 
2. В категории Дополнительно в пункте Показать параметры для следующей книги просмотрите состояние флажка Показывать ярлычки листов. Если флажок снят, установите его и нажмите кнопку ОК.



Вот тут обычно и кроется суть проблемы



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



Вот этот ползунок и надо подвинуть мышкой...



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



... примерно сюда. Готово!

Сталкиваетесь с этой проблемой слишком часто?

Не беда! Если Вы ежедневно работаете с файлами, в которых по умолчанию встречается выше описанная проблема, можно избавиться от нее с помощью VBA. А именно, добавлением пары строк программного кода в Личную книгу макросов:

Sub SheetTabsVisible()
ActiveWindow.DisplayWorkbookTabs = Not ActiveWindow.DisplayWorkbookTabs
ActiveWindow.TabRatio = 0.264
End Sub

Скачать готовую надстройку

Unknown 0 Comments

Первый пост

Блог

Добро пожаловать!

Итак, сегодня я начинаю вести свой первый блог. Как вы все могли догадаться по названию и логотипу, посвящен он будет, в первую очередь, использованию программы Microsoft Excel. Так уж получилось, что о ней знают практически все и многие используют ее возможности на работе для решения своих задач. Но мало кто может себе представить, на что реально способен MS Excel в умелых руках.

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

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

То же касается и работы самого блога. Опыт работы с HTML и CSS у меня довольно скромный, но со временем постараюсь свести количество технических проблем с отображением веб-страниц и их частей к минимуму.

Excelsior!

Unknown 0 Comments