Работа с таблицами "Эксель": пошаговая инструкция, особенности и рекомендации
Умные Таблицы Excel – секреты эффективной работы
Работа со сводными таблицами в Excel на примерах
Умные таблицы Excel 2007-2013
Умные Таблицы Excel – секреты эффективной работы
Работа с таблицами "Эксель": пошаговая инструкция, особенности и рекомендации
Видео Постановка задачи Имеем таблицу, с которой постоянно приходится работать сортировать, фильтровать, считать по ней что-то и содержимое которой периодически изменяется добавляется, удаляется, редактируется. Ну, хотя бы, для примера - вот такого вида:. Размер - от нескольких десятков до нескольких сотен тысяч строк - не важен. Задача - всячески упростить и облегчить себе жизнь, превратив эти ячейки в "умную" таблицу. Выделяем любую ячейку в таблице и на вкладке Главная Home разворачиваем список Форматировать как таблицу Format as table:. В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:. Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого. Использование любых материалов сайта строго с указанием ссылки на источник. Техническая поддержка сайта GTAlex. Умные таблицы Excel Добавил видео для ленивых Ссылка. Блин, пользовался только как для оформления. Вы не одиноки, Роман. У меня на каждом тренинге такие люди находятся Родитель Ссылка. Николай, подскажите, пожалуйста, как закрепить столбец при ссылке Таблица1[НДС], чтобы при протягивании ссылка на столбец не менялась. Алёна, что в вашем понимании "закрепить столбец"? Если вам нужна ссылка на все ячейки столбца, то можно прямо так и написать в формуле - Таблица1[НДС]. Но при копировании формулы по строке данная ссылка съезжает на соседние столбцы и выглядит соответсвенно Таблица1[Сосед], а надо чтобы осталась Таблица1[НДС]. Смею предположить, что дело в разном способе выбора ячеек при заполнении формулы. А если вводить формулу при помощи выбора нужной ячейки мышкой, либо сразу с клавиатуры набирать, то получится B3, куда в последствии можно будет добавить и формулы. Либо ручками в каждом столце поправить формулу можно. Ссылка Таблица1[НДС] удобна тем, что она динамическая. Вводите в формулу ДВССЫЛ "Таблица1[НДС]" вместо Таблица1[НДС] - тогда при копировании по строке ссылка не будет сползать на соседние столбцы. А я нашел вариант проще для понимания для Фиксированной ссылки на столбец: При протягивании формулы в таблице действительно происходит смещение. Попробуйте не тянуть формулу маркером автозаполнения, а копировать ячейку с формулой в соседний столбец или столбцы. Тогда имя указанного столбца будет оставаться неизменным. Век живи-век учись у Павлова! Буду вырабатывать правильные рефлексы работы в Excel, а протом сменю ник на Собака Павлова Ссылка. Подскажите как сослаться на конкретную ячейку в динамической умной таблице с другого листа книги? Вернее сослаться то получается вот так: А1 , но если к динамической таблице применить фильтр или сортировку, то на месте Лист1А1 уже другое значение, из другой строки! Возможно ли сделать чтобы формула Лист1А1 автоматически изменялась? Подозреваю, что нет Ссылка. При фильтрации-то все ОК, а вот при сортировке - да, ссылка остается на старую ячейку, то есть не корректируется после перестановки строк и дает неправильный результат. Выходов вижу два - либо использовать для вытаскивания из таблицы функцию ВПР, либо отказаться от использования "умных таблиц". Excel - он что, бесконечен? Не получается использовать "Промежуточный итог" с умной таблицей кнопка неактивна Ссылка. Не за что, Владимир! Да, к сожалению, без ложки дегтя не обойтись - в умных таблицах не работают промежуточные итоги но по ней можно построить сводную и получить тот же результат. Работает тут промежуточный итог Проблема с защитой листа Хочется закрыть именно столбцы с формулами, чтобы их корявыми ручками не редактировали, но тогда смысл вообще теряется. Надеюсь, что здесь кто-то уже нашел решение и готов поделиться. Я в свою очередь про промежуточный итог выдам "страшную" тайну. ИТОГИ, то она работает, но я имел ввиду нечто более изящное и автоматическое Родитель Ссылка. Особенно в связке с заполнением бланков и сводными таблицами. Не улавливаю проблему с промежуточными итогами.. Ведь при включенной строке Итогов в Конструкторе при фильтрации подводятся все промежуточные итоги, или речь о чём-то другом? Можно ли в ней работать несколькими пользователями в общем доступе?? В общих файлах умные таблицы не работают. Просто использование данного режима оказалось не совсем удобным и хотелось бы вернуть прежние свойства своему массиву данных. Кроме как копировать все и вставить через Paste special-Values решения не вижу. Если выделить любую ячейку "умной таблицы", то на вкладке Конструктор - Преобразовать в диапазон Design - Convert to Range. А можно ли, при использовании ВПР по умной таблице сослаться на название столбца, из которого будет возвращаться значение, вместо использования его номера? Получилось с помощью конструкции СТОЛБЕЦ Таблица1[НДС]. Может быть можно проще? Да нет, куда уж проще - сам бы так и делал Можно еще функцию ПОИСКПОЗ использовать для поиска в шапке нужного названия, но это не проще будет. Спасибо, как раз это решение искал. Планета Эксель как всегда спасает! Закрепить столбец таблицы в формуле без извращений можно так: Из опыта работы с "умными таблицами" получил одно негативное наблюдение, что они перестают быть "умными", как только ставишь защиту листа, при этом оставив область таблицы с учетом ее увеличения не защищенной. Николай, а почему до сих пор "Умные таблицы Excel ", а не "Умные таблицы Excel "? Да, сейчас поправлю, спасибо Родитель Ссылка. Коллеги, возможно ли для сводной таблицы использовать в формулах имя таблицы, а не ссылку на диапазон по аналогии с "умными таблицами"? Пробовал даже сводную преобразовать в умную таблицу, не помогает Предполагаю, что я что-то делаю неправильно В частности интересует использование данной возможности в функции ВПР: Но это, так сказать, обходной путь. Возможно есть другое решение? По умолчанию, если делать ссылку на любое значение сводной таблицы, Excel выводит функцию ПОЛУЧИТЬ. Можно заменить текстовые критерии ссылками на ячейки с критериями. Тогда еще круче чем ВПР получится, так как можно задать сразу несколько условий для получения результата. Есть таблица с некоторым набором столбцов и постоянно добавляющимися строчками - на Лист1. Надо на Лист2 копию этой таблицы, без некоторых столбцов, при этом чтобы новые строчки тоже добавлялись. Можно ли сделать, чтобы добавлялись? Сделал копию через сводную в "табличной форме". Её тоже обновлять ручками автоматически только макрос - зато всё понятно, копия точная. Но сводная даже в табличной форме - это не то же самое, что исходная таблица. Николай, а как бы вы копировали такую таблицу? Если на листе кроме нее ничего нет, то написал бы простой макрос копирования листа с последующим удалением ненужных столбцов в копии. Спасибо, в самом деле автоматизировать, на кнопку повесить - да и всё Родитель Ссылка. Я подобное при проверке вводимых данных в ячейке "по списку" из столбца "умной" таблицы из другого листа использую: Скажите, а что делать, если шапка сложная одно название на несколько столбцов, к примеру? Придумать название для каждого столбца - обязательно. Если у вас одно название на несколько столбцов, то как к ним адресоваться? Многострочные шапки совсем нельзя. Можно попытаться имитировать или подставлять на время работы однострочную, а "для шефа" подставлять красивую многоэтажную. Никак не могу справиться: Можно подсчитывать количество заполненных ячеек в строке и вычислять потом сумму, если заполнены все. Если, например, у вас пять столбцов в таблице, то в шестом столбце будет формула вида: E2 ;0 Родитель Ссылка. Данная формула работает некорректно, при наличии пустых строк в диапазоне, все равно происходит суммирование заполненых столбцов. Нашел какую-то функцию CountByMask, вроде работает, но уверен, что должно быть проще все. Формула была дана ранее правильная, но нужно сравнивать не с нулем, а с точным нужным количеством. В последнем аргументе ЕСЛИ стоит "", что будет приводить к пустой ячейке в результате, пока все 5 чисел не будут введены. Как быть с защитой листа в умных таблицах? Возможно ли сделать умную таблицу горизонтальной. Чтобы поля со значениями располагались не в столбец а в строку, а заголовки были в левом столбце? Точно нет Родитель Ссылка. Уважаемый Николай почему не растягивается сводная таблица при защите определенных ячеек, точнее целых столбцов. Не имеется ввиду допустим от I1: I, а весь столбец. К примеру в видео Вы добавили столбец ПРИБЫЛЬ, вот именно и его надо защитить от не умелых рук. Так в принципе работать возможно, но не удобно растягивать таблицу самому. Что-то вроде перейти на машину с механической коробкой передач с автоматической Заранее спасибо! Здравствуйте, Николай, подскажите пожалуйста решение в следующей ситуации Имеется несколько умных таблиц, абсолютно идентичных по формату число столбцов, заголовки , но с разным содержанием, каждая таблица на отдельном листе, наименование таблицы соответственно характеру содержания типа Деревья, Звери, Рыбы и т. На первом листе умная таблица аналогичного формата число столбцов, заголовки , но содержание формируется из остальных таблиц следующим образом: Попробуйте использовать ДВССЫЛ для формирования ссылки во втором аргументе функции ПОИСКПОЗ. Я как-то и забыл про ДВССЫЛ, а ведь решение было почти на поверхности: Все гораздо проще когда знаешь, совершенно с Вами согласен. Можно ли, формирование заголовков сделать через формулу? HeaderRowRange - диапазон строки заголовка соответственно он имеет тип Range Родитель Ссылка. Умные таблицы очень помогают Передачи заливки можно получить дополнительные формирования фильтров, формул И соответственно реагировать на изменения исходной. Excel Или это вопрос не в той теме? Такое только макросом - однозначно. Причем, на практике надо будет еще смотреть: Прикрепил пример на странице Отобразить заливку ячейки на другом листе Может это поможет Ссылка. По которой легко на отдельных листах показывать итоговые результаты и проводить манипуляции с цветом сортировки, формулы Как уже писали коллеги на форуме, Excel не умеет отлавливать перекрашивание ячеек ни формулами ни событиями VBA. Очень не советую использовать цвет для серьезной работы с данными, любые дальнейшие действия с ними очень усложняются. Очень понравилось то, что вы делаете. Дайте наводку на разрешение моей проблемы пожалуйста. У меня есть две умные таблицы на одном листе, одна изменяющаяся другая нет. Проблема возникает при добавлении новых строк в первую верхнюю таблицу, она растет вниз захватывая пустые строки и приближается ко второй таблице. Как сделать так чтобы это расстояние между таблицами оставалось неизменным. Во второй таблице есть формулы которые ссылаются на первую таблицу. Я бы вставил несколько лишних пустых строк "про запас" между таблицами и скрыл их, чтобы не мешали. Евгений, топорный вариант - заполнить столбец между 1ой и 2ой таблицей значениями - можно просто в каждую ячейку пробел поставить, т. Подскажите, можно ли организовать выпадающий список который будет брать значения из определенного столбца умной таблицы? Оказывается для каждого столбца умной таблицы нужно присвоить имя. Тогда и новые данные сами появляются в списке: Можно и не присваивать, а использовать в поле Источник при создании выпадающего списка вот такую функцию: Очень нужна помощь вот по такому вопросу. Умная таблица умеет делать авторазвертывание при добавлении данных в строке ниже таблица автоматически увеличивается. Но если защитить от изменений хотя-бы один из столбцов таблицы что бы туда не лезли с кривыми руками , авторазвертывание перестает работать. Как можно это исправить? Может быть поможет совет. При защите листа авторазвёртывание отключается автоматически. Но можно при защите разрешить пользователям вставку строк. Новые строки надо добавлять вовнутрь диапазона таблицы. Защита распространится на новые ячейки. Хочу вернуть статус "умная таблица". Главная - Форматировать как таблицу Что выбрать, чтобы не нарушить предыдущий стиль? Если не угадаешь, то стили накладываются. Как же узнать, какой стиль применялся ранее? Буду благодарна за помощь. Можно перед превращением таблицы обратно в диапазон отключать ее полосатую раскраску вкладка Конструктор - Стиль - Нет. Помогите пожалуйста решить проблему. В своих книгах я использую "умные таблицы", соответственно все обращения в моих формулах написаны не в виде адреса, а в виде имен столбцов, строк и т. ИНДЕКС Сводка[ Данные];ПОИСКПОЗ ОтчетОплатаПоставщику[ Контрагент] Я имею таблицу, в которой как выяснилось, периодически повторяются определенные данные, по которым происходит поиск соответствий. С помощью связки функций ИНДЕКС и ПОИСКПОЗ, я имею возможность получить только первое вхождение искомых данных. Не могли бы Вы подсказать, каким образом мне выудить не первое, а последнее вхождение искомого. Как представить имя таблицы, как строку? При генерации отчета, должно в текст отчета вставляться названия таблиц, всех, которые есть на листе Родитель Ссылка. На вкладке Конструктор есть имя таблицы в которой стоит активная ячейка. На вкладке Формулы - Диспетчер имен - можно увидеть имена всех таблиц, которые есть в файле. Можно ли, например, в конце дня отследить, какие изменения я внесла на протяжении дня или недели в эту таблицу. Я так понимаю, такое можно посмотреть в общем доступе, но тогда утратятся таблицы? Да, в общем доступе таблицы не работают, к сожалению. Надо писать макрос отслеживания, который будет регистрировать все изменения таблицы на отдельном скрытом листе. Или делать регулярное резервное копирование и сравнивать старые и новые файлы. Ну кстати на удаление данных умная таблица автоматически НЕ подстраивается вы написали, что она это делает , наверное, стоит поправить. Я имел ввиду удаление строк-столбцов, а не очистку ячеек, само собой Родитель Ссылка. Подскажите, пожалуйста, как ускорить процесс ввода таких формул, не прибегая к ручному прописыванию F2: Заходим Файл - Параметры - Формулы и снимаем галочку Использовать имена таблиц в формулах. Случайно удалил "авторазвертывание" формул в "умной" таблице и нигде не могу найти, где включить обратно. Сам же и нашел: Удаление авторазвертывание из одного столбца - только костыльно, удалить столбец а потом добавить обратно. Вопрос 1 - работает ли макрос с галочками с этой табличкой? Вопрос 2 - а можно в ней получать две итоговые суммы? Первая по отсортированной дате; Вторая - общая по всей таблице. Возможно ли преобразовать "Умную таблицу" Обратно простую. При копирование данных из разных таблиц в одну. Таблица искажает данные и меняет ссылки. А когда я раньше работала с таблицей не преобразовав ее все вставлялось корректно. Вкладка Конструктор - Преобразовать в диапазон Design - Convert to Range Родитель Ссылка. Николай, скажите, возможна ли работа с "умной таблицей" при наложении защиты на лист? Если да, то каким образом это можно сделать? Имеется в виду, оттягивание её. При добавлении строк таблица расширяется, диаграмма достаивается. А вот при добавлении столбцов только таблица меняется. У меня тоже вопрос по защите умных таблиц. В таблице есть и данные для пользователей и мои формулы. При защите ячеек с формулами и включении защиты листа пользователи не видят вводимые данные в строке формул. Можно как-то обойти эту проблему? Подскажите, можно ли в качестве диапазона для умной таблицы использовать динамический диапазон на функции СМЕЩ OFFSET или именованный диапазон с соответствующей функцией. Идея была "приставить" умную таблицу к сводной таблице с изменяющимся количеством строк, для дополнительных вычислений. Если делать просто с помощью формул, то их приходится протягивать сильно вниз с запасом. Использование умной таблицы на основе динамического диапазона помогло бы решить задачу красиво, уменьшить размер файла, сократить время пересчёта Но выбрать имя этого диапазона как источник данных для умной таблицы не получается Родитель Ссылка. Методом "Тыка" не получается, в интернете инструкций тоже не нашёл Если такая возможность есть - научите, буду признателен! По-прежнему не нашёл способа задавать размер "умной таблицы" динамически с помощью формулы СМЕЩ OFFSET. Если таковой имеется, подскажите пожалуйста! У меня есть уже отредактированный прайс цен, там ловольно много объединенных ячеек, но когда я применяю "Форматирование Таблицы", то все объединенные ячейки становятся обычными и заново их объединить не получается. Скажите, можно ли применив "Форматирование Таблицы" сохранить исходный вид прайса? Для примера прикрепляю два изображение как было и как стало: Наталия, умные таблицы не могут содержать объединенные ячейки в принципе Родитель Ссылка. Скажите, как правильно обратиться к умной таблице в случае, когда ее выбираем в качестве источника для выпадающего списка в проверке данных? Сейчас я просто ссылаюсь так: Как обратиться к Таблица1? Ответ уже писали выше: А подскажите пожалуйста, как заставить умную таблицу "забыть" формулу, которую я ввел в вычисляемом столбце. Теперь я решил, что в данном поле должны быть просто данные. Простым удалением формулы из ячеек этого поля ничего не добьешься - при добавлении новых строк Excel упорно будет восстанавливать формулу. Как заставить его ее забыть без удаления поля и создания его заново? Огромное спасибо за Вашу деятельность. Более полезного и структурированного ресурса для себя не нахожу. Вопрос связан с добавлением столбцов в "умную таблицу" - при вставке столбца и условиях, что справа и слева расположены столбцы со списками, получаем новый столбец, соответственно со списком Как избавиться от последнего красиво и правильно избавиться? На обычном листе, получается выделить столбец и изменить значение Список на Любое значение через Проверку данных при выделенном столбце, а вот в "умной таблице" при выделенном добавленном вставкой столбце - Проверка просто неактивна Проверка активна, если выбрана ячейка, но тут либо к одной ячейке применить что может быть оч затруднительно и трудоемко , либо ко всему диапазону с условием - тогда изменения постигнут и еще один столбец. Возможно есть какие хитрости при вставке, или выделении Возможно нужно выделить все значения того или иного столбца умной таблицы без самого заголовка? У меня вроде бы работает так валидация Выделяются все значения столбца точно так же, как и весь столбец, когда появляется чёрная стрелка вниз, только ловить стрелку нужно в верхней части заголовка соответствующего столбца. Ваше предложение рабочее до чего ж я темнота Не могу справиться с умной таблицей. Есть список оборудования надо сделать автоматический расчет наработки часов. В умной таблице есть столбцы: А вот как сделать это с ячейками умной таблицы не знаю. Подскажите, пожалуйста, разумное решение ситуации. В книге несколько листов с одинаковыми "умными" таблицами. Вернее "скелет" таблиц одинаковый, данные на каждом листе разные, и разное количество строк. Необходимо на итоговом листе с тем же "скелетом" разместить итоговые строки по каждому листу и вычислить среднее по каждому столбцу. Есть ли какой-то способ перенести итоги, не тыкая в каждый аргумент на каждом листе? Или, может быть, это как-то быстро умеет делать PLEX? Николай, спасибо за уроки. Скажите, почему при добавлении новой строки в умной таблице в одну из ячеек формула не добавляется автоматически в другие ячейки формулы подставляются? Вчера всё работало прекрасно, сегодня ячейка добавляется без формулы. Над пустой ячейкой формула есть. Здравствуйте, Николай, у меня записанно макро, но оно не работает в динамическом таблице. Select End Sub Ссылка. По умным таблицам проясните, пожалуйста. Есть умная таблица и в одном из столбцов данные вводятся в формате "Проверка данных". При добавлении новой строки все получается прекрасно формулы копируются, таблица автоматически расширяется, но атрибут "Проверка данных" в нужном столбце не появляется. И приходиться при каждом добавлении новой строки вручную копировать предыдущую ячейку столбца, что бы появилась "Проверка данных". Возможно ли сделать чтобы атрибут "Проверка данных" тоже копировался при добавлении строк в умной таблице. Так неудобно, ранее вставлялось Ссылка. Подскажите, пожалуйста, что не так делаю: В умной таблице живет себе формула, автоматически добавляется в новые строки, и пока все хорошо. Потом формулу редактируем в произвольной ячейке столбца. И тут есть два варианта - 1 столбец автоматически сам заполняется новой формулой и дальше все хорошо; 2 новая формула остается только в текущей ячейке и дальше все странно. После ручного "растягивания" формулы на весь! Подскажите пожалуйста, куда посмотреть и обучиться Имеется книга с большим количеством листов. На каждом листе таблица с итоговой ячейкой сумма. Как сделать ссылки на эти итоговые ячейки на один отчетный лист? Подскажите пожалуйста в умных таблицах ячейке с выпадающим списком автоматически не копируются? Выпадающий список реализован стандартным способом. Подскажите пожалуйста, в умных таблицах ячейки с выпадающим спискам автоматически не копируются при добавлении новой строки? Подскажите можно ли как то узнать имя созданной таблицы в формуле получить имя в виде текста? У меня вопрос по поводу форматирования: Когда вы выбираете форматирование таблицы, ставьте галочку на "таблица с заголовками" Родитель Ссылка. На сколько смог тщательно изучил тему, но не нашел ответа на вопрос, построил умную таблицу, заполнил ячейки разным функционалом, списки, формулы и прочее, списки в добавленных строках работают, а вот формулы нет Подскажите, где допустил ошибку? Здравствуйте, подскажите есть ли возможность строку итогов с низу перенести на верх? Например, мне нужно в строке итогов сделать средневзвешенную величину. Я прописываю формулу в одной ячейке итогов и хочу скопировать в остальные, но она ссылки на столбцы не двигает, ссылаясь все время на одни и те же. Как "протянуть", чтобы не менять вручную номера столбцов? В9 например, так как при применении фильтра нужен пересчет Ссылка. А как сделать, чтобы при изменении продолжении "умной" таблицы автоматически изменялась бы и сводная таблица. Пользуюсь умными таблицами давно и успешно и вдруг она перестала менять диапазон при добавлении строк ниже. Подскажите, пожалуйста, что cделать, чтобы восстановить эту функцию. У меня та же проблема, что и у Марины. Подставляю новые значения вниз умной таблицы, а формулы в соседние столбцы автоматом подставляются через раз, а во второй таблице вообще не подставляются. Как это можно вылечить? Спасибо за прекрасные уроки! Можно ли создав свой стиль умной таблицы сохранить ее, чтобы её можно было применить и в других книгах ексель? Заранее благодарю за ответ! Есть вопрос по данной теме. Я создала 2 умные таблицы. Название строк во второй таблице берется из первой. Мне нужно чтоб при добавлении строки в первой таблице вторая автоматически добавляла строку. Подскажите как можно обратиться к умной таблице через VBA в частности меня интересует можно ли с помощью VBA заменить название таблицы на название листа. Заранее Благодарен за ответ Ссылка. Что лучше использовать, макрос или формулу, которая покажет в 3-й таблице изменения, а именно новых - 10 человек Ссылка. Есть ли функция, возвращающая порядковый номер поля в таблице? Для использования в качестве аргумента в vlookup. Тогда номер столбца Coef в таблице PayPlan не будет совпадать с номером колонки. Таблицы это шикарное открытие. Вопрос - можно ли ссылаться на таблицу в другой книге, если она закрыта? Ссылаться можно, но должен быть открыт источник, иначе возвращается ССЫЛКА!. Есть способ получить эти данные через "подключение", но тогда совсем теряются заголовки. Получается сослаться на умную таблицу в другой книге, когда она закрыта можно только одним способом: Других способов не нашел. Вопрос по "умным таблицам" следующий. Не активны "Промежуточные итоги" на вкладке "Данные", когда стоит форматирование таблицы. Нужно, чтобы в результате таблицу можно было крестиком свернуть и развернуть.
Дикси каталог продуктов
Программы диагностики рено сандеро своими руками
Играть на баяне день победы
Расчет суммы по банковской гарантии образец
Как восстановить историю переписки в whatsapp
Снукер таблица турнира 2017