Even in many of the games you mentioned (like FFH2 and Civ4), super stacks can still be effective. Consider that both sides have access to these anti-stack ingredients, thus its almost a cancelling factor. However .... if you had an army of mounted untis behind your AOE stack, after hitting each other with AOE your mounted stack could kill them.
Another thing in FFH2, which was a real killer, was a Stack of Magical Caster units!!!! Sure, this is somewhat balanced when they are only mage strength (4) and take time to upgrade from adepts to mages. But STRONG units which can cast AND be built en masse are truly deadly. Like, Blasting Workshop golems, or vampires, or Firebows. Acess to Iron only makes it worse, as you now have Vamps and Firebows +2 strength, and Iron Golems vs Wooden Golems. truly a sad day for an enemy of a magical state.
Nay, the REAL reason why stacks won't work as well is because cheap stack tactics + world-map "fast moves" are largely impossible in a game with tactical battles. Sure, the larger army has the better chance of winning, but its far less RNG, and things aren't decided on the Strategic Map, which allows things to be somewhat more balanced (you can't kill the enemy before he even knows you are attacking him).
I still think we should have "Shadows" in this game. Invisible elite units that kill other units in their sleep (essentially). High level Shadows with inc. strength and first strikes can be truly deadly. Their could be a "reveal" spell which gives a 70% chance to reveal any Shadows in a certain part of the Battle Map for 2-3 combat turns, while a "hawk" ability could have a 30% chance to reveal any Shadows on a part of the map for 1 combat turn.
Thats another thing, hidden armies and invisible units won't be quite so NERFED by hawks (potentially), as the utility of invisible units and armies is somewhat reduced. Perhaps the "Wraiths" fallen faction could make some usage of this mechanic.
In Summary -> properly built Tactical Battles will do alot to decrease the Prolific usage of giant Stacks.