Читать далее ‘Checkbox в Google Таблицах и фишки их применения’ →]]> В Google таблицах, в отличии от Excel, чекбокс («флажок» в русскоязычной версии) добавляется без использования меню Разработчик и встраивается непосредственно в ячейку, а не как дополнительный элемент.

Флажок можно установить 2-я способам: Вставка → Флажок или Данные → Настроить проверку данных → в Критерии выбрать «Флажок».

По сути чекбокс это своего рода выключатель. Когда галочка проставлена, то он включен, и в строке формул прописывается TRUE, когда галочки нет, он выключен, и прописывается FALSE.

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

1. Список задач с условным форматированием

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

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

Формула в ячейке D1 имеет вид:

В ячейку C3 пропишем формулу изменения статуса:

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

Осталось только добавить условное форматирование. Для этого выберем Формат → Условное форматирование и далее зададим наши условия: диапазон действия правил условного форматирования, само правило (в нашем случае =$B3=TRUE, т.е. проставленная галочка в чекбоксе) и формат ячеек.

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

Кстати, вместо графы «Статус» вы можете просто переопределить значение чекбокса.  Для этого в Правилах проверки данных нужно поставить галочку Использовать особые значения ячейки. Например, для значений TRUE пропишем «Выполнено, для FALSE «В процессе». И тогда в фильтре столбца «Флаг» мы сможем выбрать необходимый нам статус. Но в таком случае во всех формулах нужно вместо TRUE и FALSE использовать новые переназначенные имена.

2. Перенос данных на другой лист

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

Так, на листе «список» в диапазоне А:Е у нас перечень товаров. На другом листе, куда нам нужно вывести только определенные позиции, пропишем формулу:

= QUERY('список'!A:E; "select B,C,D,E where A = TRUE")

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

3. Шкала прогресса

А данный пример подробно описан в одной из наших предыдущих статей.

Посмотреть выше описанные примеры примере вы можете по ссылке

https://docs.google.com/spreadsheets/d/1C7mZGJkuFyJMxwTrXhvWDbUw_C_7-GjwwxOvneD9fLA/edit?usp=sharing

Читать далее ‘Как устроены Google Таблицы и в чем их преимущество’ →]]> Первая таблица Excel была представлена публике в 1987 году компанией Майкрософт и произвела революцию в сфере анализа данных. В свою очередь Google Таблицы создавались как аналог Excel от Google и впервые появились в 2006 году. Историю появления и популяризацию и тех, и других вы сможете без труда прочесть в открытых источниках. Здесь же мы разберем в чем основные отличия Google Таблиц от Excel и почему они становятся все более и более популярными.

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

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

При этом сотрудники должны вносить правки по очереди, иначе у них получатся две версии файла, которые придётся объединять. Но кто-то по ошибке может внести правки в уже не актуальную версию файла, и у неё начнётся своя цепочка размножения. И это только два человека, а если взять пример даже маленькой компании из десяти человек, то получим десятки коммуникаций и сотни версий одного и того же документа. Умножьте это на количество документов компании учитывая, что сами документы часто ещё и связаны между собой. В итоге получаем типичную ситуацию, с которой сталкивались многие. И у этой ситуации даже есть название – Excel hell, что дословно переводится как «ад экселя» или «проклятие эксель».

“Excel hell – это состояние, когда много важной информации содержится в Excel-файлах сотрудников и происходит постоянный обмен этими файлами по почте. В итоге сложно сказать у кого самая последняя и правильная версия. Для получения общей картины нужно сводить данные из этих файлов в один, что на практике часто сводится к copy-paste, занимает много времени и приводит к ошибкам. В итоге сотрудники не только тратят время на обработку информации, но еще и рискуют принять неправильные решения на основе устаревших или вовсе неверных данных.”

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

Тогда появился тренд на хранение файлов на общем сервере, а сотрудники подключаются к нему и работают в едином информационном пространстве. Параллельно стал развиваться второй тренд – Web 2.0 В качестве примеров сайтов стиля Web 2.0 можно назвать Youtube, Twitter, Facebook, Myspace. Это примеры крупных социальных сетей, которые объединяют миллионы пользователей по всему миру.

На стыке этих двух трендов и появились Google Таблицы. С одной стороны, они похожи на Excel, но поддерживают совместную работу и лишены недостатков с пересылкой файлов. Google много перенял у своего предшественника: те же меню, панели инструментов, ячейки и листы, но есть и отличия.

Самое очевидно – это то что программы для Google Таблиц нет. Они открыты в обычном браузере. Нет и файлов на компьютере, всё что нам нужно – это доступ в Интернет. Более того, даже если подключение к Интернету отсутствует, работать можно офлайн, установив перед этим опцию офлайн-доступа, кликнув Файл → Включить офлайн-доступ.

Создавать Google Таблицу можно несколькими способами:

• первый – создавать папки сразу на Google-диске или через браузер, кликнув кнопку Сервисы →Таблицы;
• второе – загрузить папки и файлы с компьютера (Файл → Импортировать → Загрузка → Browse). Также можно обратно превратить Google Таблицу в Excel (Файл → Скачать → Microsoft Excel (XLSX)).

Кнопка «Настройка доступа» позволяет пригласить и настроить доступ для других пользователей. Здесь есть права на редактирование, на просмотр или на комментирование. Причём если человек имеет Google-аккаунт, то можно пригласить его лично, а если нет или мы не знаем его учётную запись, то можем разрешить доступ всем, у кого есть ссылка.

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

Анонимные пользователи в Google Docs обозначаются названиями животных или растений: «неопознанный жираф», «неопознанная тыква» и так далее. Это значит, ваш файл доступен всем в Интернете или всем, у кого есть ссылка.

Можно оставлять в ячейках комментарии друг другу, отвечать на них, открывать и закрывать обсуждения. Комментарии можно писать для всех или адресно конкретному пользователю. С помощью символа @ можно упомянуть конкретных пользователей, и тогда только они получат уведомление. Чтобы добавить комментарий воспользуйтесь командой Ctrl + Alt + M или через Вставка → Комментарий.

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

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

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

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

Аналогично приложению для смартфонов, расширяющих возможности вашего устройства, здесь есть Маркет приложений, который позволяет расширить стандартный функционал Google Таблиц путем создания скриптов для автоматизации работы. Скрипты здесь пишутся на языке Java Script.

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

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

О тонкостях и трюках работы с Google Таблицами расскажем в наших следующих статьях.

Читать далее ‘Как создать индикатор прогресса с цветовой шкалой в Google Таблицах’ →]]> Работая в группе над сложным и многоуровневым проектом, часто возникает необходимость контролирования выполнения этапов работ. Тем более, если члены команды работают удаленно и для координации работы над проектом используют облачные сервисы. Подобный инструмент можно быстро и легко создать в Google Таблице. Он позволит визуализировать прогресс выполнения проекта, упростить коммуникацию между коллегами и использовать файл одновременно несколькими пользователями.

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

Далее проставим «флажок» в ячейке каждого этапа. Для этого выделим диапазон B2:D6, Вставка → Флажок.

У нас получилась следующая картина.

Теперь пропишем формулу в столбце Прогресс в ячейке Е2. Но перед этим убедитесь, что в настройках таблицы названия функций прописываются на английском языке. Для этого выберите Файл → Настройки → Всегда использовать названия функций на английском языке.

Можно остаться и на русском языке. Но тогда часть формулы придется писать на русском, а часть на английском. Так, например, для функции SPARKLINE нет аналога в русскоязычной версии, поэтому она так и прописывается латинскими буквами, а функция COUNTIF будет прописана как СЧЁТЕСЛИ.  В русской версии формула имеет вид:

=SPARKLINE(СЧЁТЕСЛИ(B2:D2;ИСТИНА);{"charttype"\"bar";"max"\3;"color1"\"green"})

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

Мы продолжим написание формулы на английском языке. Наша формула выглядит следующим образом:

=SPARKLINE(COUNTIF(B2:D2;TRUE);{"charttype"\"bar";"max"\3;"color1"\"green"})

Функция SPARKLINE создает миниатюрную диаграмму внутри ячейки.

Синтаксис функции: SPARKLINE (данные; {параметры})

Функция COUNTIF Подсчитывает количество ячеек, соответствующих заданному условию и расположенных в указанном диапазоне.

Синтаксис функции: COUNTIF (диапазон; условие)

Цифра 3 означает количество ячеек, которое будет проверяться на условие функцией COUNTIF, иными словами, количество этапов выполнения проекта. Далее задается цвет — «color1» и прописывается название цвета «green».

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

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

Выберем 3 оттенка зеленого цвета и рядом укажем их название, которое позже пропишем в формуле.

Измененная формула имеет вид:

=SPARKLINE(COUNTIF(B2:D2;TRUE);{"charttype"\"bar";"max"\3;"color1"\SWITCH(COUNTIF(B2:D2;TRUE);3;"#0a860e";2;"#84c745";1;"#c6ecc3";"#c6ecc3")})

Цифры 1,2 и 3 означают номер этапа и присвоенный ему цвет. От светло-зеленого для 1 этапа до темно-зеленого для 3 этапа.

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

Синтаксис функции:  SWITCH (выражение; результат1; значение1; [результат2; значение2; ...]; [по_умолчанию])

Таким образом, при проставлении галочки в ячейке B2:D6 цвет будет менять от светлого до темно-зеленого. Шкала прогресса готова!

Пример файла можно посмотреть по ссылке: https://docs.google.com/spreadsheets/d/1wfyyIHab3BkXhD7Wv_6A12b58Swtf042G2YP3_BBywo/edit?usp=sharing

Читать далее ‘Функции СЛЧИС и СЛУЧМЕЖДУ Excel на примере создания имитации игральной кости’ →]]> Может так случиться, что под рукой не окажется игрального кубика, когда тот понадобится, однако если на компьютере имеется программа для работы с электронными таблицами, то довольно легко изготовить вполне рабочую замену этого важного для многих настольных игр инструмента. Так как Excel часто допускает множественность путей решения одной и той же задачи, в данной статье будут рассмотрены разные вариации процесса подготовки виртуальной игральной кости – выберите те, которые вам больше будут по душе.

Начать следует с создания новой книги, на листе которой необходимо выделить диапазон ячеек “A1:C10” и объединить их нажатием кнопки «Объединить и поместить в центре»:

Получившаяся большая ячейка (её адрес – “A1”, именование в таких случаях осуществляется по ячейке в верхнем левом углу объединяемого диапазона) понадобится чуть позже, ну а пока в другой ячейке – “B12” – нужно разместить формулу, выдающую случайное целое число от 1 до 6, и здесь можно предложить два варианта.

Вариант 1-1
Формула может иметь такой вид: ”=ЦЕЛОЕ(СЛЧИС()*6)+1”

Давайте разберём, как она работает. Использованная в ней функция СЛЧИС() не имеет аргументов и генерирует случайное число, принимающее значение в интервале от 0 до 1 (если выражаться более строго – функция возвращает псевдослучайное число с равномерным распределением). При умножении его на 6 тоже получится случайное число, большее 0, но меньшее 6. Функция ЦЕЛОЕ( ) отбрасывает у такого числа дробную его часть и получается случайная величина, принимающая целые значения от 0 до 5 – именно поэтому в выражении формулы к результату необходимо дополнительно прибавлять ещё единицу.

Вариант 1-2
Для достижения цели хорошо подходит и другая формула: ”=СЛУЧМЕЖДУ(1;6)”

Использованная в ней функция СЛУЧМЕЖДУ возвращает случайное целое число, находящееся в диапазоне между двумя заданными ей в качестве аргументов числами.

Рассмотренные функции СЛЧИС() и СЛУЧМЕЖДУ() обладают интересной особенностью: при любом изменении данных в листе (даже при простом нажатии клавиши [Delete], когда выделена заведомо пустая ячейка) выдаётся новое случайное значение – это можно использовать для «бросания» кубика.

Среди символов Юникода помимо букв самых разнообразных национальных алфавитов есть довольно много специальных знаков, в том числе – изображения граней игральной кости (найти их можно, воспользовавшись ресурсом unicode-table.com): ⚀ ⚁ ⚂ ⚃ ⚄ ⚅

Excel, как и многие другие современные программы, умеет работать с Юникодом, поэтому сделаем визуализацию результата «броска» кубика в виде показа одного из таких символов в той большой «ячейке» “A1”, в которую мы ранее объединили диапазон “A1:C10”. Здесь также можно предложить два варианта.

Вариант 2-1
Заполните ячейки в диапазоне “A14:A19” числами 1 до 6, а в ячейках диапазона “B14:B19” поместите соответствующие знаки-грани:

Теперь в ячейку “C14” поместите следующую формулу: ”=ЕСЛИ(A14=B$12;B14;””)”

Использованная в ней функция ЕСЛИ ( ; ; ) проверяет, совпадает ли случайное число в “B12” с числом в “A14” и если да, то функция возвращает содержимое ячейки “B14” (то есть изображение грани кубика), а если нет – то возвращается пустая строка (обозначается сдвоенными кавычками: «»). Обратите внимание на то, что в формуле в адресе ячейки “B12” к номеру строки применена так называемая абсолютная адресация – перед числом «12» стоит символ доллара $. Это позволяет, воспользовавшись маркером заполнения (маленький чёрный квадратик в правом нижнем углу рамки выделенной ячейки), размножить формулу вниз до ячейки “С19” и получить в каждой из шести ячеек диапазона “C14:C19” нужным образом работающую команду. В итоге получается следующее. В одной из шести ячеек указанного диапазона будет отображаться символ грани кубика, соответствующий числу в ячейке “B12”, а остальные пять ячеек при этом будут пустыми:

Теперь остаётся сделать так, чтобы нужный символ, изображающий грань кубика, показывался в ячейке “A1”, поэтому в неё надо ввести формулу, для выражения которой возможна парочка вариаций.

Подвариант 2-1a
Можно воспользоваться символом «амперсанд» &, работа которого в составе формулы сводится к соединению (конкатенации) строк: ”=C14&C15&C16&C17&C18&C19”

Подвариант 2-1b
В Excel имеется функция СЦЕПИТЬ( ; ; …), которая тоже позволяет объединять текстовые строки. Для нашего случая формула с ней выглядит так: ”=СЦЕПИТЬ(C14;C15;C16;C17;C18;C19)”

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

Вариант 2-2
Часто формулы Excel оказываются довольно длинными, что вызывает у новичков затруднения в их восприятии. Описанный выше Вариант 2-1 лучше подходит именно для начинающих осваивать электронные таблицы, поскольку решение задачи «размазано» по разным ячейкам, благодаря чему проще пошагово отследить, как формулы работают. Ну а тем, кто уже набрался опыта и не боится сложных и длинных команд, использующих много функций сразу, можно предложить более компактную версию в виде следующего выражения для “A1”:

=ЕСЛИ(B12=1;”⚀”;ЕСЛИ(B12=2;”⚁”;ЕСЛИ(B12=3;”⚂”;ЕСЛИ(B12=4;”⚃”;ЕСЛИ(B12=5;”⚄”;”⚅”)))))

Как нетрудно видеть, в формулах могут присутствовать не только знаки, вводимые напрямую с клавиатуры, но и символы Юникода, сама же формула представляет собой пять вложенных друг в друга функций ЕСЛИ( ; ; ).

Чтобы сделать акцент именно на графическом отображении «выпавшей» грани кубика, следует увеличить размер шрифта в “A1”, установив для него значение 120 пт:

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

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

Так как в формулах используются относительные ссылки, то они при копировании сами поменяются, образовав новый «кубик», работающий независимо от первого (абсолютная адресация строковой части адреса ячейки “B12”, использованная в Варианте 2-1, ни на что не повлияет, если вставку скопированного диапазона осуществлять строго справа, без смещения вниз).

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

Вариант 3-1
Для ячеек листа можно установить защиту от изменений. Выделите весь лист, щёлкнув левой кнопкой мыши по участку между обозначениями строк и столбцов (того же самого можно добиться при использовании комбинации клавиш [Ctrl]+[A]):

Далее нужно щёлкнуть правой кнопкой мыши по какой-нибудь ячейке, в появившемся контекстном меню выбрать пункт «Формат ячеек…», после чего откроется одноимённое диалоговое окно, в котором надо переключиться на вкладку «Защита», где снять флажок «Защищаемая ячейка» (по умолчанию все ячейки листа являются защищаемыми):

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

Последний шаг – собственно включение защиты листа. Здесь следует переключиться на вкладку ленты «Рецензирование» и в секции «Изменения» нажать кнопку «Защитить лист»:

Появится диалоговое окно «Защита листа» – в нём вполне достаточно просто нажать кнопку «ОК»:

Вариант 3-2
Из эстетических соображений ячейки с формулами, выполняющими основную работу, можно «спрятать», заодно затруднив случайное их повреждение. Выделите строки, которые содержат формулы, подлежащие сокрытию:

Если теперь щёлкнуть по выделенной области правой кнопкой мыши, то появится контекстное меню, в котором будет команда «Скрыть» – выберите её и строки листа будут убраны с глаз:

В завершение остаётся лишь сказать, что Вариант 3-1 и Вариант 3-2 можно использовать как по отдельности, так и сразу оба – сначала скрыть ячейки, а потом включить защиту листа.

Читать далее ‘Ищу авторов статей по Excel’ →]]> Ищу авторов статей на Excel тематику для сайта exceltip.ru с практическим опытом работы в программе.

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

Стоимость работы будем обговаривать индивидуально.

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

exceltip[гав-гав]mail.ru

Читать далее ‘Как отфильтровать список элементов из длинного-длинного списка’ →]]> Каким образом можно найти и выделить полезную информацию из большой таблицы?! В Excel есть как минимум два метода. Первый заключается в использовании формул с функциями поиска частичного соответствия. Второй способ дает аналогичный результат, но не содержит формул – его мы и рассмотрим в сегодняшнем номере EXCELTIP. Добро пожаловать в Расширенный Фильтр, да детка!

Команда закреплена на вкладке Данные рядом с обычным Фильтром.

Качаем этот файл с примером чтобы разобраться

Задача — выделим из длинного списка только те строки, в которых встречается слово Фреза или Сверло

Итак, есть длинный неупорядоченный список товаров. Нужно получить из него список товаров, в названии которых есть слова «фреза» или «сверло». Самое простое и бесхитростное решение — воспользоваться штатным Фильтром.

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

Процедуру можно упростить, для этого Фильтр содержит поле с поиском.

… по ключевым словам Excel сам находит именно то, что нужно. Если ввести в поле поиска “фреза“, Фильтр покажет только те элементы, в которых есть это слово. Также можно добавлять новые поисковые запросы к предыдущим, установив флажок «добавить выделенный фрагмент в фильтр“.

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

Для быстрой и безошибочной работы с длинной таблицей или списком отлично подходит команда Расширенный Фильтр.

Перед запуском команды задаётся диапазон условий. Это отдельный список имён, которые, собственно, требуется найти в таблице. В примере он установлен ячейками E2:E4

Диапазон условий должен включать названия столбцов!

Перейдите на вкладку Данные -> Сортировка и фильтр -> Дополнительно

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

Неплохо, но так Расширенный Фильтр отобрал только те строки, которые начинаются с «фреза» или «сверло».

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

Однако и этого всё ещё недостаточно, т.к. критериям фильтра соответствуют и фрезы и бор-фрезы (которые нам не нужны), но как гласит легенда: «There can be only one».

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

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

1. Имена начинаются с «фреза»
2. Имена заканчиваются на «фреза»

Чтобы учесть вышеупомянутые возможности, необходимо расширить диапазон критериев до E6, а именно:

E3- * фреза * ‘Содержит слово фреза (только отдельно и не в начале или конце)

E4 — *сверло* ‘Содержит слово сверло (в любом месте)

E5 — =»=фреза *» ‘Начинается со слова фреза

E6 — =»=*фреза» ‘Заканчивается словом фреза

Примечание: * в конце не обязательна. Она стоит там, чтобы было видно где кончается строка

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

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

* фреза

*сверло

Читать далее ‘Функция СЖПРОБЕЛЫ в Excel с примерами использования’ →]]> Многие пользователи табличного редактора ошибочно вводят дополнительные пробелы в текстовые строки, содержащиеся в ячейках. Например, текстовые строки «Macbook Air 128 Gb» и «Macbook  Air 128 Gb» будут распознаны как содержащие различные значения, поскольку между словами присутствует лишний пробел. Убрать его можно вручную, если записей немного. Но что делать, если таблица содержит огромное количество строк, которые нужно проверить? Упростить задачу поможет встроенная функция СЖПРОБЕЛЫ.

Функция СЖПРОБЕЛЫ в Excel предназначена для удаления всех пробелов из текстовой строки, принимаемой в качестве аргумента, и возвращает строку, которая содержит только одиночные пробелы.

Синтаксис функции

Функция СЖПРОБЕЛЫ имеет следующую синтаксическую запись:

=СЖПРОБЕЛЫ(текст)

Единственным аргументом функции является текст, который принимает ссылку на ячейку с текстовыми данными или текстовую строку, заключенную в кавычки (например, в результате выполнения =СЖПРОБЕЛЫ(«некоторый     текст») будет возвращена строка «некоторый текст»).

Примечание

В наборе знаков ASCII символу пробела соответствует значение 32. Функция СЖПРОБЕЛЫ используется для удаления данных символов (кроме одиночных) в текстовых строках, закодированных с использованием таблицы ASCII. В таблице Unicode содержится дополнительный знак пробела, называемый неразрывным пробелом. Ему присвоено числовое значение 160 в таблице Юникод. Функция СЖПРОБЕЛЫ не удаляет лишние неразрывные пробелы, которые наиболее распространены в текстах формата HTML и обозначаются как « ». Эту особенность следует учитывать при обработке данных, полученных с веб-страниц.

Примеры использования функции СЖПРОБЕЛЫ

Пример 1. В таблице имеется столбец из нескольких строк, в которых могут быть допущены ошибки – содержаться дополнительные пробелы. Создать рядом столбец с текстовыми строками на основе имеющихся, в которых отсутствуют лишние пробелы.

Исходная таблица имеет следующий вид:

Выделим ячейки B2:B7, в строку для ввода функций введем следующую запись:

=СЖПРОБЕЛЫ(A2:A7)

A2:A7 – диапазон ячеек, содержащих исходные строки с лишними пробелами. Для выполнения функции необходимо использовать комбинацию Ctrl+Shift+Enter, поскольку используем формулу массива.

В результате выполнения формулы получим следующее:

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

Исходная таблица данных имеет следующий вид:

Для подсчета числа продаж (количества записей в таблице) для первого менеджера используем следующую формулу:

=СУММ(—(ЛЕВСИМВ(СЖПРОБЕЛЫ(A2:A9);10)=»Менеджер 1″))

Функция СУММ используется для подсчета количества значений параметров, переданных в качестве аргумента. Оператор «—» (двойное отрицание) используется в Excel для явного преобразования типов данных. В скобках содержится логическое выражение, которое возвращает один из двух возможных вариантов значений: ИСТИНА или ЛОЖЬ.

С помощью двойного отрицания происходит преобразование данных логического типа к соответствующим числовым значениям: 1 и 0. Функция ЛЕВСИМВ отбирает первые 10 символов в каждой строке из столбца A:A (длина строки «Менеджер 1» составляет 10 символов). Для корректировки ошибок (удаления дополнительных пробелов) использована рассматриваемая функция СЖПРОБЕЛЫ. Результат выполнения функции ЛЕВСИМВ напрямую сравнивается с исходной текстовой строкой.

Для получения корректных результатов необходимо использовать комбинацию Ctrl+Shift+Enter (формула массива). Выполнив функцию, получим следующее значение:

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

=СУММ(—(ЛЕВСИМВ(СЖПРОБЕЛЫ(A2:A9);10)=»Менеджер 2″))

Полученные результаты:

Если упустить функцию СЖПРОБЕЛЫ, получим некорректные результаты:

Также можно легко подсчитать число строк, в которых содержатся лишние пробелы, используя следующую формулу (вводить с использованием комбинации клавиш Ctrl+Shift+Enter):

=СУММ(—(ДЛСТР(A2:A8)-ДЛСТР(СЖПРОБЕЛЫ(A2:A8))<>0))

Функция СУММ определяет сумму значений, содержащихся в массиве, переданном в качестве аргумента. Благодаря использованию двойного отрицания, логические данные ИСТИНА и ЛОЖЬ преобразуются в массив соответствующих числовых значений (1 или 0).

Выражением ДЛСТР(A2:A8)-ДЛСТР(СЖПРОБЕЛЫ(A2:A8))<>0 выполняется проверка длин строк до и после применения рассматриваемой функции СЖПРОБЕЛЫ. Если длина строки изменилась (разница длин не равна нулю), возвращается логическое ИСТИНА, преобразуемое в число 1.

Результат вычислений:

То есть, лишние пробелы были найдены в 4 строках столбца.

Читать далее ‘Четыре способа использования ВПР с несколькими условиями’ →]]> Все мы знаем насколько полезной является функция ВПР. Наверное, половина всех действий в Excel выполняется с помощью нее. Однако данная функция обладает рядом ограничений. Например, ВПР просматривает только крайний левый столбец в таблице или ищет только одно условие. Но что, если нам необходимо вернуть значение, соответствующее двум условиям. В таком случае нам придется прибегнуть к некоторым хитростям. Об этих хитростях мы и поговорим в сегодняшней статье.

Итак, мы с вами рассмотрим четыре варианта создания подстановочной функции с двумя условиями:

1. Использование дополнительной колонки
2. Использование функции ВЫБОР для создания новой таблицы просмотра
3. Использование функций ИНДЕКС и ПОИСКПОЗ
4. Использование функции СУММПРОИЗВ

Ну а начнем мы с вами с самого простого.

Использование дополнительной колонки

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

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

Использование функции ВПР в классическом виде нам не поможет, так как она сможет вернуть значение, соответствующее только одному условию. Из положения нам поможет выйти дополнительный столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Теперь мы сможем использовать значения столбца А, чтобы вернуть необходимое значение. Прописываем в ячейке G3 формулу:

=ВПР(G1&G2;A2:D13;4;0)

Данная формула объединяет два условия ячеек G1 и G2 в одну строку и просматривает его в столбце А. После того, как нужное условие было обнаружено, формула возвращает значение с четвертого столбца таблицы A1:D13, т.е. столбца Продажи.

Использование функции ВЫБОР для создания новой таблицы просмотра

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

Использование функции ВЫБОР подразумевает создание новой таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет выглядеть следующим образом:

=ВПР(G1&G2;ВЫБОР({1;2};B2:B13&C2:C13;D2:D13);2;0)

Основной момент данной формулы заключается в части ВЫБОР({1;2};B2:B13&C2:C13;D2:D13), который делает две вещи:

1. Объединяет значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
2. Объединяет два массива в таблицу, состоящую из двух столбцов.

Результатом работы данной функции будет таблица, которая выглядит следующим образом:

Теперь формула стала более понятной.

ВАЖНО: Так как мы использовали формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтобы дать знать программе о наших намерениях. После нажатия данной комбинации клавиш, программа автоматически установит фигурные скобки в начале и в конце формулы.

Использование функций ИНДЕКС и ПОИСКПОЗ

Третий способ, который мы с вами рассмотрим, также предполагает использование формулы массива и задействует функции ИНДЕКС и ПОИСКПОЗ.

Формула будет выглядеть следующим образом.

=ИНДЕКС(D2:D13;ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0))

Давайте разберем, что делает каждая часть данной формулы.

Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В данном случае последовательно сравнивается значение ячейки G1 с каждым значением ячеек диапазона B2:B13 и возвращается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сравнение производится со значением ячейки G2 и диапазоном C2:C13. Далее мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Комбинация ИСТИНА * ИСТИНА дает нам результат 1 (ИСТИНА). Давайте посмотрим на картинку ниже, которая поможет объяснить принцип работы более наглядно.

Теперь мы можем сказать, где находится строка, удовлетворяющая обоим условиям. Функция ПОИСКПОЗ отыскивает положение 1 в результирующем массиве и возвращает 6, так как единица встречается в шестой строке. Далее функция ИНДЕКС возвращает значение шестой строки диапазона D2:D13.

Использование СУММПРОИЗВ

СУММПРОИЗВ одна из самых мощных формул Excel. У меня даже есть отдельная статья, посвященная данной формуле. Наш четвертый способ использовании нескольких условий заключается в написании формулы с функцией СУММПРОИЗВ. И выглядеть она будет следующим образом:

=СУММПРОИЗВ((B2:B13=G1)*(C2:C13=G2);D2:D13)

Принцип работы данной формулы схож с принципом работы предыдущего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и С2:С13 соответственно. Далее оба этих массива сопоставляются и получается массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Далее данный виртуальный массив перемножается на диапазон D2:D13. Так как в нашем виртуальном массиве будет только одна единица в шестой строке, формула вернёт результат 189.

Данная функция не будет работать, если в диапазоне D2:D13 имеются текстовые значения.

Чтобы понять, как работает данная формула, рекомендую прочитать статью о функции СУММПРОИЗ.

ИТОГ

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

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

Читать далее ‘Что если отобразить скрытые строки в Excel не работает’ →]]> Не надо быть семь пядей во лбу, чтобы отобразить скрытые ячейки на незащищенном листе Excel. Так я думал до тех пор, пока не столкнулся со странным файлом, в котором я не смог показать строки традиционными инструментами.

Ниже принтскрин файла, в котором строки 2:15 срыты. Это вполне очевидно!

Далее, я попытался отобразить строки нормальными способами, которые знал. Но они не сработали.
Я выделил строки с 1 по 16, щелкнул правой кнопкой мыли, из выпадающего меню выбрал Показать. Строки 2:15 остались для меня не видимыми.

Далее перешел во вкладку Главная в группу Ячейки. Выбрал команду Формат -> Скрыть или отобразить -> Отобразить строки. И это тоже мне не помогло. Эти строки продолжали что-то от меня скрывать.

• Рабочий лист не был защищен.
• Также я попробовал переместиться с ячейки А1 на ячейку ниже с помощью клавиатуры, чтобы посмотреть изменения в окне Имя. Оно изменилось с А1 на А16, что означало, что строки 2:15 скрыты.

Тогда почему я не могу отобразить их?

Чтобы проверить работают ли команды Скрыть и Показать, я скрыл строки 18:19 и затем отобразил весь лист:

Странная ситуация, 18 и 19 строки отобразились, строки 2:15 продолжали быть скрытыми. Более странной ситуации я не встречал с момента начала использования Excel. Тем не менее отгадка пришла неожиданно.
Все дело в высоте ячеек!
Когда я попытался изменить высоту ячеек на 15, все скрытые ячейки отобразились. Для этого выделяем строки с 1 по 16, щелкаем правой кнопкой по номерам строк, из выпадающего меню выбираем Высота строки. В появившемся диалоговом окне указываем 15 (это высота, заданная по умолчанию на моем компьютере, у вас она может быть другой).

Это интересное наблюдение заставило сделать несколько тестов с высотой строки и вот что вышло:

• Для строк высотой <=0.07, строка считается скрытой и мы можем отобразить из с помощью обычных инструментов Excel
• Для строк высотой от 0.08 до 0.67, строка выглядит как скрытая, но на самом деле таковой не является. Мы не сможем отобразить такие строки нормальным способом, только изменяя высоту строки.
• Для строк высотой >=0.68, мы можем заметить маленькие строки, которые означают, что они не скрыты.

Как все это выяснилось? Только с помощью экспериментов.

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

Читать далее ‘Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами’ →]]> Сегодня я представляю свой последний (седьмой) урок бесплатного онлайн курса по основам Excel, в котором мы поговорим о том, как управлять рабочими листами.

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

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

1. Знакомство с Excel
2. Создание рабочей книги
3. Форматирование ячеек
4. Изменение ячеек
5. Печать в программе
6. Управление рабочим листом
7. Работа с несколькими рабочими листами

Скачать файл с примером вы можете по ссылке Работа с несколькими листами