прошу помощи с формулой для подсчета следующих значений: есть несколько таблиц такого рода: 1я ячейка=(текстовое название) 2я ячейка=(тоже текстовое название организации) 3я ячейка=(итоговое цифровое значение) 4я ячейка=(еще одно итоговое цифровое значение)
нужно объединить все таблички в 1 список с автосуммой по цифровым значениям. (в идеале в алфавитном порядке по 1й ячейке, но это уже мелочь... объединить и то пока не получается)
конкретный пример: в прикрепленном XLS файле.
Добрый день!
прошу помощи с формулой для подсчета следующих значений: есть несколько таблиц такого рода: 1я ячейка=(текстовое название) 2я ячейка=(тоже текстовое название организации) 3я ячейка=(итоговое цифровое значение) 4я ячейка=(еще одно итоговое цифровое значение)
нужно объединить все таблички в 1 список с автосуммой по цифровым значениям. (в идеале в алфавитном порядке по 1й ячейке, но это уже мелочь... объединить и то пока не получается)
конкретный пример: в прикрепленном XLS файле.Nekr0mant
как я вижу там используют Visual Basic скрипты. Я с ними совершенно не знаком. я хотел сделать это формулами может быть в несколько этапов, может если объединить ячейки...
как я вижу там используют Visual Basic скрипты. Я с ними совершенно не знаком. я хотел сделать это формулами может быть в несколько этапов, может если объединить ячейки...Nekr0mant
все бы ничего, если бы не списки по 60 позиций из 5ти таблиц) + человеческий фактор при объединении в ручную нескольких позиций, ошибся и все приходится с начала пересчитывать если сумма не сошлась.
уже несколько лет в ручную так и делаю)
все бы ничего, если бы не списки по 60 позиций из 5ти таблиц) + человеческий фактор при объединении в ручную нескольких позиций, ошибся и все приходится с начала пересчитывать если сумма не сошлась.Nekr0mant
Nekr0mant, а одному и тому же названию профессии могут соответствовать разные образовательные учреждения? Или для каждой профессии одно учреждение?)
Nekr0mant, а одному и тому же названию профессии могут соответствовать разные образовательные учреждения? Или для каждой профессии одно учреждение?)Irina_Sm
Nekr0mant, мне кажется, основная проблема с формулами в том, что проблематично создать итоговый список. Если же он есть (т.е. вы таки формируете его вручную), могу предложить еще один идиотский способ: 1. Создаете справочники профессий и ОУ (ну это так, чтобы минимизировать риск ошибки), можно список ОУ сделать зависимым от списка от профессий, можно независимым (1-й вариант надежнее, но сложнее и более громоздкий). 2. Итоги формируете с помощью СУММЕСЛИМН. В вашем примере в ячейку D7 листа "Сумма 1+2" формула
3. Сортировку списка с итоговом листе можно осуществить вручную, итоги пересчитаются.
Nekr0mant, мне кажется, основная проблема с формулами в том, что проблематично создать итоговый список. Если же он есть (т.е. вы таки формируете его вручную), могу предложить еще один идиотский способ: 1. Создаете справочники профессий и ОУ (ну это так, чтобы минимизировать риск ошибки), можно список ОУ сделать зависимым от списка от профессий, можно независимым (1-й вариант надежнее, но сложнее и более громоздкий). 2. Итоги формируете с помощью СУММЕСЛИМН. В вашем примере в ячейку D7 листа "Сумма 1+2" формула
Ого! формула действительно смогла сложить параметр. но как я понимаю 1е числовое значение, для 2го числового значения подойдет тот же принцип?
про справочники профессий и ОУ с зависимостью от списка можно чуть подробнее?
PS пытаюсь понять формулу, она сличает соответствие текста из 1й ячейи+2й ячейку и если соответствие совпало суммирует число в 3й ячейке, я правильно понял суть?
PPS кажется начинаю понимать, вы наверное имели в виду создать в итоговой таблице вообще все имеющиеся варианты чтобы они стекались в нее при совпадениях?
Ого! формула действительно смогла сложить параметр. но как я понимаю 1е числовое значение, для 2го числового значения подойдет тот же принцип?
про справочники профессий и ОУ с зависимостью от списка можно чуть подробнее?
PS пытаюсь понять формулу, она сличает соответствие текста из 1й ячейи+2й ячейку и если соответствие совпало суммирует число в 3й ячейке, я правильно понял суть?
PPS кажется начинаю понимать, вы наверное имели в виду создать в итоговой таблице вообще все имеющиеся варианты чтобы они стекались в нее при совпадениях?Nekr0mant
Сообщение отредактировал Nekr0mant - Вторник, 14.01.2014, 11:03
На "скриптах" я бы делал так - цикл по источникам, собираем в словарь (а проще параллельно в два) по ключу из сцепки B & "|" & C суммы по столбцам D и E. В конце перебором ключей можно сформировать массив для выгрузки на лист, или выгружать сразу (если данных мало, а времени много ) Сортировать можно или уже результат на листе, или отдельно отсортировать массив из ключей и затем уже по нему формировать результат.
Но "скриптов" Вы боитесь...
На "скриптах" я бы делал так - цикл по источникам, собираем в словарь (а проще параллельно в два) по ключу из сцепки B & "|" & C суммы по столбцам D и E. В конце перебором ключей можно сформировать массив для выгрузки на лист, или выгружать сразу (если данных мало, а времени много ) Сортировать можно или уже результат на листе, или отдельно отсортировать массив из ключей и затем уже по нему формировать результат.
...для 2го числового значения подойдет тот же принцип?
Просто протяните ее вниз и вправо, менять ничего не надо, разве что проверяемый диапазон, но и его можно настроить автоматически "Вставка" - "Таблицы" - "Таблица".
...для 2го числового значения подойдет тот же принцип?
Просто протяните ее вниз и вправо, менять ничего не надо, разве что проверяемый диапазон, но и его можно настроить автоматически "Вставка" - "Таблицы" - "Таблица".alex1248
...PPS кажется начинаю понимать, вы наверное имели в виду создать в итоговой таблице вообще все имеющиеся варианты чтобы они стекались в нее при совпадениях?
Да, если этих вариантов не слишком много. А потом просто отфильтровать те, по которым есть значения, отличные от 0. Ну а если не вручную - наверное, всё-таки только макросом. Формулами, если и можно, думаю, будет слишком громоздко, и риск ошибки также возрастет.
...PPS кажется начинаю понимать, вы наверное имели в виду создать в итоговой таблице вообще все имеющиеся варианты чтобы они стекались в нее при совпадениях?
Да, если этих вариантов не слишком много. А потом просто отфильтровать те, по которым есть значения, отличные от 0. Ну а если не вручную - наверное, всё-таки только макросом. Формулами, если и можно, думаю, будет слишком громоздко, и риск ошибки также возрастет.alex1248
Новые профессии и ОУ вводите только в соответствующих листах, при вводе инфы выбираете из выпадающего списка. Столбцы в листах с инфой переименовал в целях удобства (хотя, не исключено, что это от моего незнания ). Заранее готовить строки и протягивать формулы не надо, всё будет форматироваться при начале воода инфы в очередную строку. Если слишком ярко, можно настроить через "Работа с таблицами" - "Конструктор" - "Стили таблиц". В некоторых таблицах при вводе новых строк "летит" формат, предполагаю, что у вас разные строки могут иметь разный формат. Наверное, иногда придется подстраивать вручную, ну или "нехорошие" таблицы заменить качественными обычным копированием в другой лист.
Новые профессии и ОУ вводите только в соответствующих листах, при вводе инфы выбираете из выпадающего списка. Столбцы в листах с инфой переименовал в целях удобства (хотя, не исключено, что это от моего незнания ). Заранее готовить строки и протягивать формулы не надо, всё будет форматироваться при начале воода инфы в очередную строку. Если слишком ярко, можно настроить через "Работа с таблицами" - "Конструктор" - "Стили таблиц". В некоторых таблицах при вводе новых строк "летит" формат, предполагаю, что у вас разные строки могут иметь разный формат. Наверное, иногда придется подстраивать вручную, ну или "нехорошие" таблицы заменить качественными обычным копированием в другой лист.alex1248
как вы сделали это чудо?)) это что действительно без скриптов?))
PS все более чем изящно, даже слов нет. Одно но, добавил сейчас все курсы и УЗ, получилась шарада "собери одно с другим", можно ли сделать так, чтобы список из 2х ячеек профессия и УЗ выпадал в 1м меню, из уже заранее записанных мной вариантов? Тогда можно будет передать такой шаблон моим сотрудникам чтобы они заполняли мне отчеты по подготовленной схеме, а я смог бы их уже объединить по вашей форме.
alex1248
как вы сделали это чудо?)) это что действительно без скриптов?))
PS все более чем изящно, даже слов нет. Одно но, добавил сейчас все курсы и УЗ, получилась шарада "собери одно с другим", можно ли сделать так, чтобы список из 2х ячеек профессия и УЗ выпадал в 1м меню, из уже заранее записанных мной вариантов? Тогда можно будет передать такой шаблон моим сотрудникам чтобы они заполняли мне отчеты по подготовленной схеме, а я смог бы их уже объединить по вашей форме.Nekr0mant
Сообщение отредактировал Nekr0mant - Вторник, 14.01.2014, 14:37
Побалуйтесь с этой функцией. Только почему-то раньше у меня адреса столбцы автоматом прописывались при их выделении, а в вашем файле пришлось вручную (тоже опыт ). Это ссылки типа Таблица[Диапазон], разберетесь с ними - шараду соберете легко.
Побалуйтесь с этой функцией. Только почему-то раньше у меня адреса столбцы автоматом прописывались при их выделении, а в вашем файле пришлось вручную (тоже опыт ). Это ссылки типа Таблица[Диапазон], разберетесь с ними - шараду соберете легко.alex1248
Спасибо, буду осваивать - "Вставка" - "Таблицы" - "Таблица".
Про объединение столбцов - до последнего буду стараться этого избежать, т.к. в итоге придется копировать сведенный результат в утвержденную форму с 4 столбцами. А если их объединить, не получится скопировать результат.
PS пробовал совместить 2 ячейки по "проверке данных" по типу данных "список" из источника " "=Профессии" с 2мя столбцами вместо одного... - не возымело действия, видимо функция не предусматривает две смежные ячейки в 1 ниспадающем меню.
Спасибо, буду осваивать - "Вставка" - "Таблицы" - "Таблица".
Про объединение столбцов - до последнего буду стараться этого избежать, т.к. в итоге придется копировать сведенный результат в утвержденную форму с 4 столбцами. А если их объединить, не получится скопировать результат.
PS пробовал совместить 2 ячейки по "проверке данных" по типу данных "список" из источника " "=Профессии" с 2мя столбцами вместо одного... - не возымело действия, видимо функция не предусматривает две смежные ячейки в 1 ниспадающем меню.Nekr0mant
Сообщение отредактировал Nekr0mant - Вторник, 14.01.2014, 16:28
Проблема осталась не решенной, при данном варианте не получилось вводить учебный курс и учебное заведение готовым шаблоном, без объединения 2х ячеек в одну, что не годится из за невозможности сдать итоговый результат по установленной форме
Проблема осталась не решенной, при данном варианте не получилось вводить учебный курс и учебное заведение готовым шаблоном, без объединения 2х ячеек в одну, что не годится из за невозможности сдать итоговый результат по установленной форме Nekr0mant
Проблема осталась не решенной, при данном варианте не получилось вводить учебный курс и учебное заведение готовым шаблоном, без объединения 2х ячеек в одну, что не годится из за невозможности сдать итоговый результат по установленной форме
Я не совсем понял, что собственно нужно. Обязательно, чтобы пользователь вводил только готовые возможные комбинации? Тогда, наверное, можно сделать так, чтобы пользователь-таки это в одной ячейке, а вы в своем файле на листе, которого нет у пользователей, получаете информацию, преобразованную в нужный вам вид, уже в 2 столбца. Надеюсь, я написал разборчиво?
Теперь по поводу вопроса в личку
Цитата
как вы сделали что в проверке вводимых данных через тип даннх "Список", и источником смогли написать "=Профессии" ? я пытался повторить то же самое в своей таблице, пишут неправильный источник...
На листе "Профессии" cформирована таблица Excel (Вставка-Таблица-Таблицы), для этого выделяете весь диапазон, включая заголовок, и ставите галку "Таблица с заголовками" (впрочем, часто эксель выбирает диапазон правильно, даже если пользователь выделил любую ячейку диапазона, но это не гарантировано, надо контролировать!). Когда таблица Excel создана, выделяете полностью столбец таблицы, кроме заголовка, присваиваете ему имя, в данном случае присвоено имя "Профессии". При добавлении и удалении строк именованный диапазон автоматически изменяется, оставаясь всегда равным столбцу таблицы Excel. Это имя можно использовать в ссылках. Если снова не получится, прикрепляйте файл, посмотрим.
Проблема осталась не решенной, при данном варианте не получилось вводить учебный курс и учебное заведение готовым шаблоном, без объединения 2х ячеек в одну, что не годится из за невозможности сдать итоговый результат по установленной форме
Я не совсем понял, что собственно нужно. Обязательно, чтобы пользователь вводил только готовые возможные комбинации? Тогда, наверное, можно сделать так, чтобы пользователь-таки это в одной ячейке, а вы в своем файле на листе, которого нет у пользователей, получаете информацию, преобразованную в нужный вам вид, уже в 2 столбца. Надеюсь, я написал разборчиво?
Теперь по поводу вопроса в личку
Цитата
как вы сделали что в проверке вводимых данных через тип даннх "Список", и источником смогли написать "=Профессии" ? я пытался повторить то же самое в своей таблице, пишут неправильный источник...
На листе "Профессии" cформирована таблица Excel (Вставка-Таблица-Таблицы), для этого выделяете весь диапазон, включая заголовок, и ставите галку "Таблица с заголовками" (впрочем, часто эксель выбирает диапазон правильно, даже если пользователь выделил любую ячейку диапазона, но это не гарантировано, надо контролировать!). Когда таблица Excel создана, выделяете полностью столбец таблицы, кроме заголовка, присваиваете ему имя, в данном случае присвоено имя "Профессии". При добавлении и удалении строк именованный диапазон автоматически изменяется, оставаясь всегда равным столбцу таблицы Excel. Это имя можно использовать в ссылках. Если снова не получится, прикрепляйте файл, посмотрим.alex1248
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
) 
). 
